Auto run when loading a spreadsheet

alantodd · October 26th, 2004, 08:39 PM

Hi All,

Totally baffled here, I want to execute a routine when a spreadsheet is opened and tried a lot of things like naming the routine AutoExec AutoRun and AutoStart. None work. Am I barking up the wrong tree? MS Help doesn't.

Am I just dreaming that this is how it is done?

TIA.

Alan

BrianB · October 27th, 2004, 03:22 AM

"Open a spreadsheet" is not technically correct.
1. You can open a workbook (.xls file).
To get a macro to run with this event go to VBA Editor. View menu/Project Explorer. Doubleclick ThisWorkbook. Select the Open event from dropdown bo top right which gives a macro outlinr :-

Code:

Private Sub Workbook_Open()
    ' put code here
End Sub

2. To trap the event of changing from one worksheet to another use the SheetChange event in the same place :-

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
   ' put code here
End Sub

-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.

alantodd · October 27th, 2004, 03:27 AM

Well said.

maccas · October 27th, 2004, 03:29 AM

Alan,

There are at least a couple of ways to go about this.

Firstly you could use an

Code:

Auto_open

subroutine - tantilisingly close to variants you were trying earlier. The syntax is as follows. Place a subroutine in any code module as shown below, the code in the subroutine will automatically be executed when the file is opened. This is the prefered method of executing code in an add-in. If this method is used in an ordinary spreadsheet and you open that spreadsheet programatically (i.e. use Workbooks.Open in VBA code to open it) the Auto_open sub will not run (just put another line of code calling the auto_open sub in below to specifically get round this limitation)

Code:

Sub Auto_open
    ' Code I want to run when this workbook opens
End Sub

Alternatively you could use the Workbook_Open event. To use this method, navigate to the ThisWorkbook code sheet (under Microsoft Excel Objects in the Visual Basic Editor). Immeaditaly above the code window there should be two drop-down boxes side-by-side saying (General) & (Declarations). From the drop-down box saying (General), left-hand one, select the option Workbook. Upon doing this VBA should automatically create a Workbook_Open event subroutine for you, see below. If Excel has created another event subroutine for you (or if you want to make Excel do something before saving, for example) select the required event from the right-hand drop-down box, and hey-presto you should be done. Alternativly you could just paste the following code into the ThisWorbook code sheet. The weakness of this method is that it will not fire if

Code:

Application.EnableEvents

has been set to False.

Code:

Private Sub Workbook_Open()
    ' Code I want to run when this workbook opens
End Sub

HTH,
Maccas

alantodd · October 27th, 2004, 03:36 AM

Hi Guys,

Thanks for the suggestions.

My earlier post may seem strange, but when I read the post, it was blank. Weird!

I have just tried the Workbook_Open() method and this appears perfect for our needs. I knew it would be there somewhere, just couldn't find it.

Thanks again.

Alan

adealey · June 27th, 2009, 02:59 AM

Excellent answer. But it raises another question... why is auto_open the preferred method for an add-in?