MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro problem

Posted by Michael on January 11, 2002 11:47 AM

Is there a way to "turn off" a specific macro or Module that is set to execute on the opening of a workbook(Auto_Open), from a macro that is button activated in another workbook? I have a number of workbooks that "pull" data for Access and JD Edwards when they are first opened/created from a routine in access. However, when the new month rools around and a new one is created, I do not want the other workbooks, if they are opened again because someone wants to print it, to pull down the current months data. Any suggestions? (And no, I can not delete the macros or modules, If something occures, I may need them to update their information again)

Posted by Tom Dickinson on January 11, 2002 1:03 PM

Possible work arounds while wainting for an answer:

Your request may be possible, but I don't have time to test it at the moment. What I would suggest is some possible work arounds such as:

At the end of the month, change the name of the macro (just put another letter in the macro name and it will not run on opening).

Or start the macro with an IF/THEN such as (assuming A1 to be the report's date and B2 to be some data for the month):

If Range("B2") = Empty or Month(Range("A1")) = Month(Now()) then
The body of the program
End if
End Sub

Hope this helps

Posted by faster on January 11, 2002 1:18 PM

Re: Possible work arounds while wainting for an answer:

You may be able to hold the SHIFT key down while opening
the file. This may bypass the startup macro.

I would suggest the following however. Place this
code at the start of your automacro. It is a Yes/No
messagebox. Just click No if you don't want the code
to run.

If MsgBox("Run Startup Macro", vbYesNo) = vbNo Then
Exit Sub
End If