Custom Menubar

1733

-rh

1733 is the ID, you don't need the face id for this...

-rh

Re: Custom Toolbar (not menubar, sorry)

Sorry, my mistake, I am creating a TOOLBAR not menubar!!!

At the start of my program all Excel ToolBars are hidden, then I create my custom TOOLBAR using the code below. I need the Zoom Custom Combobox so the user can customize the view of the screen. This is what I have so far and it works fine except for the zoom cbo.

' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("Colour & Size Lists")

' Create the CommandBar called "Custom"
Set cbar1 = CommandBars.Add(Name:="Custom", Position:=msoBarTop)

' Initialize the row counter
Row = 400

' Add the control data stored on the MenuSheet
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
Caption = .Cells(Row, 1)
FaceId = .Cells(Row, 2)
OnAction = .Cells(Row, 3)
Style = .Cells(Row, 4)
TooltipText = .Cells(Row, 5)
BeginGroup = .Cells(Row, 6)
Enabled = .Cells(Row, 7)
End With

' Create the controls for "Custom" CommandBar
Set MenuObject = Application.CommandBars("Custom").Controls _
.Add(Type:=msoControlButton, Before:=1, _
temporary:=True)
MenuObject.Caption = Caption
MenuObject.FaceId = FaceId
MenuObject.OnAction = OnAction
MenuObject.Style = Style
MenuObject.TooltipText = TooltipText
MenuObject.BeginGroup = BeginGroup
MenuObject.Enabled = Enabled

' Move down a row to get the parameters for the next control
Row = Row + 1
Loop

' Show the "Custom" CommandBar
cbar1.Visible = True

Re: Custom Toolbar (not menubar, sorry)

Your problem is that the Zoom combo box is not a command bar button, it's a drop down. So when you specify the type as msoControlButton, you are eliminating your chances of adding Zoom. Maybe you ought to add a "Type" column to your spreadsheet. However, you will then need to control your adding using, say, an If-Then statement. For example,

If Type = "Dropdown" Then
.Add(Type=msoControlDropdown, 1733)
Else
' Do what you have above
End If

Hope this helps,

Russell