Excel VBA：获取单击按钮的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6242605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: getting row of clicked button
提问by Jort
I am trying to make a button in Excel which copies a certain range of cells from the active workbook to another workbook. The copying of this range works perfectly when I specify a fixed range, but I am stumped on how to figure out the row of the clicked button.
我正在尝试在 Excel 中制作一个按钮，它将一定范围的单元格从活动工作簿复制到另一个工作簿。当我指定一个固定范围时，这个范围的复制工作得很好，但我很难确定如何找出单击按钮的行。
Every row contains 7 or so cells, and the 8th cell contains a shape with a macro attached to it (the button). When the user presses this button the 7 cells on the same row as the row containing the pressed button need to be copied.
每行包含 7 个左右的单元格，第 8 个单元格包含一个带有宏的形状（按钮）。当用户按下此按钮时，需要复制与包含按下按钮的行位于同一行的 7 个单元格。
Using ActiveCell is of no use, since pressing the button doesn't actually set that cell as active. I searched around a lot but I can't seem to find how to obtain this value. Once I have the row number of the clicked button I can figure the rest out on my own.
使用 ActiveCell 没有用，因为按下按钮实际上并没有将该单元格设置为活动状态。我搜索了很多，但似乎找不到如何获得这个值。一旦我有了点击按钮的行号，我就可以自己弄清楚其余的了。
回答by GSerg
Each Shapehas TopLeftCellproperty. It contains a cell within which the top left corner of the shape resides.
每个人Shape都有TopLeftCell财产。它包含一个单元格，形状的左上角位于该单元格中。
回答by FraggaMuffin
There's a great solution mentioned here: http://www.ozgrid.com/forum/showthread.php?t=33351&p=167317#post167317
这里提到了一个很好的解决方案：http: //www.ozgrid.com/forum/showthread.php?t=33351&p= 167317#post167317
Golden code copied from the above post:
从上面的帖子复制的金码：
Sub Mainscoresheet()
' Mainlineup Macro
Dim b As Object, cs As Integer
Set b = ActiveSheet.Buttons(Application.Caller)
With b.TopLeftCell
cs = .Column
End With
MsgBox "Column Number " & cs
End Sub
回答by Robert Nagtegaal
Excellent answer. Btw It also works for Rownumber!
优秀的答案。顺便说一句，它也适用于 Rownumber！
'Same for rownumbers!
Sub Mainscoresheet()
' Mainlineup Macro
Dim b As Object, RowNumber As Integer
Set b = ActiveSheet.Buttons(Application.Caller)
With b.TopLeftCell
RowNumber = .Row
End With
MsgBox "Row Number " & RowNumber
End Sub
回答by user3099993
This works too! Selects the cell that the generated button resides in (knew it was in column "K" but that could be calculated too!.
这也有效！选择生成的按钮所在的单元格（知道它在列“K”中，但也可以计算！。
ActiveSheet.Range("K" & ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row).Select
