MrExcel.com - Your One Stop for Excel Tips & Solutions

Use a Macro to Customize Startup

August 20, 2021 - by

Use a Macro to Customize Startup

Problem: Every time I open a workbook, I would like to put the file in Data Form mode or invoke another Excel menu as the file opens.

The Data Form is a built-in tool for doing data entry. Say you have 7 input cells and a formula in each row. Use the Data Form and it will give you the headings from each column next to a textbox. Buttons lets you go Next, Previous, Criteria, etc.
Figure 56. The data form is increasingly difficult to find in Excel.

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, type Alt+T followed by M and S.

  • 2. Choose Disable All Macros with Notification. Click OK.

  • 3. Open your workbook.

  • 4. Press Alt+F11 to open the VBA Editor. Gotcha: The Microsoft Natural Multimedia keyboard does not support the use of Alt+function keys. You might have to type Alt+T followed by M and D.

  • 5. Press Ctrl+R to show the Project Explorer in the upper-left corner. You should see something that looks like VBAProject (Your BookName) in the Project Explorer.

In the Project Explorer on this computer, there are three files open: Solver.XLAM, Dataform2.xlsm, and LE10TOC.xlsm.
Figure 57. Click the + to expand the project.

  • 6. If there is a + to the left of this entry, press the + to expand it. You will see a folder underneath, called Microsoft Excel Objects. If there is a + to the left of this entry, press the + to expand it, also. You will now see one entry for each worksheet, plus an entry called ThisWorkbook.

  • 7. Right-click ThisWorkbook and choose View Code from the context menu.

  • 8. Copy these three lines of code to the large white code window:

Private Sub Workbook_Open()
	ActiveSheet.ShowDataForm
End Sub

  • 9. Press Alt+Q to return to Excel.

  • 10. Select File, Save As, Excel Macro-Enabled Workbook.

  • 11. Close the file.

  • 12. Open the file. The information bar tells you that macros have been disabled.

  • 13. Select Options, Enable This Content. The data form will open.

Alternate Strategy: To prevent Excel from automatically disabling macros, you can save the file in a trusted location. See “Use a Trusted Location to Prevent Excel’s Constant Warnings” earlier in this section.

Gotcha: The data form used to be an option on the Excel 2003 Data menu. It is hidden in Excel today. To invoke this command, you can either press Alt+D+O or add the command to your Quick Access toolbar.

Additional Details: The simple Workbook_Open macro invokes a Menu command. It is possible to build highly complex macros that would control literally anything. For a primer on macros, consult VBA and Macros for Microsoft Excel 2013 from Que Publishing.

This article is an excerpt from Power Excel With MrExcel

Title photo by Adam Lukomski on Unsplash