macros when multiple excel files running

schelber

Board Regular
Joined
Oct 22, 2005
Messages
172
I have a couple of macros in a workbook of mine that utilize the application.ontime feature in excel and run every 5 minutes automatically

When running by itself it works as it was designed , but if i open another file in excel and the macro from the 1st tries to run everything crashes.

Any ideas on how to make sure that a macro from one file isnt interfering with other excel files that may be open

Thanks

Rich
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

GaryAtWork

Board Regular
Joined
Dec 19, 2005
Messages
83
If the macro is supposed to run on a workbook that isn't active, then you either need to activate the workbook at the start of the macro, or prefix all of the cell references with Workbooks("Name").

I'm only a newbie to vba, so wise people please correct me if I'm wrong.
 

schelber

Board Regular
Joined
Oct 22, 2005
Messages
172
i assume that when you say active you mean up and in the foreground overlay.

If i have an excel file up with no macros and it is active ......the excel file that is minimized with macros will fire off a macro that i have on a timer and cause all kinds of problems with the excel file that is currently active without macros.

Is this a bit clearer?

I basically want no interference from a macro in one excel file while it is running minimized .......when i have other excel files up that have no macros.
 

GaryAtWork

Board Regular
Joined
Dec 19, 2005
Messages
83
If you don't want to bring the file with the macro to the foreground then I think you have to reference the workbook itself in your macro.

For example, if your file with the macro is called Book1 and you have a line that says
Range("A1").Value = 100
you would need to change it to read
Workbooks("Book1").Range("A1").Value = 100
 

pfarmer

Well-known Member
Joined
Jul 6, 2005
Messages
550
schelber said:
i assume that when you say active you mean up and in the foreground overlay.

If i have an excel file up with no macros and it is active ......the excel file that is minimized with macros will fire off a macro that i have on a timer and cause all kinds of problems with the excel file that is currently active without macros.

Is this a bit clearer?

I basically want no interference from a macro in one excel file while it is running minimized .......when i have other excel files up that have no macros.

Consider doing the following:

Any function that operates on a range, cell, etc. needs to be reference by the workbook the macro is located in. To do this use ThisWorkBook.

For example if you have a line in your macro that is:

wb=ThisWorkBook.Name

Then you can use this variable preceding a Worksheets and range statement.

Or you could skip this depending on how often you will be using it and go with something like:

ThisWorkBook.Worksheets("Sheet1").Range("A1")="Example"

This would then place the value "Example" in A1 on Sheet1 of the workbook that contained the macro.

Two benefits of using ThisWorkBook over stating the name of the workbook is one the code becomes portable between projects, and two it still functions if you change the name of the workbook.

Perry
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,379
Members
412,589
Latest member
ArtBOM
Top