当前位置：文江博客话题详情

如何以编程方式将工具栏按钮（和 OnClick 处理程序）添加到 Excel

发布于 2024-07-14 10:10:19 字数 100 浏览 5 评论 0原文

如何以编程方式将工具栏（其上带有按钮）添加到 Excel （2002 年或之后）？

单击按钮时，我想要一个处理程序来创建我的 COM 对象并调用它的方法？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

锦爱 2024-07-21 10:10:19

这是应该适用于但不包括 Excel 2007 版本的基础，它具有完全不同的界面。

这位于您的 ThisWorkbook 模块中：

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeleteCommandBar
End Sub
Private Sub Workbook_Open()
    ShowToolbar
End Sub

这可以位于同一个模块中，也可以位于单独的模块中，您可以选择，尽管我更喜欢将其放在自己的模块中，这样可以更明显。您不需要 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

This is the basis for something that should work on versions up to but not including Excel 2007, which has a completely different interface.

This goes in your ThisWorkbook module:

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.

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

回复收藏 0 原文