PDA

View Full Version : Menu issues



sasa
03-05-2012, 01:31 AM
Pls, Someone can help me to create a SubRemoveMenus, the way I can add my Menus and Remove my Menus every time Excel start without create a duplicate.
I have this one but It seems does not work


Sub RemoveMenu()

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("List").Delete
On Error GoTo 0

End Sub





Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim cbcCutomMenu As CommandBarControl

Set cbMainMenuBar = Application.CommandBars("Custom 1")
With cbMainMenuBar.Controls.Add(Type:=msoControlPopup)
.Caption = "&New Menu"

With .Controls.Add(Type:=msoControlButton)
.Caption = "Menu 1"
.OnAction = "MyMacro1"
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Menu 2"
.OnAction = "MyMacro2"
End With
End With
End Sub
Thanks in advance

Sasa

Bob Phillips
03-05-2012, 02:49 AM
Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim cbcCutomMenu As CommandBarControl

Call RemoveMenu

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
Set cMenu1 = cbMainMenuBar.Controls.Add(Type:=msoControlPopup)
With cMenu1

.Caption = "Custom 1"
With .Controls.Add(Type:=msoControlPopup)
.Caption = "&New Menu"

With .Controls.Add(Type:=msoControlButton)
.Caption = "Menu 1"
.OnAction = "MyMacro1"
End With

With .Controls.Add(Type:=msoControlButton)
.Caption = "Menu 2"
.OnAction = "MyMacro2"
End With
End With
End With
End Sub

Sub RemoveMenu()

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Custom 1").Delete
On Error GoTo 0
End Sub