用VBA写Excel Addin，然后放一个触发它的按钮 [英] Write Excel Addin with VBA and then Put a button that trigger it

问题描述

我已经写了一个简单的excel加载项与VBA，它包含一个表单和相关代码。在将其保存为加载项，然后将其安装在Excel中后，没有任何事情发生！

我需要在丝带中的某个位置触发我的加载项，你知道像Solver。
我真的需要它，请告诉我如何。

我非常感谢任何建议。

解决方案

尝试这个，需要添加到您的加载项中，无论是在模块中还是在ThisWorkbook中。

 code> Private Const Button as String =SomeName
 
 SubWork_Open'或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）
设置CmdBarMenu = CmdBar.Controls（Tools）'索引6 
 
错误恢复下一步
 Application.DisplayAlerts = False 
 CmdBarMenu.Controls（Button）.Delete'为了防止具有相同名称的按钮已存在
 Application.DisplayAlerts = True 
错误GoTo 0 
 
设置CmdBarMenuItem = CmdBarMenu.Controls.Add（Type：= msoControlButton）
使用CmdBarMenuItem 
 .Caption = Button 
 .OnAction =MainSub'按下按钮时将调用的子名称
 End with 
 
 End Sub 
  

确保在关闭Excel时删除按钮，否则每次打开您的插件时都会添加一个按钮。

  Sub Auto_Close'或Private Sub Workbook_BeforeClose（Cancel As Boolean）in ThisWorkbook 
 Dim CmdBar as CommandBar 
 Dim CmdBarMenu as CommandBarControl 
 
 Set CmdBar = Application.CommandBars（Worksheet菜单栏）
设置CmdBarMenu = CmdBar.Controls（工具）'索引6 
 
错误恢复下一步
 Application.DisplayAlerts = False 
 CmdBarMenu.Controls（Button）.Delete 
 Application.DisplayAlerts = True 
 On Error GoTo 0 
 
 End Sub 
  

您创建的子项，您希望从按钮运行。

  Public Sub MainSub 
 MsgBox（Hello）
 End Sub 
  

您还可以在加载项功能区中添加列表框以按住多个按钮。首先创建一个MenuItem作为类型：= msoControlPopup然后在如上所述的弹出式添加按钮中。

除了这个VBA代码， >选项 - >自定义功能区，并使用新组添加新选项卡，并为该组分配一个宏。但这只适用于您，上述代码将允许任何人安装插件，并在打开时自动执行按钮。

希望这可以回答您的问题。 >

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!

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.

解决方案

Try this, needs to be added to your add-in, either in a module or in 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.

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.

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.

Hope this answers your question.

这篇关于用VBA写Excel Addin，然后放一个触发它的按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！