如何在 VBA 中以编程方式在某些工作表单元格数据旁边添加按钮？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4559094/
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
How to add a button programmatically in VBA next to some sheet cell data?
提问by tobefound
I have a function that generates data for say 100 rows (and 2 columns). For each row (in the 3rd column) I need to add a button which, when clicked, brings up a custom modal dialog box giving the user 4 options/buttons to choose from.
我有一个函数可以为 100 行（和 2 列）生成数据。对于每一行（在第 3 列中），我需要添加一个按钮，单击该按钮时会弹出一个自定义模式对话框，为用户提供 4 个选项/按钮可供选择。
Any idea how to do this?
知道如何做到这一点吗？
回答by Dr. belisarius
I think this is enough to get you on a nice path:
我认为这足以让你走上一条不错的道路：
Sub a()
Dim btn As Button
Application.ScreenUpdating = False
ActiveSheet.Buttons.Delete
Dim t As Range
For i = 2 To 6 Step 2
Set t = ActiveSheet.Range(Cells(i, 3), Cells(i, 3))
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
.OnAction = "btnS"
.Caption = "Btn " & i
.Name = "Btn" & i
End With
Next i
Application.ScreenUpdating = True
End Sub
Sub btnS()
MsgBox Application.Caller
End Sub
It creates the buttons and binds them to butnS(). In the btnS() sub, you should show your dialog, etc.
它创建按钮并将它们绑定到butnS()。在 btnS() 子中，您应该显示您的对话框等。
回答by Alex P
Suppose your function enters data in columns A and B and you want to a custom Userform to appear if the user selects a cell in column C. One way to do this is to use the SelectionChangeevent:
假设您的函数在 A 列和 B 列中输入数据，并且您希望在用户选择 C 列中的单元格时显示自定义用户表单。 一种方法是使用SelectionChange事件：
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim clickRng As Range
Dim lastRow As Long
lastRow = Range("A1").End(xlDown).Row
Set clickRng = Range("C1:C" & lastRow) //Dynamically set cells that can be clicked based on data in column A
If Not Intersect(Target, clickRng) Is Nothing Then
MyUserForm.Show //Launch custom userform
End If
End Sub
Note that the userform will appear when a user selectsany cell in Column C and you might want to populate each cell in Column C with something like "select cell to launch form" to make it obvious that the user needs to perform an action (having a button naturally suggests that it should be clicked)
请注意，当用户选择C 列中的任何单元格时，用户窗体将出现，您可能希望在 C 列中的每个单元格中填充“选择单元格以启动表单”之类的内容，以明确用户需要执行操作（具有一个按钮自然表明应该点击它）
