如何从宏创建一个可重复使用的按钮？ [英] How to make a reusable button from a macro?

问题描述

有一个工作的宏，我想重用它。也就是说，在工具栏上创建一个按钮（名称+图标），它将启动一个宏。试过一些教程（例如： http://www.officetooltips.com/excel/tips/create_a_toolbar_button_or_menu_item_to_run_a_macro .html ），但是我也希望将创建代码打包在一些文件中，以便在点击时将安装该宏。

是有一个简单的方法来做，如果是，比如何？ （最好的选择适用于Windows和Mac）

解决方案

更新4/20：关于构建一个加入。话虽如此，我相信这里的老计时器会说，值得做的事情是值得做的。以下是创建加载项的简短演练：

（1）保存 xlsm 或 xlsb 文件，其名称对于加载项的版本来说容易增加。

（2）将以下脚本添加到ThisWorkbook，以确保您在打开工作簿并激活工作簿时创建菜单栏：

  Private Sub Workbook_Open（） 
调用CreateMenuBar 
 End Sub 
 
 Private Sub Workbook_Activate（）
调用CreateMenuBar 
 End Sub 
  

（3）创建一个新模块，并添加以下代码来创建，删除和更新菜单栏：

  Option Explicit 
 Sub CreateMenuBar（）
 
 Dim MenuObject As CommandBarPopup 
 Dim MenuItem As Object 
 Dim SubMenuItem As Object 
 
'清除旧菜单栏
调用DeleteMenuBar（& MyMenuBar）
 
'创建菜单栏并下拉选项
 Set MenuObject = Application.CommandBars（1）.Controls.Add（Type：= msoControlPopup，_ 
 before：= 10，Temporary：= True）
 MenuObject.Caption =& MyMenuBar 
 MenuObject.OnAction =UpdateMenuBar
 
'第一级菜单选项
设置MenuItem = MenuObject.Controls.Add（Type：= msoControlPopup）
 MenuItem.Caption =& First Menu Stuff
 
'链接到第一个脚本
 Set SubMenuItem = MenuItem.Controls.Add（Type：= msoControlButton）
 SubMenuItem.Caption =&第一个脚本
 SubMenuItem.OnAction =Script1
 
'链接到第二个脚本
 Set SubMenuItem = MenuItem.Controls.Add（Type：= msoControlButton）
 SubMenuItem .Caption =& Second Script
 SubMenuItem.OnAction =Script2
 
'第一级菜单选项
设置MenuItem = MenuObject.Controls.Add（Type：= msoControlPopup ）
 MenuItem.Caption =& Second Menu Stuf f
 
'链接到第三个脚本
 Set SubMenuItem = MenuItem.Controls.Add（Type：= msoControlButton）
 SubMenuItem.Caption =& Third Script
 SubMenuItem.OnAction =Script3
 
 End Sub 
 
 Sub DeleteMenuBar（MenuName As String）
 On Error Resume Next 
 Application.CommandBars（1 ）.Controls（MenuName）.Delete 
 On Error GoTo 0 
 End Sub 
 
 Sub UpdateMenuBar（）
'在此例程中执行特殊检查，如验证工作表
 End Sub 
  

（4）验证脚本是否正常工作并保存文件。 >

（5）将文件另存为 xlam 或 xla 将其分发给用户。 Boom！

- 下面的原始帖子 -

这是Windows上的一个加载项Excel实例：

这里是Mac Excel实例中的加载项：

如果您开发了许多脚本，加载项可以非常方便想要确保他们都使用相同的代码。

Having a working macro, I'd like to reuse it. Namely, to create a button on a toolbar (with a name + icon) that will launch a macro. Tried some tutorials (example: http://www.officetooltips.com/excel/tips/create_a_toolbar_button_or_menu_item_to_run_a_macro.html), but I'd also like to pack the creation code in some file, so that when clicked, the macro would be installed.

Is there an easy way to do it, and if yes, than how? (the best option would work for both Windows and Mac)

解决方案

Update 4/20: great comment about the complexity of building an add-in. That being said, I'm sure the old timers here would say that something worth doing is worth doing right :). Here is a short walkthrough for creating an add-in:

(1) Save an xlsm or xlsb file with a name that's easy to increment for versions of your add-in.

(2) Add the following scripts into ThisWorkbook to ensure that you create a menu bar when the workbook is opened and when the workbook is activated:

Private Sub Workbook_Open()
    Call CreateMenuBar
End Sub

Private Sub Workbook_Activate()
    Call CreateMenuBar
End Sub

(3) Create a new module and add the following code to create, delete and update your menu bar:

Option Explicit
Sub CreateMenuBar()

    Dim MenuObject As CommandBarPopup
    Dim MenuItem As Object
    Dim SubMenuItem As Object

    'clear the old menu bar
    Call DeleteMenuBar("&MyMenuBar")

    'create the menu bar and drop down options
    Set MenuObject = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, _
        before:=10, Temporary:=True)
    MenuObject.Caption = "&MyMenuBar"
    MenuObject.OnAction = "UpdateMenuBar"

    'first level menu option
    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
    MenuItem.Caption = "&First Menu Stuff"

        'link to first script
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        SubMenuItem.Caption = "&First Script"
        SubMenuItem.OnAction = "Script1"

        'link to second script
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        SubMenuItem.Caption = "&Second Script"
        SubMenuItem.OnAction = "Script2"

    'first level menu option
    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
    MenuItem.Caption = "&Second Menu Stuff"

        'link to third script
        Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
        SubMenuItem.Caption = "&Third Script"
        SubMenuItem.OnAction = "Script3"

End Sub

Sub DeleteMenuBar(MenuName As String)
    On Error Resume Next
    Application.CommandBars(1).Controls(MenuName).Delete
    On Error GoTo 0
End Sub

Sub UpdateMenuBar()
    'do special checks, like verifying sheets, in this routine
End Sub

(4) Verify your scripts work and save the file.

(5) Save the file again as an xlam or xla file and distribute that to users. Boom!

--Original post below--

Here's what an add-in looks like on a Windows Excel instance:

And here's what an add-in looks like on a Mac Excel instance:

An add-in can be very handy if you develop many scripts for a fleet of users and want to ensure they're all using the same code.

这篇关于如何从宏创建一个可重复使用的按钮？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！