Same macro in two files

  • Thread starter Thread starter David Miller
  • Start date Start date
D

David Miller

I have written a VBA macro in an excel file. If a user
loads the excel file, and a renamed version of the same
file Excel uses the macro from the last file loaded. If
the last file loaded is closed first, and then the macro
is again used, Excel wants to load the second file again.

If I have been unclear, perhaps this step-by-step view
will help:

Load file 1:
Load file 2:
Run Macro A
Close file 2:
Run Macro A (it exists in file 1)
Excel wants to load file 2

Is there a way to associate the macro only to the
particular XLS workbook?

Thank you.
Dave Miller
 
I suspect that its the assignment that's gone awry. Its
unlikely that the code itself is tring to force another
file open, but check the code.
delete any objects that call the macro then re-create
them.


HTH
Patrick Molloy
Microsoft Excel MVP
 
Hi David,
I have written a VBA macro in an excel file. If a user
loads the excel file, and a renamed version of the same
file Excel uses the macro from the last file loaded. If
the last file loaded is closed first, and then the macro
is again used, Excel wants to load the second file again.
Click to expand...

You have probably assigned this macro to a toolbar button, right?
When you do a save-as, excel checks the toolbar buttons and changes
their attached macro to the last saved-as version. Your buttons will
point to the macro in the second file now.
To avoid that, you need to attach the toolbar containing the button (to
do that you have to create a custom bar) to the workbook in question.
Then you need code that deletes the toolbar upon closure of the
workbook.

Here is some general information:

Excel keeps toolbar and menubar customizations in a file with the
extension .xlb. The exact filename depends on Excel version and
install, but usually is: Excel9.xlb or Excel.xlb or Username8.xlb.
Often this file can be found in your WINDOWS directory.

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
 
Back
Top