Excel 2000 custom toolbar button macro assignment

[Daryl]

Hello,
I have a workbook template with a custom tool bar with command buttons that
have assignments to local macros within the workbook. These files are kept on
a network share.

The issue is when one user has the file up on there PC and another user
tries to open that workbook they receive the "File in use" message box which
give them the options to (Open as read only, notify or cancel) This issue
doesn't occur when you select open as read only or notify, however if you
click cancel and shut down Excel. Next select a another existing file that
was built off of the same template one of the buttons on the custom tool bar
is maintaining a reference to the macro in the workbook that you got the
"file in use" message box. When you click on the link you get the message box
disable or enable macros if close down this message box without clicking one
of the macro options you receive a Excel popup box that says "400" with no
other text. To correct this issue you would have to shut down the workbook
and then go back into it to get it to point to the local macro.

Thanks for your help!

 

[Greg Wilson]

Since you've gone this long without an answer, I'll give you a
less-than-expert opinion, since I'm not an IT guy and don't have significant
experience with workbook sharing and macros.

IMO, the answer is what is commonly done by VBA developers, namely: Create
the toolbar programmatically using wb_open event code and make it Temporary.
The macro assignments will be made each time the wb is opened and point to
those of the local wb. Also add wb_deactivate code to make it disappear or
disabled, and wb_activate code to undo this. An alternative to making it
Temporary is to use wb_beforeclose event code to delete it.

I suspect the situation you describe is similar to what happens when someone
makes a copy using Save As. The macros get reassigned to the new wb in this
event. And when someone opens the original, the macros now point to the new
wb. Excel will attempt to open the new workbook when you try to run a macro.
Toolbar customizations are saved external to the wb in the Excel.xlb file.

I suggest that you google for example code on how to create toolbars
programmatically if you're not familiar. Unless you get a better answer, try
it experimentally.

Greg

 

[Dave Peterson]

Just to add to Greg's response.

If you're building multiple files based on that same template and the code is
the same, I'd remove the code from that template file. I wouldn't want to have
to support who knows how many workbooks that have been created by using that
template file.

I'd put the code into an addin. Tell your users that whenever they open a
workbook that needs that toolbar, to open the addin. (Alternatively, you could
have them install the addin so that it opens whenever the user opens excel.
Maybe you could do some checking to see if your macros should work on the
workbook before continuing.)

Anyway, you'd end up only supporting that one addin. If the user opened the
addin from a common folder on a network drive, then there'd only be one file.

If you had to share files with users, you could tell them to locate the addin:
c:\DarylUtils\addinnamehere.xla

Then if you have to update any code, you could either update that file in the
network drive or just tell the user to update the file on their local drive.

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)