none
Dropdown buttons in custom excel add-in menu using VBA RRS feed

  • Question

  • Hi,

    I am trying to create an add-in containing drop down which has a few button and clicking on each of the buttons executes different macros.

    Currently I have created a button (under add-in menu) clicking on which executes a macro. The below procedures are what I use in ThisWorkbook to do that.

    But I have three different macros that I would like to assign to three buttons that should be in the dropdown-list of the same add-in menu (My_Addin). I am looking to do it in excel VBA. How can it be done please?

    An example might be: In excel 2007, under 'Data' menu, 'Data Validation' contains three buttons in dropdown, viz, 'Data Validation', 'Circle Invalid Data', and 'Clear Validation Circles'.

    Thank you for your time!

    Private Const Button As String = "My_Addin"
    
    Private Sub Workbook_Open()
    Dim CmdBar As CommandBar
    Dim CmdBarMenu As CommandBarControl
    Dim CmdBarMenuItem As CommandBarControl
    
    Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
    Set CmdBarMenu = CmdBar.Controls("Tools")
    
    On Error Resume Next
       Application.DisplayAlerts = False
       CmdBarMenu.Controls(Button).Delete
       Application.DisplayAlerts = True
    On Error GoTo 0
    
    Set CmdBarMenuItem = CmdBarMenu.Controls.Add(Type:=msoControlButton)
    With CmdBarMenuItem
         .Caption = Button
         .OnAction = "My_Macro"    'Name of the macro that I would like to run
    End With
    
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim CmdBar As CommandBar
    Dim CmdBarMenu As CommandBarControl
    
    Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
    Set CmdBarMenu = CmdBar.Controls("Tools")
    
    On Error Resume Next
       Application.DisplayAlerts = False
       CmdBarMenu.Controls(Button).Delete
       Application.DisplayAlerts = True
    On Error GoTo 0
    
    End Sub
    Saturday, August 30, 2014 8:04 AM

Answers

  • Hi,

    Although we can't create/modify new ribbon using VBA, we can certainly create/modify dropdown controls under ribbon "Add-ins" and that suffices my purpose. I have solved my query using 'msoControlPopup' and 'msoControlButton' types for command-bar controls.

    For completeness, here goes my solution (the MACRO1 through MACRO4 are the ones I would like to run upon clicking of buttons I create below).

    Private Sub Workbook_Open()
    
    Application.DisplayAlerts = False
    
    Dim cmdBar As CommandBar
    Dim cmdBarCtl As CommandBarControl
    Dim cmdBarSubCtl As CommandBarControl
    
    On Error GoTo Err_Handler
    
    Set cmdBar = Application.CommandBars("Worksheet Menu Bar")
    cmdBar.Visible = True
    cmdBar.Protection = msoBarNoMove
    
    Set cmdBarCtl = cmdBar.Controls.Add(Type:=msoControlPopup)
    cmdBarCtl.Caption = "My Template &Components"
    
    Set cmdBarSubCtl = cmdBarCtl.Controls.Add(Type:=msoControlButton)
    With cmdBarSubCtl
       .Style = msoButtonIconAndCaption
       .Caption = "Insert &disclaimer"
       .FaceId = 317
       .OnAction = "MACRO1"
       .Parameter = 1
       .BeginGroup = True
    End With
    
    Set cmdBarSubCtl = cmdBarCtl.Controls.Add(Type:=msoControlButton)
    With cmdBarSubCtl
       .Style = msoButtonIconAndCaption
       .Caption = "Insert c&over"
       .FaceId = 318
       .OnAction = "MACRO2"
       .Parameter = 2
       .BeginGroup = True
    End With
    
    Set cmdBarSubCtl = cmdBarCtl.Controls.Add(Type:=msoControlButton)
    With cmdBarSubCtl
       .Style = msoButtonIconAndCaption
       .Caption = "Insert bl&ank page"
       .FaceId = 224
       .OnAction = "MACRO3"
       .Parameter = 3
       .BeginGroup = True
    End With
    
    Set cmdBarCtl = cmdBar.Controls.Add(Type:=msoControlButton)
    With cmdBarCtl
       .BeginGroup = True
       .Caption = "My &Template Full"
       .Style = msoButtonCaption
       .OnAction = "MACRO4"
    End With
    Application.DisplayAlerts = True
    Exit Sub
    
    Err_Handler:
    MsgBox "Error " & Err.Number & vbCr & Err.Description
    Application.DisplayAlerts = True
    Exit Sub
    
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim cmdBar As CommandBar
    Set cmdBar = Application.CommandBars("Worksheet Menu Bar")
    On Error Resume Next
       Application.DisplayAlerts = False
       cmdBar.Controls("Asian Template &Components").Delete
       cmdBar.Controls("Asian &Template Full").Delete
       Application.DisplayAlerts = True
    On Error GoTo 0
    End Sub

    Thanks for your time!

    Sauvik

    • Marked as answer by desauvik Sunday, August 31, 2014 1:17 PM
    Sunday, August 31, 2014 12:57 PM

All replies

  • Re: Excel Menus

    Programmatically modifying the Ribbon requires xml code.
    VBA does not and will not modify anything on the ribbon.

    Using VBA code in xl2007+  on the Application.CommandBars automatically generates a new tab on the Ribbon called "Add-Ins".
    The menu generated appears on that tab.
    '---
    Jim Cone
    free excel Date Picker add-in for xl97 thru xl2010

    • Edited by James Cone Thursday, October 20, 2016 3:01 PM
    Saturday, August 30, 2014 1:49 PM
  • Hi,

    Although we can't create/modify new ribbon using VBA, we can certainly create/modify dropdown controls under ribbon "Add-ins" and that suffices my purpose. I have solved my query using 'msoControlPopup' and 'msoControlButton' types for command-bar controls.

    For completeness, here goes my solution (the MACRO1 through MACRO4 are the ones I would like to run upon clicking of buttons I create below).

    Private Sub Workbook_Open()
    
    Application.DisplayAlerts = False
    
    Dim cmdBar As CommandBar
    Dim cmdBarCtl As CommandBarControl
    Dim cmdBarSubCtl As CommandBarControl
    
    On Error GoTo Err_Handler
    
    Set cmdBar = Application.CommandBars("Worksheet Menu Bar")
    cmdBar.Visible = True
    cmdBar.Protection = msoBarNoMove
    
    Set cmdBarCtl = cmdBar.Controls.Add(Type:=msoControlPopup)
    cmdBarCtl.Caption = "My Template &Components"
    
    Set cmdBarSubCtl = cmdBarCtl.Controls.Add(Type:=msoControlButton)
    With cmdBarSubCtl
       .Style = msoButtonIconAndCaption
       .Caption = "Insert &disclaimer"
       .FaceId = 317
       .OnAction = "MACRO1"
       .Parameter = 1
       .BeginGroup = True
    End With
    
    Set cmdBarSubCtl = cmdBarCtl.Controls.Add(Type:=msoControlButton)
    With cmdBarSubCtl
       .Style = msoButtonIconAndCaption
       .Caption = "Insert c&over"
       .FaceId = 318
       .OnAction = "MACRO2"
       .Parameter = 2
       .BeginGroup = True
    End With
    
    Set cmdBarSubCtl = cmdBarCtl.Controls.Add(Type:=msoControlButton)
    With cmdBarSubCtl
       .Style = msoButtonIconAndCaption
       .Caption = "Insert bl&ank page"
       .FaceId = 224
       .OnAction = "MACRO3"
       .Parameter = 3
       .BeginGroup = True
    End With
    
    Set cmdBarCtl = cmdBar.Controls.Add(Type:=msoControlButton)
    With cmdBarCtl
       .BeginGroup = True
       .Caption = "My &Template Full"
       .Style = msoButtonCaption
       .OnAction = "MACRO4"
    End With
    Application.DisplayAlerts = True
    Exit Sub
    
    Err_Handler:
    MsgBox "Error " & Err.Number & vbCr & Err.Description
    Application.DisplayAlerts = True
    Exit Sub
    
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim cmdBar As CommandBar
    Set cmdBar = Application.CommandBars("Worksheet Menu Bar")
    On Error Resume Next
       Application.DisplayAlerts = False
       cmdBar.Controls("Asian Template &Components").Delete
       cmdBar.Controls("Asian &Template Full").Delete
       Application.DisplayAlerts = True
    On Error GoTo 0
    End Sub

    Thanks for your time!

    Sauvik

    • Marked as answer by desauvik Sunday, August 31, 2014 1:17 PM
    Sunday, August 31, 2014 12:57 PM