Custom menus

[Lee]

How many levels can a custom menu have? I currently have
a menu that has two levels or popups, but I can't get it
to extend further than two. I didn't know if that was
because I'm doing it wrong or if there is a limit.

 

[Chip Pearson]

Lee,

I don't know of any hard and fast limit, and if there is such a limitation,
it is definitely greater than two levels. Are you creating the menus with
code? What code are you using?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Lee]

This is the code I'm currently using:

On Error Resume Next
With Application.CommandBars("worksheet menu bar").Controls
("Tally")
With .Controls
With .Add(msoControlPopup)
.Caption = "Select Tally"
With .Controls
With .Add(msoControlButton)
.Caption = "Med/Dental Tally"
With .Controls
With .Add(msoControlButton)
.Caption = "Format Tally"
.OnAction = "FormatTally"
End With
With .Add(msoControlButton)
.Caption = "Add Tally Formulas"
.OnAction = "MakeFormulas"
End With
End With
End With
With .Add(msoControlButton)
.Caption = "Rx Tally"
With .Controls
With .Add(msoControlButton)
.Caption = "Format Rx Tally"
.OnAction = "rxFormattally"
End With
End With
End With

I modified this from an earlier menu I was using. I've
added another code module to my application, and I want to
use menus to allow my users to select the type of tally
(either Med/Dental Tally or Rx Tally) that they need to
create. What is currently happening is that a "Tally"
menu is created, the first level shows "Select Tally", and
then a submenu that shows "Med/Dental Tally" and "Rx
Tally", but nothing beyond that point. Once I can get
these commands working, I plan to add more commands under
each type of tally.

Thanks, Lee

 

[Felix Rode]

Lee,
I believe the below code is more like what you are looking
for.
Hope this helps,
Felix

Sub Excel_CreateMenuItem()

Dim CustMenu As CommandBar
Dim CustDrop As CommandBarPopup
Dim CustButton As CommandBarButton
Dim CustPopup As CommandBarPopup
Dim CustPopup2 As CommandBarPopup
Dim CustPopup3 As CommandBarPopup
Dim i As Integer

'Create a new menu item in the Excel menu bar
Set CustMenu = Application.CommandBars("Worksheet Menu
Bar")

For Each CustDrop In CustMenu.Controls
i = CustDrop.Index
Next

Set CustDrop = CustMenu.Controls.Add(msoControlPopup,
Before:=i, Temporary:=True)
CustDrop.Caption = "Tally"
CustDrop.Visible = True

Set CustPopup = CustDrop.Controls.Add(msoControlPopup)
CustPopup.Caption = "Select Tally"

Set CustPopup2 = CustPopup.Controls.Add(msoControlPopup)
CustPopup2.Caption = "Med/Dental Tally"

Set CustButton = CustPopup2.Controls.Add(msoControlButton)
CustButton.Caption = "Format Tally"
CustButton.OnAction = "FormatTally"

Set CustButton = CustPopup2.Controls.Add(msoControlButton)
CustButton.Caption = "Add Tally Formulas"
CustButton.OnAction = "MakeFormulas"

Set CustPopup2 = CustPopup.Controls.Add(msoControlPopup)
CustPopup2.Caption = "Rx Tally"

Set CustButton = CustPopup2.Controls.Add(msoControlButton)
CustButton.Caption = "Format Rx Tally"
CustButton.OnAction = "rxFormattally"

'Use CustPopup3 if need to go another level down
End Sub

 

[Dick Kusleika]

Lee

With .Add(msoControlButton)
.Caption = "Med/Dental Tally"
With .Controls
With .Add(msoControlButton)

You're adding an msoControlButton and trying to add controls to it. You
can't. The first With .Add needs to be msoControlPopup.