Results 1 to 2 of 2
Thread: VBA on open? (2000)
2003-04-24, 14:51 #1
- Join Date
- Sep 2002
- Thanked 0 Times in 0 Posts
VBA on open? (2000)
How can i execute some VBA on opening a worksheet ?
ie: how to get to the 'on open' call
2003-04-24, 15:06 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: VBA on open? (2000)
Make sure that the Project Explorer is visible in the Visual Basic Editor (shortcut: Ctrl+R)
If you "expand" your workbook, you will see ThisWorkbook and an entry for each sheet (for example Sheet1) under Microsoft Excel Objects.
ThisWorkbook is the object that represents the workbook as a whole; the others represent the individual sheets.
By double clicking one of these objects, you open the class module for that object. You can write event handlers for the events that belong to the object.
If you want to execute some code when the workbook is opened, write code for the Workbook_Open event in ThisWorkbook. You can select the Workbook object from the object dropdown list in the upper left corner of the code window, and the event from the dropdown list in the upper right corner.
Similarly, if you want to execute code when a specific sheet is activated, write code for the Worksheet_Activate event in the code window of that worksheet.
Warning: Writing event handlers for worksheets and for ThisWorkbook can be tricky. Some tips:
<UL><LI>Save your work often.
<LI>Always use error handlers (On Error Goto SomeLabel).
<LI>Code in a standard module is easier to debug than code in ThisWorkbook and the sheet modules. I often create a procedure in a standard module and call that in the event handler.
Private Sub Workbook_Open()
In a standard module:
Public Sub MyProcedure
' code goes here