Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: VBA on open? (2000)

    Hi Steve,

    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.

    In ThisWorkbook:

    Private Sub Workbook_Open()
    End Sub

    In a standard module:

    Public Sub MyProcedure
    ' code goes here
    End Sub[/list]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts