Button referencing incorrect row in excel

Question

I have this block of code which helps a button identify which row it's in. However, when I hide the above row, the button references that hidden row.

For example: if the button is on row 20 and I hide row 19, clicking the button returns row 19. If i hide both rows 19 and 18, the button returns row 18.

It's really weird.

Here is the block which I used to create the button:

Sub AddButtons()
  Dim button As button
  Application.ScreenUpdating = False

  Dim st As Range
  Dim sauce As Integer

  For sauce = 10 To Range("F" & Rows.Count).End(xlUp).Row Step 1
    Set st = ActiveSheet.Range(Cells(sauce, 11), Cells(sauce, 11))
    Set button = ActiveSheet.Buttons.Add(st.Left, st.Top, st.Width, st.Height)

    With button
      .OnAction = "GoToIssue.GoToIssue"
      .Caption = "Go To Source"
      .Name = "Button" & sauce
    End With
  Next sauce
  Application.ScreenUpdating = True
End Sub

And here's the block which returns the row ID of the button once it's clicked:

Sub GoToIssue()

    Dim b As Object
    Dim myrow As Integer

    Dim hunt As String

    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
        myrow = .Row

    End With


    hunt = Worksheets("Dummy").Range("F" & myrow).Value

    'MsgBox hunt

End Sub

Your time and help are appreciated.

Answer 1

You can use this function:

Public Function FindButtonRow(btn As Object) As Long
    Dim cell As Excel.Range
    '-------------------------------------------------

    Set cell = btn.TopLeftCell

    Do While cell.EntireRow.Hidden
        Set cell = cell.Offset(1, 0)
    Loop

    FindButtonRow = cell.row

End Function

It checks if the cell returned by TopLeftCell method is not in hidden row. It if is, the function tries the cell below and so on, as long as it finds the cell from unhidden row.

---

You can use it in your subroutine GoToIssue just like that:

Sub GoToIssue()

    Dim b As Object
    Dim myrow As Integer

    Dim hunt As String

    Set b = ActiveSheet.Buttons(Application.Caller)
    myrow = FindButtonRow(b)

    hunt = Worksheets("Dummy").Range("F" & myrow).Value

    'MsgBox hunt

End Sub