用 VBA 编写 Excel 插件,然后放置一个触发它的按钮
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/10992205/
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
Write Excel Addin with VBA and then Put a button that trigger it
提问by Bob.S.P
I have written a simple excel add-in with VBA, it contains a form and related codes. after saving it as add-in and then installing it in Excel, nothing happened!
我用 VBA 编写了一个简单的 excel 插件,它包含一个表单和相关代码。将其另存为加载项,然后在 Excel 中安装后,什么也没发生!
I need to put a button somewhere in ribbons that trigger my add-in, you know like "Solver". I really need it, pleas tell me how.
我需要在功能区的某处放置一个按钮来触发我的加载项,你知道像“求解器”。我真的需要它,请告诉我如何。
I really appreciate any suggestions.
我真的很感激任何建议。
回答by Rory
Try this, needs to be added to your add-in, either in a module or in ThisWorkbook.
试试这个,需要添加到您的加载项中,无论是在模块中还是在 ThisWorkbook 中。
Private Const Button as String = "SomeName"
Sub Auto_Open 'Or Private Sub Workboo_Open() in ThisWorkbook
Dim CmdBar as CommandBar
Dim CmdBarMenu as CommandBarControl
Dim CmdBarMenuItem as CommandBarControl
Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
Set CmdBarMenu = CmdBar.Controls("Tools") ' Index 6
On Error Resume Next
Application.DisplayAlerts = False
CmdBarMenu.Controls(Button).Delete 'Just in case a button with same name already exists
Application.DisplayAlerts = True
On Error GoTo 0
Set CmdBarMenuItem = CmdBarMenu.Controls.Add(Type:=msoControlButton)
With CmdBarMenuItem
.Caption = Button
.OnAction = "MainSub" 'Name of the sub it will call when button is pushed
End With
End Sub
Make sure you delete the button on closing Excel, otherwise an additional one will be added everytime you open your addin.
确保在关闭 Excel 时删除按钮,否则每次打开插件时都会添加一个。
Sub Auto_Close 'Or Private Sub Workbook_BeforeClose(Cancel As Boolean) in ThisWorkbook
Dim CmdBar as CommandBar
Dim CmdBarMenu as CommandBarControl
Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
Set CmdBarMenu = CmdBar.Controls("Tools") ' Index 6
On Error Resume Next
Application.DisplayAlerts = False
CmdBarMenu.Controls(Button).Delete
Application.DisplayAlerts = True
On Error GoTo 0
End Sub
Your sub that you have created that you wish to run from button.
您创建的希望从按钮运行的子程序。
Public Sub MainSub
MsgBox("Hello")
End Sub
You can also add a list box in the Add-in ribbon to hold multiple buttons. First create a MenuItem as type:=msoControlPopup then within the popup add buttons as above.
您还可以在加载项功能区中添加一个列表框来容纳多个按钮。首先创建一个 MenuItem 作为 type:=msoControlPopup 然后在弹出窗口中添加按钮,如上。
As well as this VBA code, it is much easier for you to go File -> Options -> Customize Ribbon and add a new tab with a new group and assign a macro to that group. But that will only work for you, the above code will allow anyone to install the addin and have a button automate upon opening.
除了此 VBA 代码外,您还可以更轻松地转到“文件”->“选项”->“自定义功能区”并添加带有新组的新选项卡并为该组分配宏。但这只会对你有用,上面的代码将允许任何人安装插件并在打开时自动生成一个按钮。
Hope this answers your question.
希望这能回答你的问题。