26 | 02 | 2017

Excel VBA created toolbar

You can create a toolbar using VBA or VB. Code below will create a toolbar with two buttons and a combobox.  A visualisation is shown below

 

 "Toolbar created using the code below"

Save the Excel Worksheets as a *.XLA file to automaticly run the toolbar code.

Sub Auto_Open()
    '****************************************
    '*****  Code from VisibleVisual.com *****
    '****************************************
    Dim CB As CommandBar
    Dim ctrl As CommandBarControl
    'Delete toolbar if Toolbar exists
    On Error Resume Next
    Application.CommandBars("SampleCommandBar").Delete
    On Error GoTo 0
    'Create a new toolbar called SampleCommandBar
    Set CB = Application.CommandBars.Add(Name:="SampleCommandBar", temporary:=True)
    'Add controls to the toolbar
    With CB
        .Visible = True         'Make sure the toolbar is visible
        .Position = msoBarTop   'Set the position of the toolbar
        'ADD A BUTTON
        Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
        With ctrl
            .Style = msoButtonCaption                                   'Set control style to BUTTON STYLE
            .Caption = "BUTTON 1"                                       'Set the button Caption
            .OnAction = "'" & ThisWorkbook.Name & "'!TestButton1Hit"    ''When activated goto Sub TestButton1Hit
        End With
        'ADD A SECOND BUTTON
        Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
        With ctrl
            .Style = msoButtonCaption                                  'Set control style to BUTTON STYLE
            .Caption = "BUTTON 2"                                      'Set the button Caption
            .Tag = "__button2__"
            .Enabled = False                                           'Disable the button
            .OnAction = "'" & ThisWorkbook.Name & "'!TestButton2Hit"   'When activated goto Sub TestButton2Hit
        End With
        'ADD A COMBOBOX
        Set ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True)
        With ctrl
            .Width = 150                                                'Set the box width
            .Tag = "__combosample__"                                    'Set a tag witch you can refer to later on
            .OnAction = ThisWorkbook.Name & "!TestComboHit"             'What to do when activated
            .DropDownLines = 10                                         'No of Lines Displayed
            .ListIndex = 0
        End With
    End With
FillComboBox  'Goto this sub to fill the combobox
End Sub
Sub TestButton1Hit()
MsgBox "You have hit button 1" & vbCr & "This also activates Button2"
'Activate Button2
Dim ctrl As CommandBarControl
    Set ctrl = Application.CommandBars("SampleCommandBar").FindControl(Tag:="__button2__")
    ctrl.Enabled = True
'Add your code here
End Sub
Sub TestButton2Hit()
MsgBox "You have hit button 2"
'Add your code here
End Sub
Sub TestComboHit()
'Display the selected row
MsgBox Application.CommandBars.ActionControl.List(Application.CommandBars.ActionControl.ListIndex)
'If Application.CommandBars.ActionControl.ListIndex = 2 then.......
End Sub
Sub FillComboBox()
    'This sub routine fills the combobox
    Dim ctrl As CommandBarControl
    Set ctrl = Application.CommandBars("SampleCommandBar").FindControl(Tag:="__combosample__")
    With ctrl
        .Clear ' remove existing entries from the combobox
        .AddItem "Row 1"
        .AddItem "Row 2"
        .AddItem "Row 3"
        .AddItem "Row 4"
        .AddItem "Row 5"
        .AddItem "Row 6"
        .AddItem "Row 7"
    End With
End Sub
Login

Sign up now and upload your code to the website.

Help us to continue.....
Statistics
Articles View Hits
2179665
Latest Articles