Text command bar

I'm trying to create a custom command bar which looks like the standard menu bar, text only, not buttons. I can build the bar but can only get buttons with text and an icon on the top level (like File  Edit  View etc.).

The code is below. LoadCustomCmdBar uses CommandsBar.Add(..msoBarTop). AddTopCmdBarCtl uses Controls.Add(msoControlPopup). And finally AddSubCmdBarCtl adds a msoControlButton.

This code creates the menu except that the top level control is a button with Roster and a down arrow. topCmdBarCtl.Style can't be used because it's a popup. When I change the top level control to msoCommandButton (to use .Style) I can't add buttons to the control.

My question is what combination of parameters creates a text type menu?


Option Explicit
    '  customCmdBar     the new command bar, gstrCustomCmdBarName
    '       GSChapter CmdBar
    '  topCmdBarCtl a control that's always visible...main level
    '         Roster
    '  subCmdBarCtl a control that's below a top level control
    '           Export
    '           Import
    Dim cbr As CommandBar
Public Sub ChangeCmdBar()
    If MsgBox("Yes to load, No to unload", vbYesNo + vbDefaultButton2) = vbYes Then
    End If
End Sub

Public Sub LoadCustomCmdBar()
    Dim customCmdBar As CommandBar
    Dim topCmdBarCtl As CommandBarControl
    For Each cbr In CommandBars
        If cbr.Name = gstrCustomCmdBarName Then
            If cbr.Visible Then cbr.Enabled = False
        End If
    Next cbr

    Set customCmdBar = CommandBars.Add("GSChapter CmdBar", msoBarTop, False, True)
    Set topCmdBarCtl = AddTopCmdBarCtl(customCmdBar, "&Roster")
    AddSubCmdBarCtl topCmdBarCtl, "&Import", "cbRoster_Import"
    AddSubCmdBarCtl topCmdBarCtl, "&Export", "cbRoster_Export"
    AddSubCmdBarCtl topCmdBarCtl, "Create &floppy disks", "cbRoster_DistFloppy"
    customCmdBar.Visible = True
End Sub

Public Function AddTopCmdBarCtl(customCmdBar As CommandBar, _
                        theCaption As String) As CommandBarControl
    Dim topCmdBarCtl As CommandBarControl
    Set topCmdBarCtl = customCmdBar.Controls.Add(msoControlButton)
    topCmdBarCtl.caption = theCaption
    Set AddTopCmdBarCtl = topCmdBarCtl
End Function

Public Function AddSubCmdBarCtl(topCmdBarCtl As CommandBarControl, _
                               theCaption As String, _
                               onAction As String) As Boolean
    Dim subCmdBarCtl As CommandBarControl

    Set subCmdBarCtl = topCmdBarCtl.Controls.Add(msoControlButton)
    With subCmdBarCtl
        .Style = msoButtonCaption
        .caption = theCaption
        .onAction = onAction
        .Tag = theCaption
        .TooltipText = theCaption
    End With
End Function

Public Sub UnloadCustomCmdBar()
    For Each cbr In CommandBars
        If cbr.Name = gstrCustomCmdBarName Then cbr.Delete
    Next cbr
    Set cbr = CommandBars("Database")
    Set cbr = CommandBars("Visual Basic")
    cbr.Visible = True
    cbr.Enabled = True
    Set cbr = CommandBars("Menu Bar")
    cbr.Visible = True
    cbr.Enabled = True
End Sub

'********** OnAction procedures **********

Public Sub cbRoster_Export()
    DoCmd.OpenForm "frmExport"
End Sub

Public Sub cbRoster_Import()
    DoCmd.OpenForm "frmImport"
End Sub

Public Sub cbRoster_DistFloppy()
    MsgBox "not implemented yet"
End Sub
Who is Participating?
mgrattanConnect With a Mentor Commented:
Here's a code example from the on-line help that shows how to create Text-only menu items.  The key is to use the Menubar:=True option when adding the CommandBar:

Set menubar = CommandBars.Add _
    (Name:="mBar", Position:=msoBarRight, MenuBar:=True)
With menubar
    .Protection = msoBarNoMove
    .Visible = True
End With

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.