12.3 Running Macros: Button, Shortcut and the Macros Dialog
Steps in Excel
- Macros dialog: Developer › Code › Macros (Alt + F8) › select › Run. Options… sets or changes the shortcut key.
- Shortcut key: use Ctrl + Shift + letter – plain Ctrl + letter can override Excel shortcuts like Ctrl + C.
- Button (Form Control): Developer › Controls › Insert › Form Controls › Button › draw it › the Assign Macro dialog opens › choose
FormatDailyReport› OK › right-click › Edit Text "Format Report". - Any shape as a button: Insert › Illustrations › Shapes › draw a rounded rectangle › type text › right-click › Assign Macro…
- Quick Access Toolbar: File › Options › Quick Access Toolbar › Choose commands from: Macros › Add › Modify… to pick an icon.
Worked example. On the Report sheet Zoya adds three shape-buttons: Format Report, Split by City, Combine Sheets, each assigned to a macro from 12.12 – the Nashik store manager can run them without opening the VBA Editor.
Ravindra Bagale's Tip
If you give a macro the shortcut Ctrl + C or Ctrl + V, Excel's copy-paste stops working – and many students can't figure out why! Always use Ctrl + Shift + a letter. Write a clear name on the button ("Format Report"), not "Button 1" – the user should know what will happen.
Ravindra Bagale's Tip – मराठी
Macro ला Ctrl + C किंवा Ctrl + V shortcut दिला तर Excel चं copy-paste बंद होतं – आणि बऱ्याच students ना कळत नाही का! नेहमी Ctrl + Shift + letter वापरा. Button वर स्पष्ट नाव लिहा ("Format Report"), "Button 1" नको – वापरणाऱ्याला काय होईल ते कळलं पाहिजे.
Ravindra Bagale's Tip – हिंदी
Macro को Ctrl + C या Ctrl + V shortcut दिया तो Excel का copy-paste बंद हो जाता है – और बहुत से students समझ नहीं पाते क्यों! हमेशा Ctrl + Shift + letter इस्तेमाल करो. Button पर साफ़ नाम लिखो ("Format Report"), "Button 1" नहीं – इस्तेमाल करने वाले को पता होना चाहिए कि क्या होगा.
Practice task
Assign your FormatHeader macro to a shape button and to the Quick Access Toolbar. Change its shortcut to Ctrl + Shift + F from the Macros dialog.