vba 按钮 - 查找被点击的按钮

vba button - find which was clicked

提问by Gadolin

I have assigned macro to few buttons.

我已将宏分配给几个按钮。

How can I find out inside macro which button was clicked?

如何在宏中找出点击了哪个按钮？

I am doing as user form, where he can input peoples from family:

我正在做用户表单，他可以在其中输入来自家人的人：

name1:
surname1:

名1：
surname1：

name2:
surname2:|add next member|

name2:
surname2: |添加下一个成员|

I wish button to appear always in last row of the last added person. For simplicity I think it is better to have like 100 empty forms in the sheet but all invisible at the begining.
Then when user clicks add next member I simply make next rows visible, and move button to next person. But to do that I need to know my current position.

我希望按钮总是出现在最后添加的人的最后一行。为简单起见，我认为最好在工作表中有 100 个空表单，但在开始时全部不可见。
然后当用户点击添加下一个成员时，我只需让下一行可见，然后将按钮移动到下一个人。但要做到这一点，我需要知道我目前的位置。

Similar with deletion I would make rows invisible when remove button is clicked.

与删除类似，当单击删除按钮时，我会使行不可见。

name1:
surname1:[remove]

name1:
surname1: [删除]

name2:
surname2:[remove]

name2:
surname2: [删除]

name3:
surname3:|add next member|

name3:
surname3: |添加下一个成员|

I need to know which remove button was clicked.

我需要知道点击了哪个删除按钮。

EDIT: Found in web - what do you think, seems to be best /way

编辑：在网上找到 - 你怎么看，似乎是最好的/方式

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Select

回答by Lance Roberts

I always write wrappers for each button that then call the macro in question.

我总是为每个按钮编写包装器，然后调用有问题的宏。

Like so:

像这样：

Public Sub StoreButton_Click()

  Call StoreTransValues(ActiveSheet)

End Sub

If you have only one button for any one page, you can just get the ActiveSheet property, and it will be the button on that page.

如果任何一页只有一个按钮，则只需获取 ActiveSheet 属性，它将是该页面上的按钮。

---

---

Edit:

编辑：

Here's the code to get and use the name of the calling button:

这是获取和使用调用按钮名称的代码：

Dim ButtonText As String

ButtonText = Application.Caller

ActiveSheet.Shapes(ButtonText).Delete

You would use the .Move method to move the button.

您将使用 .Move 方法移动按钮。

回答by NeverLift

I finally found the solution for determining which button in a Worksheet was pushed. Credit is due to Derk at http://www.ozgrid.com/forum/showthread.php?t=33351.

我终于找到了确定工作表中哪个按钮被按下的解决方案。信用归功于http://www.ozgrid.com/forum/showthread.php?t=33351上的 Derk 。

My final example code:

我的最终示例代码：

Sub HereIAm()
    Dim b As Object
    Dim cs, rs As Integer
    Dim ss, ssv As String
    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
        rs = .Row
        cs = .Column
    End With
    ss = Left(Cells(1, cs).Address(False, False), 1 - (ColNumber > 26)) & rs
    ssv = Range(ss).Value
    MsgBox "Row Number " & rs & "    Column Number " & cs & vbNewLine & _
        "Cell " & ss & "   Content " & ssv
End Sub

If you don't need the cells label, Cells(rs,cs).Value works as well.

如果您不需要单元格标签，则 Cells(rs,cs).Value 也可以使用。

回答by Nick Spreitzer

Since you have a macro wired to your button(s), I assume you know which button it is that was clicked. To get the location of the button, use this:

由于您有一个连接到按钮的宏，我假设您知道点击的是哪个按钮。要获取按钮的位置，请使用以下命令：

ActiveSheet.Shapes("ButtonName").TopLeftCell.Address

To move a button to a new location, use this:

要将按钮移动到新位置，请使用以下命令：

Dim NewAddress as Range
NewAddress = ActiveSheet.Cells(5, 5) 'Or where ever you need it to go
ActiveSheet.Shapes("ButtonName").Left = NewAddress.Left
ActiveSheet.Shapes("ButtonName").Top = NewAddress.Top

回答by pablo

Dim button as a string:

    button = ActiveSheet.Shapes(Application.Caller).Name