Custom Toolbars Disappear

[c mateland]

Periodically, I open Excel to find my custom toolbars are gone and no
longer in the Toolbars list. This has occured for a long time, but
it's really getting on my nerves now. I'm tired of rebuilding them.

Any advice on what could be causing this, how to prevent it or
otherwise deal with it?

What's the best way to backup my toolbars so at least I can get them
back?

Thanks,
Chuck

 

[c mateland]

Sorry...

Excel 2003
WinXP sp2

Also, I just noticed it nuked my Personal workbook too. I lost all my
routines I had stored there.

What could be causing this?

-Chuck

 

[Gord Dibben]

Menu and Toolbar customizations are stored in your Excelx.xlb file

"x" is a version number like 9 for 2000, 10 for 2002 and 11 for 2003.

If it gets too large, it can become unstable.

Or perhaps if on a network, Excel is confused about where to store it.

Do a search for this file. There could be more than one *.xlb file so may have
to check all copies to see which is loading.

Usually stored in..................

C:\Documents and Settings\username\Application Data\Microsoft\Excel

When found, first rename the *.XLB file to *.OLD file. Note the size of this
file in kb.

Now close out Excel then re-open.

Make a few minor changes to a menu or toolbar then close Excel.

Does Excel create a new *.XLB file?

What is the size compared to the *.OLD file?

Reopen Excel. Have your changes stuck?

As far as Personal.xls being nuked by "it", have a look under Help>About MS
Excel>Disabled Items.

Perhaps Excel thought it was not nice and banished it to the bad dog box.

If found there, hit "enable" to restore.


Gord Dibben MS Excel MVP

 

[c mateland]

Thanks, Gord.

"It" is this issue, whatever is causing it to happen. I'm loosing my
toolbars and personal.xls from loading each time "it" happens. They're
obviously related somehow because they get nuked at the same time.

My Personal.xls is only 25kb. That's not real large, is it? And it
contained no auto_... macros. In fact it had only one simple macro.

I'm not on a network, just a stand alone desktop.

I did the Help>About MS> Excel>Disabled Items and, sure enough, I
found the Personal.xls there. It had a note stating Excel had trouble
starting so disabled it. I enabled it, and it loaded my personal.xls,
plus I got my toolbars back and all my menu settings. How are those
related to disabling my personal.xls?

What possibly could Excel be having a problem with to periodically
disable my personal.xls and my customizations? How can I prevent or
better manage that?

Thanks,
Chuck

 

[Gord Dibben]

The "too large" referred to the Excelx.XLB file, not the Personal.xls.

What version of Excel are you running and when did you create Personal.xls?

In older versions..........before XL97.............. Excel had a menu editor and
customized toolbars and menus were stored in Personal.xls.

If it did not load, the customizations would not show up.

If this is the case, you could go to the trouble of converting but sounds like
you should just re-build Personal.xls and delete your current *.XLB file and
start over.

For conversion of the older menu editor setup you can use a Jim Rech add-in.

http://www.oaltd.co.uk/MVP/Default.htm

Look for Jim's RemoveMenus add-in.

As far as why does Excel periodically disable Personal.xls..............hard to
say but usually due to some code Excel doesn't like or your Macro Security is
set to "High"

If it loads most of the time I would rule out the Security issue.


Gord

 

[c mateland]

I'm using Excel 2003 sp3 with WinXP sp2.

My Excel11.xlb is 48 kb. Is that big?

I created the PERSONAL.XLS fresh upon loading Excel 2003. It wasn't
built in Excel 97 for sure. I don't see how Excel could have a problem
with the code because it's only macro is the well-known sort
worksheets routine I got from this group. That's all that's in it. And
my security is always set to medium.

It appears I've rebuilt my xlb file before. I'm showing a
Excel11OLD.xlb in the folder that I created back last year and let
Excel build a new one. Yet, I'm still having the problem. I have many
custom tools and bars in my xlb, and so it's a real time-consuming
difficult task to rebuild these tools from scratch. I've done it
before trying to work this problem out, but it still occurs, so I'm
afraid it'd be a waste of effort.

I'll just watch what happens now that I have it back, but I'm very
curious why my tool settings and customizations disappear when Excel
disables my personal.xls? Why is that? Frankly, I could live without
my personal.xls, but NOT my tool customizations.

-Chuck

 

[c mateland]

I'm sorry, Gord, that should have read "Excel11xlb.OLD" below. (In
case that lead you to another thought.)

-Chuck

 

[Gord Dibben]

See in-line..........

Do you have code in Personal.xls that creates the toolbars

I'm using Excel 2003 sp3 with WinXP sp2.

My Excel11.xlb is 48 kb. Is that big?

I wouldn't consider it large.

I created the PERSONAL.XLS fresh upon loading Excel 2003. It wasn't
built in Excel 97 for sure. I don't see how Excel could have a problem
with the code because it's only macro is the well-known sort
worksheets routine I got from this group. That's all that's in it. And
my security is always set to medium.

Strange that Excel would disable Personal.xls. Excel should condiser
Personal.xls to trusted even if security is set on High or Very High.

It appears I've rebuilt my xlb file before. I'm showing a
Excel11OLD.xlb in the folder that I created back last year and let
Excel build a new one. Yet, I'm still having the problem. I have many
custom tools and bars in my xlb, and so it's a real time-consuming
difficult task to rebuild these tools from scratch. I've done it
before trying to work this problem out, but it still occurs, so I'm
afraid it'd be a waste of effort.

I'll just watch what happens now that I have it back,

When Personal.xls is not disabled what happens if you move it out of your
XLSTART folder?

but I'm very
curious why my tool settings and customizations disappear when Excel
disables my personal.xls? Why is that?

All I can think of is that you have code in Personal.xls that creates the
toolbar customizations.

Frankly, I could live without
my personal.xls, but NOT my tool customizations.

I don't even have a Personal.xls and none of my customizations disappear so
don't know what's going on with yours.

Keep eye on this thread. Someone might have a better idea than I have.


Gord

 

[Dave Peterson]

I don't think it's a security measure (against the macros in personal.xls). I
think excel actually thinks that that file is getting corrupted--just like if
you had trouble opening it manually.

But sometimes, xl gives a false positive and just removing the file from the
quarantined list is enough.

But if it keeps getting quarantined, then maybe it's time to rebuild that
personal.xls file--or dump that version and copy your backup version into your
XLStart folder.

As for the toolbar, 48k doesn't sound like a lot. But I bet it is a bit
bloated. If you delete (or rename it), then restart excel and rebuild it, I bet
it'll be less than half that size. (I hit close to 500kb before I found out it
could cause trouble--when I rebuilt it, it was down to less than 15kb.)

I'd make a note of the icons you like and then dump that *.xlb and rebuild it.
Once you have it perfect, make a copy and put it in a nice backup location
(along with a backup of personal.xls!).

Then if you have trouble, you can always just copy the backup files back to
where they need to be.