When we mention buttons in Excel, anyone who is not a consistent user will wonder what that means. Yes, Microsoft Excel does have Macros buttons which are the most advanced level of Excel. These buttons are commands initiated by a single click. It is easy to add buttons to excel. A user can simplify and save the time that they will take to navigate between different cells looking for specific information. In short, the buttons are inserted to perform specific tasks for us. The three different types of buttons you can place in a worksheet include;
- Shapes
- Form Control Buttons
This article shows how to add a button in Excel and how to assign Macros to them. With those buttons, navigating through your spreadsheet won't be a nightmare anymore.
Method 1: Using shapes to create Macro buttons to open a particular sheet
You can create a macros button by using shapes. You can easily create a rounded rectangle; add a hyperlink to it for your worksheet. Here is what you can do;
1. On the main menu ribbon, click on the Insert tab.
2. Go to Shapes, click the drop-down arrow, and select the Rounded Rectangle icon.
3. Draw a rounded rectangle on your worksheet.
5. Format the shape by typing text into it-Right-click on the form and select edit text. Or double-click the shape.
6. To Hyperlink the shape, right-click on it and select Hyperlink from the menu. Right-clicking will display an Insert Hyperlink dialogue box.
- Under the 'Link to' section, select 'Place in This Document.
- Under the 'Type the cell reference' section, type in the destination cell address.
- Under the 'Or select a place in this document box, click to choose the particular sheet name. Click the OK button when done.
When you click the rounded rectangle, it will skip to the specified cell of a specified sheet.
7. To assign the macro, right-click on the table and select Assign Macro. Under the 'Macros in' drop-down arrow, select 'This Workbook'. Here, select the macro from the list of macros in This Workbook.
8. Press OK. When you point your mouse on this shape, it will turn to the hand pointer cursor, and clicking the form will run the macro. Remember to set your shape not to resize with cell changes by right-clicking on it and selecting 'Size and Properties.'
Method 2: Using Developers Form Control Buttons to create buttons in Excel
1. On the main ribbon, click on the Developer tab.
2. Go to the Insert button and click the drop-down arrow.
3. Under Form Control, select the first option called button. Draw a button on your worksheet
4. Next, in the Assign Macro dialogue box, type or select a name for the macro.
5. Click OK when done. You can click on this button to run the macro.
Using ActiveX Controls
Since running a macro in Excel can prove tedious, you can assign a macro to a button to run it faster. In this case, you can follow these steps to add a button in Excel using ActiveX controls easily.
1. Right-click anywhere on the Home ribbon and select Customize the Ribbon option from the pop-up menu.
2. Once the Customize the Ribbon window is open, go to the Main Tabs section and select the Developer option.
3. Click OK. However, if you already have the Developer Tab added to your ribbon, then proceed as follows.
4. Go to the Developer Tab and click on Insert.
5. Next, click on your preferred button under the ActiveX Controls.
You can now drag it anywhere in the Excel worksheet to create a button.
6. Right-click on the newly created button and select the View Code option from the drop-down menu.
7. You can now type this code, which sets the value of cell A6 to Hello:
Range(“A6”).Value = “Hello”
8. If you want to test setting the cell value, ensure the Design Mode option is deselected. You can also click on the button, and the Hello text will be displayed on your screen.
You can further use VBA codes to assign a different task for various operations such as double-clicking, single-clicking, right-clicking, and many more. When you right-click on the button, you can also select the Format Control option. However, the only downside is that the size of the buttons changes every time you make changes on the worksheet or share it.
Adding Macros To Quick Access Toolbar
Adding macros to Quick Access Toolbar also allows you to create buttons in Excel and use them on any sheet in your present workbook. To do this, follow these steps:
1. Right-click on the arrow below the ribbon of the Excel workbook.
2. When the Customize Quick Access Toolbar screen opens, navigate and select More Commands at the bottom.
3. Select Macros in the Choose commands from the section. You can click on the down-facing arrow in the Popular Commands box.
4. Select the HighlightMaxValue option and click on the Add button.
5. You can now click on Modify to customize the symbol of the macro.
6. Select your preferred symbol from the provided list and hit the OK button.
7. Finish by clicking OK to add a button to your Excel workbook. If you want to run the HighlightMaxValue macro, simply click on the icon.
Conclusion
When working with adding buttons to Excel, it is best to keep it easy and straightforward. The above methods portray these as the steps are short and easy to follow. They are not only easy to set up, but they also give you different options for formatting.