Help - Excel VBA Menus

  • Thread starter Thread starter RJSUK
  • Start date Start date
R

RJSUK

I inherited an Excel workbook with Excel 4 macros. I have rewritten it
all, but have one o/s problem. The XLM macros Add.Menu & Delete.Menu
seems not to have an equivalent in VBA. i.e. I want to add a new top
level menu to the Menu bar say titled "Choice". To do this with an XLM
you use =ADD.MENU(1,Choice,9) and this will insert a new menu as defined
before position 9 on the menu bar. How can this be done in VBA.
Equally, to delete the menu "Choice" you use =DELETE.MENU(1,"Choice").
How can this be done in VBA.
Thanks in anticipation of help

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
One way:

With CommandBars("Worksheet Menu Bar")
With .Controls.Add( _
Type:=msoControlPopup, _
Before:=9, _
Temporary:=True)
.Caption = "Choice"
.Tag = "MyChoiceMenu"
' other properties here
End With
End With

to delete:

Application.CommandBars("Worksheet Menu Bar").FindControl( _
Tag:="MyChoiceMenu").Delete

When working with controls, I prefer to use Tags rather than rely on the
Caption property since users can modify the captions easily.
 
Thanks for help,
Hav tried solution proffered, but can not add 2nd tier as with Excel 4
macro using following VBA statements
Set newItem =
CommandBars("Choice").Controls.Add(Type:=msoControlButton)
With newItem
.BeginGroup = True
.Caption = "Rons Line"
.FaceId = 0
.OnAction = "Rons_Line"
End With
Can anyone help further.
Thanks

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
One way (going back to your original post for some info):

With CommandBars("Worksheet Menu Bar")
With .Controls.Add( _
Type:=msoControlPopup, _
Before:=9, _
Temporary:=True)
.Caption = "Choice"
.Tag = "MyChoiceMenu"
With .Controls
With .Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Rons Line"
.FaceID = 0
.OnAction = "Rons_Line"
End With
With .Add(Type:=msoControlButton)
.Caption = "Item 2"
.FaceID = 0
.OnAction = "Do Something 2"
End With
With .Add(Type:=msoControlButton)
.Caption = "Item 3"
.FaceID = 0
.OnAction = "Do Something 3"
End With
End With
End With
End With
 
Back
Top