Resetting Excel to all default and installed options

Hi Dominic

While in Excel go to View>Toolbars>Customize select each menubar and click Reset.

Dave

OzGrid Business Applications

Hi DAve

That Resets everything , but still my Command Bar does not sho wup , when the MAcro is run, and I have tested it on another machine and it works fine. It used to work on mine but not anymore...AAAARRRGGGG
Here is the code
Sub CreateMenu()

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup

Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId

Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")

Call DeleteMenu

Row = 2

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With

Select Case MenuLevel
Case 1
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption

Case 2
If NextLevel = 3 Then
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True

Case 3
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
End Sub
It is based on something from the John Walkenbach site, and worked fine. Have I somehow changed a global option in Excel, is that possible.
The above Macro runs on Worksheet open when it is called by the Worksheet open Macro.

Select Case MenuLevel Case 1 Set MenuObject = Application.CommandBars(1). _ Controls.Add(Type:=msoControlPopup, _ Before:=PositionOrMacro, _ Temporary:=True) MenuObject.Caption = Caption Case 2 If NextLevel = 3 Then Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup) Else Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton) MenuItem.OnAction = PositionOrMacro End If MenuItem.Caption = Caption If FaceId <> "" Then MenuItem.FaceId = FaceId If Divider Then MenuItem.BeginGroup = True Case 3 Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton) SubMenuItem.Caption = Caption SubMenuItem.OnAction = PositionOrMacro If FaceId <> "" Then SubMenuItem.FaceId = FaceId If Divider Then SubMenuItem.BeginGroup = True End Select Row = Row + 1

Do you mean the "Worksheet Menu Bar" ? if so this macro will restore it!

Sub ShowMainMenu()
Application.CommandBars("Worksheet Menu bar").Enabled = True
End Sub

I have some code on my Website under "VBA Tips and Tricks" that my be of interest to you. It hides all Excels menu bars (except your custom one), then restores them to how they were if you close or Window to another Workbook.

Dave

OzGrid Business Applications

It worked......But

Hi Dave
I can now see what I did, When playing around I created a new Menu Bar , that became the"resident/default" bar and it had a different name, thereby not allowing my code to work with the real Menu Bar. I used your code and I also used ------.reset now it is fine. But HOW DO I DELETE THIS "NEW MENUBAR" totally from my system. I have used code as suggested to override it, but is still in my Menu Bar list and I want it OUT. If I go View>Toolbars>Customize the delte option is greyed out.

THanks