I have written a VBA script that is very specific to a workbook. I have no problems running the VBA script on my machine, but when I give the excel.xls file to another user by copying it to their machine, the VBA script tries to open another workbook. When the user opens the copy of the .xls file all the data is fine. They then run the script via a menu button and it seems to be attempting to open the original workbook on my machine rather than the workbook in session, which is an exact copy of the workbook on my machine. How can I share the workbook and the VBA script together since they are dependant on each other?

Hi Steve,

How can I share the workbook and the VBA script together since they are dependant on each other?

Your problem has nothing to do with the location of the VBA code but rather with the reference of
a button to a macro in that code.

A small instruction about distributing macros:

Many users tend to put their macros in their personal.xls file. Nothing wrong with that, because
that is what it is there for. But what if you like your macros so much you decide others might
benefit?

You might be tempted to copy your personal.xls onto a floppy and give that to others. Don't!
Also don't copy your xlb file to others to give them your toolbars, you will overwrite their
customisations..

I would be very distressed if you would come and hijack my personal.xls and excel.xlb files!

What you should do is create a new workbook with all your code *and toolbars* and distribute
*that* file (maybe saved as add-in).

About toolbars:

You can attach a toolbar to a workbook. When this workbook is loaded, XL checks if the toolbar is
on the system. If not, it copies the toolbar from the workbook to the system.

After creating *or changing* the toolbar, you should attach the toolbar to your workbook:

- activate the workbook to which you want to attach the toolbar
- Rightclick the toolbar, select 'customize'
- Click 'Attach' (Toolbars Tab)
- If the workbook already contains a toolbar by that name, delete it first by clicking on it on
the righthand side and choosing Delete.
- Select your toolbar (on the left) and press 'copy'
- Save the workbook (optionally: save_as an add-in).

Also, You should include code that deletes the toolbar when your workbook or add-in is closed, so
that when you deliver a new version of your workbook the new toolbar will be used i.s.o the old
one. You can do that in the Thisworkbook module, using the Workbook_beforeClose event:

Private Sub Workbook_BeforeClose(Cancel as Boolean)
On Error Resume Next 'In case Toolbar is absent
Application.CommandBars("YourBarsName").Delete
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

I would have never thought it could be the toolbar.

I attached the toolbar to the workbook and it works like a charm now

Thanks
Steve