programming4us
 
Office
 

Microsoft Excel 2010 : Automating Repetitive Tasks by Using Macros - Running Macros When a Button Is Clicked

4/12/2014 9:12:55 PM

The ribbon enables you to discover the commands built into Excel quickly. However, it can take a few seconds to display the View tab, open the Macro dialog box, select the macro you want to run, and click the Run button. When you’re in the middle of a presentation, taking even those few seconds can reduce your momentum and force you to regain your audience’s attention. Excel offers several ways for you to make your macros more accessible.

If you want to display the Macro dialog box quickly, you can add the View Macros button to the Quick Access Toolbar. To do so, click the Customize Quick Access Toolbar button at the right edge of the Quick Access Toolbar, and then click More Commands to display the Customize The Quick Access Toolbar page of the Excel Options dialog box.

Running Macros When a Button Is Clicked

When you display the Popular Commands command group, you’ll see that the last item in the command pane is View Macros. When you click the View Macros item, click the Add button, and then click OK, Excel adds the command to the Quick Access Toolbar and closes the Excel Options dialog box. Clicking the View Macros button on the Quick Access Toolbar displays the Macro dialog box, which saves a significant amount of time compared to displaying the View tab and moving the mouse to the far right edge of the ribbon.

If you prefer to run a macro without having to display the Macro dialog box, you can do so by adding a button representing the macro to the Quick Access Toolbar. Clicking that button runs the macro immediately, which is very handy when you create a macro for a task you perform frequently. To add a button representing a macro to the Quick Access Toolbar, click the Customize Quick Access Toolbar button at the right edge of the Quick Access Toolbar, and then click More Commands to display the Customize The Quick Access Toolbar page of the Excel Options dialog box. From there, in the Choose Commands From list, click Macros. Click the macro you want represented on the Quick Access Toolbar, click Add, and then click OK.

If you add more than one macro button to the Quick Access Toolbar or if you want to change the button that represents your macro on the Quick Access Toolbar, you can select a new button from more than 160 options. To assign a new button to your macro, click the macro item in the Customize Quick Access Toolbar pane and click the Modify button to display your choices. Click the symbol you want, type a new text value to appear when a user points to the button, and then click OK twice (the first time to close the Modify Button dialog box and the second to close the Excel Options dialog box).

See Also

Finally, you can have Excel run a macro when you click a shape in your workbook. Assigning macros to shapes enables you to create “buttons” that are graphically richer than those available on the Quick Access Toolbar. If you’re so inclined, you can even create custom button layouts that represent other objects, such as a remote control. To run a macro when you click a shape, right-click the shape, and then click Assign Macro on the shortcut menu that opens. In the Assign Macro dialog box, click the macro you want to run when you click the shape, and then click OK.

Important

When you assign a macro to run when you click a shape, don’t change the name of the macro that appears in the Assign Macro dialog box. The name that appears refers to the object and what the object should do when it is clicked; changing the macro name breaks that connection and prevents Excel from running the macro.

In this exercise, you’ll add the View Macros button to the Quick Access Toolbar, add a macro button to the Quick Access Toolbar, assign a macro to a workbook shape, and then run the macros.

Set Up

Open the PerformanceDashboard_start workbook, click the Enable Content button on the Message Bar (if necessary), and save the workbook as PerformanceDashboard. Then follow the steps.

  1. On the Quick Access Toolbar, click the Customize Quick Access Toolbar button, and then click More Commands.

    Set Up

    The Customize The Quick Access Toolbar page of the Excel Options dialog box opens, displaying the Popular Commands category in the Choose Commands From pane.

  2. In the list of available commands, click View Macros.

  3. Click Add.

    The View Macros command appears in the Customize Quick Access Toolbar pane.

    Set Up
  4. In the Choose commands from list, click Macros.

    The available macros appear in the pane below.

  5. In the Choose commands from pane, click SavingsHighlight.

    Troubleshooting

    If macros in the workbook are not enabled, the SavingsHighlight macro will not appear in the list.

  6. Click Add.

    The SavingsHighlight macro appears in the Customize Quick Access Toolbar pane.

  7. In the Customize Quick Access Toolbar pane, click the SavingsHighlight command.

  8. Click Modify.

    The Modify Button dialog box opens.

  9. Click the blue button with the white circle inside it (the fourth button from the left on the top row).

  10. Click OK twice to close the Modify Button dialog box and the Excel Options dialog box.

    The Excel Options dialog box closes, and the View Macros and SavingsHighlight buttons appear on the Quick Access Toolbar.

  11. On the worksheet, right-click the Show Efficiency shape, and then click Assign Macro.

    The Assign Macro dialog box opens.

    Troubleshooting
  12. Click EfficiencyHighlight, and then click OK.

    The Assign Macro dialog box closes.

  13. On the Quick Access Toolbar, click the SavingsHighlight button.

    Troubleshooting

    Excel runs the macro, which applies a conditional format to the values in the Savings column of the table on the left.

  14. Click the Show Efficiency shape.

    Excel runs the macro, which applies a conditional format to the values in the Efficiency column of the table on the right.

    Troubleshooting
  15. On the Quick Access Toolbar, click the Save button to save your work.

    Troubleshooting

Clean Up

Close the PerformanceDashboard workbook.

 
Others
 
- Microsoft Excel 2010 : Automating Repetitive Tasks by Using Macros - Creating and Modifying Macros
- Microsoft Excel 2010 : Enabling and Examining Macros (part 2) - Examining Macros
- Microsoft Excel 2010 : Enabling and Examining Macros (part 1) - Macro Security in Excel 2010
- Microsoft Project 2010 : Maintaining Baselines (part 3) - Resetting a Baseline , Multiple Baselines
- Microsoft Project 2010 : Maintaining Baselines (part 2) - Rolling-Wave Planning with Baselines
- Microsoft Project 2010 : Maintaining Baselines (part 1) - Clearing a Baseline
- Microsoft Project 2010 : Viewing Baselines (part 2) - Formatting the Gantt Chart to Display the Baseline
- Microsoft Project 2010 : Viewing Baselines (part 1) - Tracking Gantt View and the Variance Table
- Microsoft Project 2010 : Understanding and Setting Baselines
- Microsoft Project 2010 : Strategic Importance of Project 2010
 
 
REVIEW
 
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- Sigma 24mm f/1.4 DG HSM Art

- Canon EF11-24mm f/4L USM

- Creative Sound Blaster Roar 2

- Alienware 17 - Dell's Alienware laptops

- Smartwatch : Wellograph

- Xiaomi Redmi 2
 
VIDEO TUTORIAL
 
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
 
Popular tags
 
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
 
Top 10
 
- Setup Free Media Server To Stream Videos To DLNA Compatible TV, Xbox 360 & PS3 (Play Station 3)
- How To Install Android Market & Google Apps On Kindle Fire
- How To Make Ubuntu Look Like Windows 7
- How To Add A New Account in MS Outlook 2013
- Get Android & Mac OS X Style Gadgets For Windows 7 & Windows 8 With XWidget
- How To Activate Microsoft Office 2013
- How To Install Actual Facebook App On Kindle Fire
- How To Create, View And Edit Microsoft Office Files On Kindle Fire
- Download Attractive Business PowerPoint Templates For Free At SlideHunter
- How To Use And Enable Hibernate & Sleep Mode In Windows 8