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 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:
- In Excel, type Alt+T followed by M and S.
- Choose Disable All Macros with Notification. Click OK.
- Open your workbook.
- 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.
- 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.
- Click the + to expand the project.
- 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.
- Right-click ThisWorkbook and choose View Code from the context menu.
- Copy these three lines of code to the large white code window:
Private Sub Workbook_Open() ActiveSheet.ShowDataForm End Sub
- Press Alt+Q to return to Excel.
- Select File, Save As, Excel Macro-Enabled Workbook.
- Close the file.
- Open the file. The information bar tells you that macros have been disabled.
- 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.