Create Menus with Macros-2
Continued from last week's Article: Create Menus with Macros, please go through it before continuing.
But, before all that let us make little modification to our earlier shortcut menu to add a menu group item added with few options.
- Open a new macro in design view.
- Select MsgBox from the drop down list in the Action Column.
- Type Proceed with Report Data Processing? in the Message control under the Action Arguments list.
- Select Critical from the drop down list in the Type control.
- Type Report Processing in the Title control.
Note: This macro is supposed to contain a series of actions; like running of action queries (Make-Table, Append, Update, Delete or running of other macros or VBA Routines) to prepare data ready for Reports. Here, we are simply adding a message box to display a message instead. You must create Menus with actions as per requirements of your Application.
- Save the Macro with the name: macProcess and close it.
- Click on New. . . to open another new macro in Design View.
- If the Macro Name Column is not visible then select Macro Name from View Menu.
- Type &Form in the Macro Name Column on the first row and select OpenForm from the drop down list under Action Column.
- Select an existing Form in the From Name control using the drop down control.
- Type Process &Data in the Macro Name column in the second row and select RunMacro in the Action Column.
- Type macProcess (or select it from the drop down list) to insert the process macro that we have created in step-6.
- Type &Report in the Macro Name column in third row and select OpenReport in the Action column.
- Select an existing Report from your application in the Report Name control.
- Select Print Preview in the View control.
- Save the macro with the name macSubMenu and close it.
- Open the macro mcrShortCut that we have created last week.
- Type MenuGroup in the next row (as shown in the image given below) and select AddMenu option from the drop down list under Action column.
- Type Form/Report in the Menu Name control below the Action Arguments.
- Type macSubMenu (or select from the drop down list) in the Menu Macro Name control.
- Save and close the macro mcrShortCut.
- If you remember on which Form you have attached the earlier macro Shortcut Menu last week, then you can open that Form in normal view and right-click on the Form to display the Form/Report Sub-Menu we have added to the earlier menu. If You don't remember then open one of your existing Form in Design View.
- Display the Property Sheet (View – -> Properties or press ALT+Enter).
- Click on the Other Tab.
- Set the Shortcut Menu Property value to Yes.
- Set the Shortcut Menu Bar Property value to macMenu (you can select the macro from the drop down list also).
- Save and close the Form.
- Open the Form in normal View.
- Right-Click on a field to display the Shortcut Menu. The new Group Menu that we have added will appear at the end of the Menu with description Form/Report.
- Place the mouse over Form/Report menu group to display its Sub-Menu items.
The menu definition now remains in Macros and we are directly using it on Forms. But, we can convert the Menu Macro into a regular:
- Menu Bar (like File, Edit etc.) OR
- Tool Bar OR
- Shortcut Menu.
If you would like to convert this macro menu into a Menu Bar then we must make a small change in the macMenu macro.
- Open macMenu in design view.
- Type myMenu in the Menu Name control under Action Arguments.
- Save and close the macro.
- Click on macMenu macro to select it.
- Highlight the option Macro in Tools Menu.
- Click on Create Menu from Macro Option.
A Menu Bar will appear on top with the label myMenu.
- Click on myMenu to display the menu options which we have created in macros.
Note: You should not attempt to covert the same macMenu to a Toolbar Menu; a name conflict will take place.
Tip: To create a quick Toolbar Button with the same set of macros, do the following:
- Make a copy of the macros: macMenu, mcrShortCut and macSubMenu and name them as mac_Menu, mcr_ShortCut and mac_SubMenu.
- Open mcr_ShortCut macro in design view.
- Click on the last line and change the Menu Macro Name control value to mac_SubMenu, save and close the Macro.
- Open the mac_Menu macro in design view.
- Change the Menu Name control value to myToolbar.
- Change the Menu Macro Name control value to mcr_ShortCut, save and close mac_Menu Macro.
- Click on the mac_Menu Macro to select it.
- Highlight Macro in Tools Menu to display its Options.
- Click on Create Toolbar from Macro option to convert the macro into a Toolbar; myToolbar button will appear near to the existing Toolbar with the label myToolbar.
- You may click on it to display the Menu.
Microsoft Excel Power in MS-Access
Change Secure DB to Unsecured
Date and Time Values
Limit to List Combo Box
Input Masks and Data Entry