How to stop a Macro either if it has been run already or if a there is a value in a particular cell

Question

I'm using Auto_Open on a Excel Template (.xlt) to run a macro which imports some data to a sperate sheet, adds some forula and then creates a number of pivot tables and charts. The result of which is a finalised report which can then be saved as .xls.

I have added the following IF statement to the top of the Auto_Open sub to check cell A2 if it contains "Service Activity Report" (this is a merged cell from A2:N2) and if it does to Exit the Macro. This is so the Macro will not run for second time after the report has been generated.

If ActiveSheet.Range("A2").Text = "Service Activity Report" Then
Exit Sub
Else

Two Questions:

Is this the best way to stop the macro running for a second time and overwriting the finalised report?

The Macro only works in Excel 2007 due to the nature of the charts yet the code above is being skipped over in Excel 2003

Any ideas?

Answer 1

1. I'm not exactly sure what you are checking. Does ActiveSheet refer to the Excel template or the finalized report? If this cell is created for the finalized report and is simply checking to see if it exists, thus indicating the final report has been completed, I wouldn't necessarily change it. It may not be the "best" way to accomplish that task, but when working with VBA and Office, it can often be difficult to find a "best" way to accomplish tricky things.
2. I've never used Auto_Open before in Excel 2007, so I can't say whether or not it is available in Excel 2003. However, I have used Workbook_Open() before to run code when a Workbook starts up. You should be able to see that method if you navigate to ThisWorkbook in the Visual Basic explorer window. On the right window, change (General) to be Workbook and you should now see the Workbook_Open() method.

Answer 2

No expert here by any means, but...

1. If you're not calling another Sub after the "Auto_Open" Sub, then I would use "End" instead of "Exit Sub". This will ensure that that no further code will execute if "ActiveSheet.Range("A2").Text = "Service Activity Report" = True.

2. Survey says "Auto_Open" was superceeded by "Workbook_Open" in Excel 2003, so it might not be supported anymore...

Cheers!

Answer 3

You might also want to consider being more specific than 'ActiveSheet,' just in case the workbook was saved with a different sheet active. You could give A2 a name - say 'ReportTitle' - which means it doesn't matter what sheet is active:

If Range("ReportTitle").Text = "Service Activity Report" Then 
    Exit Sub
Else
   ' Code to do whatever you are doing...
End if

And since you actually want to do nothing if your test is true and something if it is false, you might want to consider:

If Not (Range("ReportTitle").Text = "Service Activity Report") Then 
   ' Code to do whatever you are doing...
End if