PDA

View Full Version : Restore "lost" custom menus?



TomSmith
11-01-2007, 09:30 AM
Hello, I made the mistake of allowing macros to run on a commercial Excel spreadsheet I had downloaded to try out. Now a number of my custom menus are gone and I have no idea where they are or how to get them back.

Any idea on where (or if) Excel 2002 stores custom menus (not toolbars)?

:( :( :banghead: :motz2:

TomSmith
11-01-2007, 09:34 AM
Also, I've been meaning to do this for awhile due to the risk of loss of these menues. Can someone post a sample "template" of how to create menus using VBA? I used to do this in the "old" Excel macro method but never got around to doing this in VBA.

While I'm at it, will these methods even work in new Excel for Vista?

Bob Phillips
11-01-2007, 09:38 AM
Here is a starter



Sub MultiLevelMenus()
Dim oCb As CommandBar
Dim oCtl1 As CommandBarPopup
Dim oCtl2 As CommandBarPopup
Dim oCtl3 As CommandBarPopup
Dim oCtlBtn As CommandBarButton

Set oCb = Application.CommandBars("Worksheet Menu Bar")
With oCb
Set oCtl1 = .Controls("Tools").Controls.Add( _
Type:=msoControlPopup, _
temporary:=True)
oCtl1.Caption = "Level1"
With oCtl1
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton)
oCtlBtn.Caption = "Level1 Button1"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myLevel1Button1Macro"
With oCtl1
Set oCtl2 = .Controls.Add( _
Type:=msoControlPopup)
oCtl2.Caption = "Level2"
With oCtl2
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton)
oCtlBtn.Caption = "Level2 Button1"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myLevel2Button1Macro"
Set oCtl3 = .Controls.Add( _
Type:=msoControlPopup)
oCtl3.Caption = "Level3"
With oCtl3
Set oCtlBtn = .Controls.Add( _
Type:=msoControlButton)
oCtlBtn.Caption = "Level3 Button1"
oCtlBtn.FaceId = 161
oCtlBtn.OnAction = "myLevel3Button1Macro"
End With
End With
End With
'etc.
End With
End With


Vista yes, Excel 2007, well srt of. They create, but theyare part of the addins tab, you don't get menus as you do in 2003.