Have Macro run when spreadsheet opens 1

[dokken]

Does anybody know how I do this?

Paul

 

[DaleWatson123321]

Hi Paul,

The following is the examples given from within Excel Help.

This example opens the workbook Analysis.xls and then runs its Autpen macro.

Workbooks.Open "ANALYSIS.XLS"
ActiveWorkbook.RunAutoMacros xlAutoOpen

This example runs the Auto_Close macro for the active workbook and then closes the workbook.

With ActiveWorkbook


End With


Hope this helps,

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[dokken]

I guess I was wondering how I specify that a macro is autorun when the spreadsheet opens?

 

[DaleWatson123321]

Hi Steve,

The simplest method is to type:

&quot;Private Sub Workbook_Open&quot; <Enter>

You'll notice that the &quot;_&quot; is REQUIRED


Hope this is what you needed. Let me know if you have any problem.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[dokken]

What tells Excel that it should autorun? Is it any marco with a name followed by _Open?

 

[DaleWatson123321]

No, not any macro followed by _Open. It HAS to be &quot;Private Sub Workbook_Open().

Try it, and you'll see that it works. Implicitly, you will need to include your code within this routine - i.e. after &quot;Private Sub Workbook_Open()&quot; and before &quot;End Sub&quot;.

Let me know how you make out.

Regards, ...Dale Watson

 

[Databaseguy]

Actually, if you go to the VBE (Visual Basic Editor, Alt+F11), double click on the This WorkBook in the left hand pane, and select workbook from the drop don list towards the top. Type your code in there.

Also, be VERY careful what you put in there. JV had a spreadsheet where someone had typed:

Private Sub Workbook_Open()
Application.Quit
End Sub

(I Think. Never could open the %$^%^* thing) Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[DaleWatson123321]

Thanks Ty, for contributing. You are indeed right about the need to select &quot;ThisWorkbook&quot;. I had too many interruptions today, and didn't focus long enough to cover the entire picture.

Sorry, Paul, for not getting it right the first time.

By the way, Ty, can you also share the method of creating an &quot;On Close&quot; routine. I have yet to create an Excel application which requires this, but it is nonetheless something I would like to know for the future. I have tried Workbook_Close, but it doesn't work. Can you help ?

Thanks very much.

...Dale Watson dwatson@bsi.gov.mb.ca

 

[Databaseguy]

Well, there's not much you can do with close, except check for errors and cancel the close. Example:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Somevalue <> &quot;What you want&quot; Then
Cancel = True
Else
Cancel = False
End Sub

Hope this helps.

Ty Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[DaleWatson123321]

Thanks Ty, ...much appreciated.

...Dale