vba 如何在excel中创建动态按钮
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3549586/
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 create a dynamic button in excel
提问by gizgok
I'm populating my spreadsheet with Database values.Now if I 30 rows were filled then I want to create a dynamic button on say 31st or 32nd row for doing some action.The number of rows that will be populated is not fixed.How can I do this.
我正在用数据库值填充我的电子表格。现在，如果我填充了 30 行，那么我想在第 31 行或第 32 行上创建一个动态按钮以执行某些操作。将填充的行数不固定。如何才能我这样做。
回答by MikeD
I assume that you will cycle through the records after the query has filled your table, search for a condition and "do stuff". I therefore asume that the location where you want to place the button is represented by a Range() object within the ActiveSheet()
我假设您将在查询填满您的表后循环浏览记录，搜索条件并“执行操作”。因此，我假设要放置按钮的位置由 ActiveSheet() 中的 Range() 对象表示
So let's create a dynamic ActiveX button at that location:
因此，让我们在该位置创建一个动态 ActiveX 按钮：
Sub CreateDynamicButton()
Dim MyR As Range, MyB As OLEObject
Dim MyR_T As Long, MyR_L As Long
Set MyR = Range("C110") 'just an example - you get that from your own script
MyR_T = MyR.Top 'capture positions
MyR_L = MyR.Left '...
'create button
Set MyB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False)
'set main button properties
With MyB
.Name = "MyPrecodedButton" 'important - code must exist ... see below
.Object.Caption = "MyCaption"
.Top = MyR_T
.Left = MyR_L
.Width = 50
.Height = 18
.Placement = xlMoveAndSize
.PrintObject = True 'or false as per your taste
End With
End Sub
If - in advance - you have created following routine within the active sheet
如果 - 事先 - 您在活动工作表中创建了以下例程
Private Sub MyPrecodedButton_Click()
MsgBox "Co-Cooo!"
End Sub
then a nice message box will appear once you press the button created above (tested under XP/SP2 + Excel 2003).
然后，一旦您按下上面创建的按钮（在 XP/SP2 + Excel 2003 下测试），就会出现一个不错的消息框。
The Create routine doesn't ask if a button of same name exists, you need to take measures to create it only once with the same name. If you call the routine twice, the .Name = "..."will silently fail and start naming the button "CommandButton1" and up.
Create 例程不会询问是否存在同名按钮，您需要采取措施以仅创建一次同名按钮。如果您调用该例程两次，则该例程.Name = "..."将静默失败并开始将按钮命名为“CommandButton1”及以上。
So you should have all ingredients now to create your button(s). Each of them will need to have a precoded procedure if they need to act differently. I should mention you cannot debug (step through) the Create routine after the cration of the OLE object, because control is transfered outside Excel - "it's not a bug, it's a feature!"
因此，您现在应该拥有所有成分来创建您的按钮。如果他们需要采取不同的行动，他们每个人都需要有一个预编码的程序。我应该提到您不能在创建 OLE 对象后调试（单步执行）Create 例程，因为控制被转移到 Excel 之外 - “这不是错误，而是一个功能！”
I have to admit for me it sounds a bit unusual and I would probably prefer not to install dynamic buttons acting on pre-coded Sub's, instead I would do an initial dialog before the query giving options via checkboxes like "Truncate after X rows (Y/N)" and the like - but you will have good reasons for doing it your way.
我不得不承认这听起来有点不寻常，我可能不希望安装作用于预编码 Sub 的动态按钮，相反，我会在查询之前做一个初始对话框，通过诸如“在 X 行之后截断（Y /N)”之类的 - 但你会有很好的理由按照你的方式去做。
Hope that helps - good luck
希望有所帮助 - 祝你好运
