Menu-bar missing when launching Excel

[tbd]

Greetings,
I used VBA to remove menu and tool bars from the first sheet of an
..xls I'm developing, but managed to change Excel's defaults such that when
simply launching excel, the menu-bar is missing!

Does Excel use a configuration text-file I can change with notepad?

Note: I can open Help via the code-page (right-click on 'sheet1'
view code) but can't change worksheet properties there. I can get to
worrksheet menu-bar by opening my VBA code, it runs code to show menus,
though, my attempts to reset default properties have failed.

Thanks/Cheers!

 

[Ron de Bruin]

Hi tbd

This info is in your xlb file

You can rename or delete it to restore your menus

1) Close Excel
2) Do a search for .xlb in Windows (Use: search hidden files and folders)
3) Rename or delete the .xlb file or files (In 2002 the name = Excel10.xlb)
4) Start Excel

Deleting the file or renaming will do no harm to your system
Excel will create a new file for you. (You lost your customization remember that)
If you make your own toolbars or add buttons to the others
this file is important (backup it so you can restore it)

 

[tbd]

Hi Ron,
Thanks for the reply! (however...)
I am unable to locate any ".xlb" files under "\Program Files" or "\Documents
And Settings" - searches included sub directories. I've also "manually"
checked office10 folders several times. My Folder/File settings show
extensions, system and hidden files. I also searched Excel*.* but found only
the .exe, .pif, and a shortcut.

I thought, maybe, excel was using some other file for defaults, so
tried to hide (rename) the folder of my .xls, but that was no help - Excel
still opens showing an empty "Book1" and no file menu!

For the record, I think Excel did present a "are you sure you want to
save these settings" dialog once, yesterday, while I was closing my .xls.

Well, the EXE can't be changing so these settings must be somewhere -
hmm, what about the registry?

Thanks/Cheers!

 

[tbd]

.... In case it helps narrow the problem, I'm using Office/Excel 2002!

 

[tbd]

Tools\Customize for application defaults
.... I think the help refers to tool\OPTIONs, which seems to be
worksheet-specific.

Cheers!

 

[Gord Dibben]

Then you want Excel10.xlb

Are you sure in My Computer>Tools>Folder Options>View you have unchecked
"hide extensions for known file types"?

What happens when you run this macro?

Sub Worksheet_Menu_Hide()
If Application.CommandBars("Worksheet Menu Bar").Enabled = True Then
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Else
Application.CommandBars("Worksheet Menu Bar").Enabled = True
End If
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

If the menu bar was missing how did you get to Tools>Customize?

Tools>Options has global options, workbook options and window options.

No worksheet options........unless you consider the active window to be a
worksheet setting.


Gord

 

[Jon Peltier]

Toolbars do not reside with a worksheet, and with the exception of
attached custom toolbars, they do not reside with workbooks. They are a
part of the Excel Application. Also, built-in toolbars and menus cannot
be deleted, only hidden. I suspect your VBA just went and hid them.

Ron's suggestion to hose your .xlb file is okay if you don't have
toolbar customizations, since those are stored in the .xlb file (and not
in a format that ordinary humans can alter). It would be better to go
back into the VB Editor and restore what you hid.

If you want to hide menus and toolbars when a certain sheet is active,
you should hide them in that sheet's _Activate event, but don't forget
to save the list of hidden elements so you can restore them in that
sheet's _Deactivate event.

- Jon

 

[tbd]

Hi Gord,

Tools>Options has global options, workbook options and window options.
No worksheet options........unless you consider the active window to be a
worksheet setting.

To be honest, it's not clear to me what properties belong to what, probably
because I'm doing a lot of cutting/pasting without studying the objects.
Anyway...

If the menu bar was missing how did you get to Tools>Customize?

I kept opening/running my .xls, which enabled tools/menus for a specific
sheet.
Then I broke the code so even that didn't work! But the VBA editor was
still available (right-click on sheets-tab) so, was able to cut/paste the
tools/menu enable code into workbook_open, on the workbook page for a "Book1"
- and get the menus visible.

Thanks/Cheers!

 

[tbd]

Thanks very much, Jon.
I'm intruiged by "tool-bar customizations" and will
remember that sheet _deactivate is available!

Thanks/Cheers!