{"id":8854,"date":"2020-12-20T14:58:05","date_gmt":"2020-12-20T13:58:05","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=8854"},"modified":"2024-04-10T01:16:36","modified_gmt":"2024-04-09T23:16:36","slug":"how-to-create-your-first-macro-button-in-excel-beginners-tutorial","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/how-to-create-your-first-macro-button-in-excel-beginners-tutorial\/","title":{"rendered":"3 Easy Methods to Create Button in Excel for Macros"},"content":{"rendered":"

Do you repeat various Excel<\/a> actions like copy & pasting data, formatting, putting borders, etc?<\/p>\n

Do you know that you create button in Excel and automate your tasks<\/strong> in Excel with a couple of mouse clicks?<\/p>\n

If you keep on repeating the same thing over and over again,\u00a0creating an\u00a0Excel Macro\u00a0<\/strong>is perfect for you!\u00a0 It saves you time so you can do things that you like doing, like going home early \ud83d\ude42<\/p>\n

Click a button and all your tasks are done for you!<\/p>\n

I will show you 3 different ways using which you can enable the Macro Creation<\/strong> and create button in Excel!<\/h4>\n\n
\n

Follow a step-by-step tutorial on how to create a button in Excel and download this Excel workbook to follow along:<\/p>\n<\/div>\n

\n

download excel workbook<\/strong><\/span>How-to-Create-Your-First-Macro-in-Excel-1.xlsm<\/span><\/a><\/p>\n<\/div>\n

<\/a>Using ActiveX Control Buttons<\/span><\/strong><\/h3>\n

Running a macro can sometimes be a tedious task. The fastest way to run a macro will be to assign a macro to a button<\/strong>. You can use ActiveX controls<\/strong> to create button in Excel.<\/p>\n

STEP 1:<\/strong><\/span> Right-click anywhere on your Ribbon and select\u00a0Customize the Ribbon<\/strong><\/em>:<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 2:<\/strong><\/span>\u00a0Make sure the\u00a0Customize Ribbon\u00a0<\/strong>is selected.<\/p>\n

Then select the\u00a0Developer\u00a0<\/strong>option under\u00a0Main Tabs<\/strong>.<\/p>\n

Click\u00a0OK.<\/strong><\/p>\n

\"3<\/a><\/h2>\n

 <\/p>\n

STEP 3:<\/strong><\/span>\u00a0Now go to\u00a0Developer > Insert > ActiveX Controls > Button<\/strong><\/em><\/p>\n

\"3<\/a><\/p>\n

Drag anywhere in the Excel spreadsheet to create button in Excel.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 4:<\/strong><\/span> Right-click on your new button and select\u00a0View Code<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

STEP 5:<\/strong><\/span>\u00a0Type in this code:\u00a0\u00a0Range(“A6”).Value = “Hello”<\/em><\/p>\n

What this will do is to\u00a0set the value of cell A6<\/strong> to\u00a0Hello<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 6:<\/strong><\/span>\u00a0Time to test it out! Make sure\u00a0Design Mode is deselected.<\/strong><\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

STEP 7:<\/strong><\/span>\u00a0Click on the button now and you will see\u00a0Hello\u00a0<\/strong>pop up on your screen!<\/p>\n

\"3<\/a><\/p>\n

 <\/p>\n

In this way, you can create button in Excel and assign a macro to it. Using VBA codes, you can also assign a different task for a different trigger<\/strong> like double-click single-click, right-click, etc.<\/p>\n

You can even change the formatting<\/strong> of this button by right-clicking on the button and selecting the option Format Control<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

But the only issue<\/strong> with this method is that the size of the button may change<\/strong> when you make changes to the worksheet or share it with someone else.<\/p>\n

 <\/p>\n

<\/a>Using Shapes<\/strong><\/span><\/h3>\n

Shapes provide you the flexibility to format and customize the appearance of the buttons<\/strong> on your Excel spreadsheet.<\/p>\n

You can use different shapes, icons, WordArt images, and objects<\/strong> as per the theme of your worksheet. The formatting will also remain consistent<\/strong> using this method.<\/p>\n

In my opinion, this is the simplest and most adaptable option<\/strong> to create button in Excel and run a macro.<\/p>\n

 <\/p>\n

Let’s look at a detailed tutorial on how we can go about this:<\/p>\n

STEP 1: <\/strong><\/span>Go to Insert<\/strong> Tab > Select Illustrations<\/strong> > Under shapes dropdown > Select rectangle<\/strong> shape.<\/p>\n

\"3<\/a><\/p>\n

STEP 2:<\/strong><\/span>\u00a0Right-click<\/strong> the shape and select Edit Text<\/strong> to add text to the button.<\/p>\n

\"3<\/a><\/p>\n

Or, you can double click<\/a><\/strong> on the button to edit text.<\/p>\n

STEP 3:<\/strong><\/span> Provide a name<\/strong> to this button – Highlight Max Value.<\/p>\n

\"3<\/a><\/p>\n

STEP 4:<\/strong><\/span> Go to Developer<\/strong> >Visual Basic<\/strong>.<\/p>\n

\"3<\/a><\/p>\n

STEP 5:<\/strong><\/span> Type the following code in the module.<\/p>\n

Copy Source Code:<\/strong><\/em><\/p>\n

\r\nSub HighlightMaxValue()\r\n\r\nDim cell As Range\r\nFor Each cell In Selection\r\n\r\n'If it matches the highest value, then change the style to be Note\r\nIf cell = WorksheetFunction.Max(Selection) Then\r\ncell.Style = "Note"\r\nEnd If\r\nNext cell\r\n\r\nEnd Sub\r\n<\/pre>\n
\"3<\/a><\/p>\n
HighlightMaxValue will now be saved!<\/p>\n
STEP 6:<\/strong><\/span> You can easily add a macro to this button by right-clicking<\/strong> on the button and select Assign Macro<\/strong>.<\/p>\n
\"3<\/a><\/p>\n
STEP 7:<\/strong><\/span> In the Assign Macro dialog box, you can select one macro (here, HighlightMaxValue)<\/strong> from the list saved in the Excel Workbook. Click OK<\/strong>.<\/p>\n
\"3<\/a><\/p>\n
This HighlightMaxValue will highlight the cell with maximum value with yellow color.<\/em><\/p>\n
STEP 8:<\/strong><\/span> Select the range<\/strong> and then press the Excel Macro button<\/strong>.<\/p>\n
\"3<\/a><\/p>\n
STEP 9:<\/strong><\/span> Select the maximum value from the range i.e. 947 will be highlighted<\/strong>.<\/p>\n
\"3<\/a><\/p>\n
This is how you can easily use shapes to create button in Excel and assign a macro to it.<\/p>\n
 <\/p>\n
You can also customize these buttons <\/strong>by following the steps below:<\/p>\n