macros when multiple excel files running

[schelber]

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

 

[GaryAtWork]

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]

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]

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]

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