vba 如何以编程方式向 Excel 添加工具栏按钮（和 OnClick 处理程序）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/593117/
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 programmatically add a toolbar button (and OnClick handler) to Excel
提问by Mike Woodhouse
How do I programmatically add a toolbar (with buttons on it) to Excel (2002 or later)?
如何以编程方式将工具栏（上面带有按钮）添加到 Excel（2002 或更高版本）？
When the button is clicked I want a handler to create my COM object and call a method on it?
单击按钮时，我想要一个处理程序来创建我的 COM 对象并在其上调用一个方法？
回答by Mike Woodhouse
This is the basis for something that should work on versions up to but not includingExcel 2007, which has a completely different interface.
这是适用于Excel 2007 以上版本但不包括Excel 2007（具有完全不同的界面）的基础。
This goes in your ThisWorkbook module:
这在你的 ThisWorkbook 模块中：
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteCommandBar
End Sub
Private Sub Workbook_Open()
ShowToolbar
End Sub
And this can go in the same module or a separate one, your choice, although I prefer to put it in its own module where it can be more visible. YOu shouldn't need an OnClick, the button is told what routine to call when you create the button.
这可以放在同一个模块中，也可以放在一个单独的模块中，你可以选择，尽管我更喜欢把它放在自己的模块中，这样它可以更明显。你不应该需要一个 OnClick，当你创建按钮时，按钮会被告知要调用什么程序。
Private Const TOOLBARNAME = "MyFunkyNewToolbar"
Public Sub ShowToolbar()
' Assumes toolbar not already loaded '
Application.CommandBars.Add TOOLBARNAME
AddButton "Button caption", "This is a tooltip", 526, "NameOfASubInYourVBACode"
' call AddButton more times for more buttons '
With Application.CommandBars(TOOLBARNAME)
.Visible = True
.Position = msoBarTop
End With
End Sub
Private Sub AddButton(caption As String, tooltip As String, faceId as Long, methodName As String)
Dim Btn As CommandBarButton
Set Btn = Application.CommandBars(TOOLBARNAME).Controls.Add
With Btn
.Style = msoButtonIcon
.FaceId = faceId ' choose from a world of possible images in Excel: see http://www.ozgrid.com/forum/showthread.php?t=39992 '
.OnAction = methodName
.TooltipText = tooltip
End With
End Sub
Public Sub DeleteCommandBar()
Application.CommandBars(TOOLBARNAME).Delete
End Sub
回答by stuartd
You could write an Excel add-in that creates a toolbar with your button and COM-calling code, then drop the .xla file you create in the user's XLStart folder.
您可以编写一个 Excel 插件，用您的按钮和 COM 调用代码创建一个工具栏，然后将您创建的 .xla 文件放到用户的XLStart 文件夹中。
