Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Same macro in two files
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Same macro in two files
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Same macro in two files
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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run a DOS .bat files under an excel macro | Excel Programming | |||
Opening CSV files with VBA/macro | Excel Programming | |||
Import multiple files macro can't find files | Excel Programming | |||
Email files using macro | Excel Programming | |||
Run Macro on many files at once | Excel Programming |