Excel 2010: Using Macro Through Buttons

Excel Macro feature lets you to record actions you perform on worksheet, and run the actions again for future use. But it would be extremely useful if you can run macro with a simple click on button, rather than running it manually.  By creating macro-buttons you will be able to associate macros with buttons, and show them on the worksheet for performing different tasks you have recorded with macro. Excel enables you to create custom buttons to link macros with them, this post will elaborates how to create macros and associate  buttons them with.

Launch Excel 2010, Create or open a datasheet in which you want to create button for Macros.

For instance, we will be using a very simple example to illustrate it in detail. We have included a datasheet, containing fields; Name, Courses, Marks, Grade Prize Money.

datasheet students

To start off with, we need to make Developer tab apparent on Excel window. In case you don’t find Developer tab, go to File menu, click Options, and in left pane click Customize Ribbon, from right pane enable Developer check-box. Click OK to to see Developer tab on the ribbon.

developer tab

Navigate to Developer tab and click Record Macro.

record

Now give it an appropriate name, and click OK to start recording macro.

record macro

Now navigate to Home tab, and from Conditional Formatting, go to Highlight Cells Rules menu, and click Less Than.

less tahn

Enter value suitable for your datasheet, as you can see in the screenshot below we have entered 60 (to mark fail students with less than this number). Click OK.

60

As you can see Conditional Formatting is applied on Marks table.

conditional form

Now follow the same procedure to apply Conditional Formatting on Grade, and Prize Money field.

cond 1

Now we will stop recording Macro, navigate to developer tab and hit Stop Recording.


stop

We will be recording a new macro, for formatting columns as Bold, and center align. Follow the procedure for creating a new macro.

You can see the macro by the name of Bold_Align, it will bold the font and align the text in center.

bold align

Now we need to create buttons for each macro, for this navigate to Developer tab, from Insert options, click button image present under Form Controls, as shown in the screenshot below.

developer button 1

Upon click, you will see pointer change to plus sign, click any where on datasheet to place button. When you will click, a dialog will appear asking you to associate macro with button. Select Cond_form from the list. And click OK.

cond form button

You will see button appear on the datasheet.

button

Now give it an appropriate name by clicking on the button (while it is selected) as shown in the screenshot below.

conditional formatting button

We will be creating another button, to associate Bold_Align macro, and name it Format.

button comnplete 1

Upon clicking buttons, you will see the changes, as macro run at the back-end.

Complete]

For future use, you need to save the worksheet as macro enabled, for this, go to File menu, and click Save As.

save as

A dialog will appear, from Save as type, click Excel Macro-Enabled Workbook and click Save.

macro enabled

You can also check out previously reviewed guides on SmartArt in Excel 2010 & How to embed Video in Excel 2010.

Advertisement
  • Pingback: Insert Checkboxes In Excel 2010

  • Mubarish

    Your details helps me to know how to create macro button in Excel 2010.. Thanks..

  • Guest

    Great job – saved me a ton of time at work!

  • Santosh

    Thank you its very helpful

  • Nick

    Superb post. Helped me a lot..Thanks

  • Lou

    can a macro be ceated … and in the middle of the macro – wait to input data? For example, I was to write a macro that will copy & paste worksheet into a new tab then sort. I’d like the tab to be named a month and year (Jun – 12). Is this possible?

  • Paul

    good stuff!

  • bob

    thank u for a great help

  • jquinn

    Is it possible to assign a custom macro to an existing button on the toolbar? For example, if you wanted a different currency format to be assigned to the $ button.

  • Lisa

    what is the process to apply a print button to a worksheet to print a whole worksheet template

  • daniel

    Thank you, following you notes was very easy

  • manoj

    Your details helps me to know how to create macro button in Excel 2010