Excel 2000 Menu Tools-Options does not work

[Arie Sukendro]

Hi,
my excel 2000 menu item Tools-Options currently does not work.
It does not show the options form.
Has anybody experienced this or maybe have some suggestions?

Thank you.
Ari

 

[Dave Peterson]

uh....good point. (watta dope I am!)

But since resetting the worksheet menubar didn't help, I doubt just resettion
(tools (on the menubar itself) wouldn't help either).

Excel keeps these kinds of customizations in a file with an extension of .xlb.
Close excel and use windows start button|Find|Files or folders to search for
*.xlb.

You might have to tell Find to search through hidden folders for hidden files.
Once you find it, rename it to *.xlbOLD.

Then start excel and see if that helps.

If it did, dump the .xlbold and recreate any customized changes at your leisure.

(I guess that first response was kind of a catch-22. If you could find it, you
wouldn't need to reset it!)

 

[Arie]

I tried renaming the excel.xlb and restarted Excel but it still doesn't
work.
I even tried to reinstalling excel but also didn't help.

I don't think this is caused by customization of the menu but more like the
VBA code to open the "Options" screen got corrupted.

Any other suggestions?

Thanks

 

[Dave Peterson]

First, make sure you rename all you *.xlb files. You might have more than you
first noticed.

If that wasn't it, maybe you have a macro that runs and disables that option.
But if you successfully renamed the real *.xlb file, then this macro has to run
each time excel opens.

Try starting excel in Safe mode:
Windows Start Button|Run
Excel /safe

This tells excel to load in vanilla mode.

If you have Tools|options enabled, the next thing to do is to close excel and
reopen it normally.

Then find out what other files are being loaded when excel starts up.

I like to hit Alt-F11 to get to the VBE (where macros live).
Then hit ctrl-R to view the project explorer (like windows explorer)

See what is loaded at startup.

I see this in mine:
atpvbaen.xls (ATPVBAEN.XLS) (analysis toolpak for VBA stuff)
funcres (FUNCRES.XLA) (anaylsis toolpak for Excel stuff)
personal (personal.xla) (my own set of macros/functions)
VBAProject (book1) (initial empty workbook)

Now you have to find the culprit.

Close excel.
Move all your workbooks out of your XLStart folder.
Then add one back at a time.
Restart excel to see if the problem occurred.
If it did, you've found the problem

Chip Pearson has some detailed instructions to help diagnose these startup
errors at:
http://www.cpearson.com/excel/StartupErrors.htm

I tried renaming the excel.xlb and restarted Excel but it still doesn't
work.
I even tried to reinstalling excel but also didn't help.

I don't think this is caused by customization of the menu but more like the
VBA code to open the "Options" screen got corrupted.

Any other suggestions?

Thanks

 

[Dave Peterson]

I still think it's in a *.xlb file. I'd logon using the problem id and search
for *.xlb once more.

Make sure you look through all hidden folders and for hidden files.

In fact, start excel and create a new toolbar. Add any icon you want to it.

Close excel and look for *.xlb. If you don't find that one, then something is
wrong, since you just created it. If you do find it, delete it and then start
excel once more.

But that's all I have. Maybe someone else will jump in with more info.

Dave,
I tried all your suggestions + Chip Pearson's + uninstalling & installing
Excel but I still can't solve the problem.
Only when I did Safe mode, then it worked, but it doesn't work with normal
mode (without any addins or xlstarts).

Since the problem is not solved even by uninstalling & reinstalling Excel, I
suspect for something in registry or other temp files. Plus, it works fine
if I started Excel using different Login.

Any idea?

Thanks
Arie

 

[Arie Sukendro]

Dave,
Thank you very much for all your suggestions.

I finally decided to delete all my local settings and create a new one. It
solved the problem, despite the hassle that came with it.

Arie

 

[Dave Peterson]

I ain't giving up that easy.

I bet you that your *.xlb file was hidden in your local settings folder
(somewhere). So when you deleted all the settings, the *.xlb file got deleted,
too.

(You may never know for sure, but it's my story and I'm sticking with it!)

Glad you're up and running again.

ps. Did you customize your new toolbar and then try to find it? It's not a bad
idea to know where this file is located and to have a backup. When the *.xlb
file gets corrupted, it can cause bad things (crashes, slow openings/slow
closings) in excel.

I keep a backup of my file. Every so often I just plop the backup copy into the
"live" folder.

Dave,
Thank you very much for all your suggestions.

I finally decided to delete all my local settings and create a new one. It
solved the problem, despite the hassle that came with it.

Arie