How to create a button in Excel's Ribbonbar from the vba code in an Excel Addin.
Task:
For an existing Excel Addin, a button should be created, which is generally used in the toolbar or Ribbonbar of Excel.
No separate Vsto Addin is created, but the existing code of the Excel xlam Addin file is accessed.
Create a button in Ribbonbar
In Excel addin
Under Project-> This Workbook
If the Ribbonbar button code is written
Option Explicit On
Private Const MenuName As String = "menu_CodeDocu_de"
Private Sub Create_Ribbonbar_Addin_Button() '------------< Create_Ribbonbar_Addin_Button() >------------ '-< Set Ribbonbar_Addin >- On Error Resume Next Application.CommandBars(MenuName).Delete On Error GoTo 0 Dim addin_Menu As CommandBar Set addin_Menu = Application.CommandBars.Add(MenuName, msoBarTop) addin_Menu.Visible = True '-</ Set Ribbonbar_Addin >-
'-< create button >- Dim btn As CommandBarButton Set btn = addin_Menu.Controls.Add(Type:=msoControlButton) btn.Caption = "Emails senden.." btn.OnAction = "Test_Addin" btn.FaceId = 5622 btn.Style = msoButtonIconAndCaptionBelow '-</ create button >- '------------</ Create_Ribbonbar_Addin_Button() >------------ End Sub
|
Code to execute
In Project-> Modules
The code to be executed is written, which is called by the Ribbonbar button
Option Explicit On
Public Sub Test_Addin() MsgBox("Test Addin") End Sub
|
Save in Excel Addin
The whole code can be saved in an Excel file.
When saving, select as Save as: as add-in
And then can simply pass the addin, for example, by making the addin available on a share drive.
The add-in file is then available as Excel.xlam.