How do I run code before the screen is displayed? RRS feed

  • Question

  • I'm sure this is easy when you know how to do it.

    When I click on the .xls file name, the Excel worksheet displays. In this case the worksheet is more of a display with some text boxes to be filled it, labels that I use to display the results, and a few buttons.

    I want to run some code before the worksheet is displayed, such as setfocus on a text box.  I would think that the place to put such code would be "Worksheet Activate", but it doesn't work.  I've tried to simply put a starting value on a field in WorkSheet Activate, but it does not initialize the field to the value in the code before displaying.

    How do you do this?

    Monday, March 12, 2012 7:49 PM

All replies

  • Try this in ThisWorkbook:

    Private Sub Workbook_Open()
       Application.ScreenUpdating = False
       <whatever code>
       Application.ScreenUpdating = True
    End Sub

    Monday, March 12, 2012 8:23 PM
  • Thanks for the reply, but it didn't work :-(
    Tuesday, March 13, 2012 3:05 AM
  • Hi JLeites,

    I agree with Jeffmb's idea. We need to put the method in ThisWorkbook project's code. Please try the following steps:

    1. Save and close all open workbooks.
    2. Open a new workbook, and then start the Visual Basic Editor (press ALT+F11).
    3. Press CTRL+R to switch to the Project Explorer window.
    4. In the Project Explorer window, right-click the ThisWorkbook object, and click View Code on the shortcut menu.
    5. In the module, type the following code:
          Private Sub Workbook_Open()
              Msgbox Date
              Worksheets("Sheet1").Range("A1").Value = Date
          End Sub

    6. Switch to Microsoft Excel and save the workbook.
    7. Close and reopen the workbook.

    When you open the file again, Excel runs the Workbook_Open macro, which displays today's date in a message box.
    Click OK in the message box.

    Reference from: 

    Also, you may consider about write logic for Auto_Open macro, and it will start automatically. This link provides a nice example on this approach:

    You can also check out the following link: 

    Hope it helps.

    Have a nice day.

    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, March 13, 2012 9:09 AM
  • Thanks for the help.  I don't want you guys to think that I'm ignoring you, but I came down with a cold yesterday afternoon, and I'm not feeling up to working on this right now.  I'll get back to this soon, and let you know how it's going.
    Tuesday, March 13, 2012 7:13 PM