excel - 按钮在 Excel 中引用了错误的行

Question

我有这段代码可以帮助按钮识别它所在的行。但是，当我隐藏上面的行时，按钮会引用该隐藏的行。

例如:如果按钮位于第 20 行，而我隐藏了第 19 行，则单击该按钮将返回第 19 行。如果我同时隐藏第 19 行和第 18 行，则该按钮将返回第 18 行。

确实很奇怪。

这是我用来创建按钮的 block :

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

下面是单击按钮后返回行 ID 的 block :

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

感谢您的时间和帮助。

Answer 1

您可以使用此功能:

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

它检查TopLeftCell方法返回的单元格是否不在隐藏行中。如果是，该函数将尝试下面的单元格，依此类推，只要它从未隐藏的行中找到该单元格即可。

<小时/>

您可以在子例程GoToIssue中使用它，就像这样:

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