VBA to add Menu Control, and then Controls to that menu

northrock

New Member
Joined
Apr 29, 2002
Messages
38
Please help,
I have a list of names in the range B3:b22 on worksheet 1...

  • Dave R
    Dave P
    Katrina
    Anne Marie
    Alex W
    Lynne
    Errol
    Charles
    Jim
    Mike
    Martin
    Bill
    Ann
    Keith
    Neil
    Alex D
    Marina
    Kathy
    Mark
    Bob
    Stephan
    Paul

Using VBA, I can build a custom commandbar/toolbar but I cannot get my head around adding a menu control to the commandbar, and then adding a series of individual (control) buttons to that drop-down menu control.

(Each button will be assigned to a name in the list on the worksheet... I think I can work that bit...but it's getting the menu control added onto the commandbar, and then adding control buttons to that menu control that I'm having problems with)

Thank you.

edit: this is what I am building the commandbar with:
Code:
Private Sub CBTR() 'all User & Authoriser level 2
On Error Resume Next
'if commandbar already exists, delete it
Application.CommandBars("CBTR").Delete
On Error GoTo errhandler
'
'build new commandbar
Set CB = Application.CommandBars.Add("CBTR")
     With CB
    .Protection = msoBarNoMove + msoBarNoCustomize
    .Position = msoBarTop
    .Enabled = True
    .Visible = True
    End With
'
'build commandbar controls
Set CBC = CB.Controls.Add(Type:=msoControlButton, ID:=2950, before:=1)
With CBC
.Caption = "CBTR:"
.TooltipText = "© 2005"
.Enabled = False
.Style = msoButtonCaption
End With
'
Set CBC = CB.Controls.Add(Type:=msoControlButton, ID:=2950, before:=2)
With CBC
.Caption = "Sign In / Sign Out"
.TooltipText = "Change User"
.Enabled = True
.Style = msoButtonCaption
.BeginGroup = False
.OnAction = "Log_User"
End With

I'd like to add the Menu Control as the third item

Thanks again
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Why do you want an individual button for each name?

Why not add a dropdown?
Code:
Sub test()
Dim cbNew As CommandBar
Dim cbCombo As CommandBarComboBox
Dim rng As Range
    
    Set cbNew = Application.CommandBars.Add
    
    Set cbCombo = cbNew.Controls.Add(msoControlComboBox)
    
    For Each rng In Range("B3:B24")
        
        cbCombo.AddItem rng.Value
    Next rng
    
    cbCombo.ListIndex = 1
    
    cbNew.Visible = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top