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.
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, go to Tools – Macro – Security. Set the security level to Medium or lower.
-
Open your workbook.
Advertisement -
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.)
-
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.
Advertisement -
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.
-
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.
Advertisement -
Copy these three lines of code to the large white code window.
Private Sub Workbook_Open()
ActiveSheet.ShowDataForm
End Sub
-
Hit Alt+Q to return to Excel.
-
Save the file.
Advertisement -
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.
Images
