Macros, Buttons & the XLB file.

[pa_broon74]

Hey folks.

Looking for some help with a custom toolbar, I have three macros
stored on one spreadsheet, its contained within the XLstart file as it
should be. I've assigned each macro a custom button on a new toolbar.
Apart from the fact that you can't seem to move an XLB file to another
user's PC succcessfully it also seems that this particular user's
instance of Excel isn't saving the toolbar preferences when excel is
closed. (I'm told that the XLB file is updated each time excel is shut
down.)

I'm not a code expert, I'd prefer not to have to record the macros
again. I suppose my questions are thus:

A: Why would the XLB file not be getting updated on excel closure?

and

B: Why with the existing XLB file when any of the buttons are pressed
do they try to open the spreadsheet containing the macros and not run
them. (The sheet is already open, it being in the XLstart file.)

Being as I am a somewhat simple person, I'd appreciate any simple
answers you might have to offer. (As would our IT department as I'm
considering throwing my computer out of a window

Thanks.

 

[Bob Phillips]

Don't know about A, but when you add buttons in this way, they point at the
original source. If anything gets moved, It is better to add code to the
workbook that builds the toolbars dynamically.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

First, I agree with what Bob wrote.

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)

======
My guesses (and only guesses) on the other stuff...

A. The user may not have write permission to that folder.
The *.xlb file could be marked readonly.
Excel could be working fine and saving the *.xlb file.

But the user could have other *.xlb files that open after the updated file gets
open. The last *.xlb file that opens will be the one you see. But it may not
be the one that gets updated when excel closes.

I'd search for all the *.xlb files (look in hidden folders, too!) and
move/delete them to a safe spot and then see what happens.

B. Yep. If the workbook assigned to that button isn't open, then excel will
try to open it. And if the user is prompted and allows macros to run (or has a
"nice" security setting), then the macro will run.

The bad news is that if that button points to a different location on the user's
pc than on your pc, then there could be major trouble.

That's why Bob recommends building the toolbar on the fly.

 

[pa_broon74]

At the moment, I'm just creating the toolbar and buttons on each
user's PC (only three of them.) I suppose the XLB question is a bit
moot since, even when the toolbar does appear the buttons don't work
anyway.

Thanks for the help.

Just had a thought, the buttons will point to C:\...myusername\app data
\microsoft etc etc etc... For it to work on their PC, it'll need to
point to their username folder in Documents and Settings... Mmm... I
wonder if you can deallocate a button's attributes (take the macro
away) then re-add...

I've redone the buttons anyway but I hate to get beaten... :-|

Thanks again.

 

[Dave Peterson]

Yep. You can reassign the buttons to the new location.

But it seems to me that you could spend time doing something like that or you
could spend the equivalent time to make the code that adds the buttons when the
workbook opens.

I'd spend time on the second option.