Lost new custom toolbar button with its macro

[hmm]

On more than one occasion, I recorded a new macro and assigned it a custom
toolbar button, only to have them both disappear when I next open Excel.

My question: when does Excel actually save new macros (in PERSONAL.xls) and
new toolbar buttons? Do I need to save after creating a new toolbar button,
and if so how?

 

[Mike H]

Recording a macro doesn't necessarily mean it will automatically be available
when you open Excel.

To make it available in all workbooks 'you' open you can save it in
personal.xls but you must tell excel to do this. If you are recording the
macro select personal.xls for the save location or if you are writing it
manually create the module in personal.xls.

If the macro is to be available when 'anyone' opens a particular workbook
then the macro must be in the workbook. If you want a toolbar button then you
will have to load that using something like the workbook_open event.

Mike

 

[Gord Dibben]

Customizations to Toolbars and Menus are saved in your Excel11.xlb file.

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

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

but if on a network may be somewhere else. Do a file search for *.xlb if need
be. You may have more than one so note which one changes when you make a new
customization.

Make a backup copy when good so's you can recover.

For added info from Dave Peterson............................

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

End Dave quote..........................................


Gord Dibben MS Excel MVP

 

[hmm]

Thanks Gord.

I actually found the file, in the folder you indicated, called Excel. xlb.

When you add a new button to your toolbar, when and how does Excel save it
to Excel. xlb?

 

[Gord Dibben]

After you customize a menu or toolbar, upon closing Excel will save the changes
to your *.xlb file.

How................assume the developers know, but I don't.


Gord

 

[hmm]

Ah, therein lies the problem. If Excel crashes, as it often does, any
changes will not be saved. It would be nice if they could let you save the
toolbar changes.

Thanks for your help.

 

[Gord Dibben]

I have about 2 Excel crashes per annum.......user-generated BTW

Perhaps you should review how you are using Excel and what state your OS is in.

I would backup the current *.xlb file after a non-crashing close of Excel. At
least you would have a good file up to that point you could use.


Gord