Web Development

Ask something
Discussion Locked

Excel 2003 WorkbookOpen

0 helpful
3 replies

by vosa 2 views

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I use Excel 2003 and would like to do a few initializations when my workbook opens. In WORD I use AutoOpen macro for that purpose, but it is not available in Excel. So I followed instructions given in Help for VB, created a new Class module named Class1 (it is not possible to rename it) and wrote the code below as a first probe. I saved the workbook and opening no message box appeared. (Nothing happend and I wonder as under the lemmon tree). Where I made a mistake? Thanks for any advice. Vosa
----------------------------

Public WithEvents App As Application
Dim X As New Class1

Sub InitializeApp()
Set X.App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox ("START")
End Sub

3 replies

by tink!

There's a much simpler solution.

Create the following macro named "Auto_Open" and save it to your Personal.xls workbook.

Sub Auto_Open()
'
' Auto_Open Macro
'
' Keyboard Shortcut: Ctrl+n
'
SendKeys "^o"
End Sub

Any macro that you save and call "Auto_Open" will automatically start when that worksheet is opened. So if you save it in your Personal.xls sheet it will automatically start when you open Excel.

by tink!

Well, I haven't been able to get what you posted to work. The Class1 module just doesn't seem to get the focus like it's supposed to. I tried all sorts of different arrangements.

However, if you use the Personal.xls workbook you can place the following in the Workbook object, Open property.

Private Sub Workbook_Open()
MsgBox ("START")
End Sub

I believe the result is the same as what you were going for.

by jumigurl

On the sheet you want to Auto Open, record a new macro, stop it. Go to macros, select and edit it in VBA. Use the code below to help you. it should work as it worked for me.

Sub Auto_Open()
'
' Auto_Open Macro
' Macro recorded 10/05/2007 by ...
'
Sheets("name of sheet to automatically open").Select
'
End Sub

This discussion is closed to new replies.