Excel is one of the most widely used office applications worldwide. Being able to create professional spreadsheets can help an individual’s career to grow, get the job done efficiently, and help to stand out in a competitive job market. One of the most useful features in Excel is creating buttons that enable users to print, filter data, create options, and more. This article will outline how to create buttons using macros in Excel.
Creating a Print Button
One of the most common functionalities that Excel buttons perform is to print a sheet or a selected range of cells. This can come in handy when you have to print long spreadsheets or for presentations. With the help of macros, creating a print button becomes easy. Here are the steps:
- Open Excel and select the sheet that you want to print.
- Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
- Enter the following code:
Private Sub PrintBtn_Click()Application.Dialogs(xlDialogPrint).Show
End Sub
This code will bring up the Print dialog box when the button is clicked.
- Close the VBA editor and return to the worksheet.
- From the Developer tab, click on the Insert button, which is in the Controls group. Select the Button control and add it to the worksheet.
- Right-click the button and select the Edit Text option. Change the text to ‘Print.’
- Right-click the button again and select the Assign Macro option. In the dialog box that appears, select the PrintBtn_Click option and click OK.
- The button is now ready to be used. Click the button to launch the Print dialog box.
Creating an Option Button
Option buttons are used to select a single option from a group of options. For example, you can use them to let users select one item from a set of predefined options. Here are the steps to create an option button:
- Open Excel and select the sheet where you want to add the option button.
- Click on the Developer tab and select the Insert button, which is in the Controls group. Select the Option Button control and add it to the worksheet.
- Right-click the option button and select the Format Control option. In the dialog box that appears, select the Control tab. Here, you can set the cell link that the option button will be linked to.
- Select the cell you want to use for the option button by typing the cell address in the Cell link box. For example, if you want to link the option button to cell A1, type ‘A1’ in the box.
- Click OK to close the dialog box.
- Copy and paste the option button to create additional buttons if required. Each button must be linked to a different cell.
- Right-click each button and select Edit Text. Change the text for each button.
- The option buttons are now ready for use. Click each button to select the appropriate option.
FAQ
A: No, macro-enabled buttons can only be used on computers that have macros enabled. Some companies disable macros to increase security, so it’s best to check with the IT department before sharing spreadsheets that contain macros.
Q: Can macros harm my computer?
A: Macros can be used to create malicious code that can harm your computer. However, as long as you only enable macros from trusted sources and disable them when you are not using them, they should be safe to use.
Video Tutorial: Adding Buttons in Excel
Here’s a helpful video tutorial that shows how to add buttons using macros in Excel:
Conclusion
Excel buttons can help to save time, simplify tasks, and make spreadsheets more professional. The two most common buttons are the print button and option button. The print button is used to print a sheet or a range of cells, while the option button is used to select a single option from a group of options. By following the steps provided in this article, anyone can easily create macros enabled buttons in Excel.
