Button referencing invalid line in excel

Question

I have this block of code that helps the button identify which line it is on. However, when I hide the above line, the button refers to the hidden line.

For example: if the button is on line 20 and I hide line 19, pressing the button returns line 19. If I hide both lines 19 and 18, the button returns line 18.

This is really weird.

Here is the block 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 is the block that returns the row id of the button after clicking:

 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 is 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 to see if the cell returned by the TopLeftCell method has TopLeftCell to the hidden row. This, if any, the function tries the cell below, and so on, until it finds the cell from an open row.

---

You can use it in your GoToIssue routine GoToIssue this:

 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