Pin Me

Microsoft Excel: Use A Macro To Further Customize Startup

written by: Mr Excel•edited by: Tricia Goss•updated: 7/10/2008

Problem: Every time that you open a workbook, you would like to put the file in Data Form mode, as shown in Fig. 72, or to invoke any other Excel menu as the file opens.

  • slide 1 of 2

    See all Microsoft Excel tips

    Strategy: Startup switches can only do so many things. You will have to use a Workbook_Open macro in order to force Excel into Data Form mode. Follow these steps:

    1) In Excel, go to Tools – Macro – Security. Set the security level to Medium or lower.

    2) Open your workbook.

    3) Hit Alt+F11 to open the VBA Editor. (Caution: The Microsoft Natural Multimedia keyboard does not support Alt+F keys. You might have to use Tools – Macros – Visual Basic Editor, instead.)

    4) Hit Ctrl+R to show the Project Explorer in the upper left corner. You should see something that looks like VBAProject (Your Book-Name) in the Project Explorer, as shown in Fig. 73.

    5) If there is a + to the left of this entry, hit the + to expand it. You will see a folder underneath, called Microsoft Excel Objects. If there is a + to the left of this entry, hit the + to expand it, also.

    6) You will now see one entry for each worksheet, plus an entry called ThisWorkbook. Right-click on ThisWorkbook and choose View Code from the pop-up menu, as shown in Fig. 74.

    7) Copy these three lines of code to the large white code window.

    Private Sub Workbook_Open()


    End Sub

    8) Hit Alt+Q to return to Excel.

    9) Save the file.

    10) Open the file. The data form should open.

    Additional Information: This simple macro invokes on Menu command. It is possible to build highly complex macros that would control literally anything. For a primer on macros, consult VBA & Macros for Microsoft Excel from QUE.

    Summary: Customize your shortcut to Excel to open a specific file or to use a specific path as the current folder.

    See all Microsoft Excel tips

  • slide 2 of 2


    Fig. 72Fig. 73Fig. 74