- Accessing advanced Excel controls (including buttons) requires the Developer tab, which is hidden in the settings.
- There are three main types of macro buttons: Form Control buttons (simplest), ActiveX buttons (more advanced), and Shapes (most customizable).
- Learning to create Excel macro functions requires patience and VBA programming knowledge, as complex macros can be incredibly handy yet difficult to replicate.
If you find yourself repeatedly performing the same sequence of actions in Excel, chances are at least a part of the process can be automated with macros. Even though VBA coding (which is the only one that works in Excel macros) might be a bit tricky to grasp at first, it can be worth it. With a macro loaded onto the button, clicking on it runs through the macro in a second. Here’s how to create a macro button in Excel.
Part 1 – Enable the Developer Tab in Excel
Before you can create macro buttons, you need Excel’s Developer tools, which are hidden by default. The Developer tab contains all the controls necessary for both macros and buttons.
Step 1. Click on the “File” tab in the top-left corner.
Step 2. Select “Options” from the menu (you might need to click “More” first if Excel is in windowed mode).
Step 3. In the “Excel Options” dialog, click on “Customize Ribbon” on the left-side panel.
Step 4. In the right panel under “Main Tabs,” check the box for “Developer” and click “OK.”
Step 5. Leave the settings.
The Developer tab should now appear in your Excel ribbon, giving you access to buttons, macros, and other advanced controls.
Part 2 – Creating a Button for the Macro
Option 1 – Create a Form Control Button (Recommended for Most Users)
Form Control buttons are the simplest and most reliable option for most users and they work across most versions without compatibility issues.
Step 1. Go to the “Developer” tab and click “Insert” in the Controls group.
Step 2. Under “Form Controls” (the top section), select the “Button” icon (first option in the top row).
Step 3. Click then drag with the mouse to draw the button where you want it to go. The “Assign Macro” dialog will automatically open once you release the left mouse button.
Step 4. In the “Assign Macro” dialog, choose one:
- Select an existing macro from the list
- Click “New” to create a new macro, which will open the Visual Basic Editor
- Click “Record” to record a new macro
Step 5. Once you’ve chosen or created a macro, click “OK” to assign the macro to your button.
Step 6. To customize the button’s text, right-click the button and select “Edit Text,” then type in your desired button label.
Step 7. Click anywhere outside the button to apply the changes.
Option 2 – Create an ActiveX Control Button (Advanced Users)
ActiveX buttons offer more customization options and advanced programming capabilities, but they only work on Windows systems and may have compatibility issues across different Excel versions.
Step 1. Go to the “Developer” tab and click “Insert” in the “Controls” group.
Step 2. Under “ActiveX Controls” (the bottom section), select the “Command Button” icon.
Step 3. Click and drag on your worksheet to create the button.
Step 4. When the button is created, you’ll see “Design Mode” is active (and highlighted in the Developer tab). Right-click on the button and select “Properties” to customize its appearance, text, and behavior.
Step 5. To assign VBA code to the button, right-click it and select “View Code.” This opens the Visual Basic Editor with an empty click event procedure.
Step 6. Add your custom code between the “Private Sub CommandButton1_Click()” and “End Sub” lines. The number in the function’s title will change if you have other buttons.
Step 7. Save and close the Visual Basic Editor, then click on “Design Mode” in the Developer tab to save all changes and get back to the sheet.
Option 3 – Create a Shape-Based Macro Button (Most Flexible)
Using shapes as macro buttons provides the most design flexibility, allowing you to create visually appealing buttons that match your worksheet’s aesthetic.
Step 1. Go to the “Insert” tab and click “Shapes” in the Illustrations group.
Step 2. Select any shape you prefer (rectangles and rounded rectangles work well for buttons).
Step 3. Draw the shape on your sheet where you want the button to appear.
Step 4. Right-click on the shape and select “Assign Macro” from the context menu.
Step 5. Add a macro or make a new one, then click “OK.”
Step 6. To add text to your shape, right-click it and select “Edit Text,” then input your button label.
Step 7. Use the formatting options in the “Shape Format” tab to customize colors, effects, and styles to make your button visually appealing.
Part 3 – Managing and Editing Macro Buttons
Once created, you may need to modify your macro buttons over time.
To change the assigned macro, right-click the button and select “Assign Macro,” then choose a different macro from the list or create a new one.
Editing button properties depends on the button itself:
- Form Controls: Right-click and select “Format Control”
- ActiveX Controls: Enable Design Mode, right-click, and select “Properties”
- Shapes: Right-click and use formatting options from the context menu
To prevent other people from accidentally deleting Form Controls macro buttons, right-click on it, select “Format Control,” go to the “Properties” tab, and check “Don’t move or size with cells.”
You can also copy and paste the entire button and its macro assignment to other sheets or places within the same sheet via “Ctrl + C” and “Ctrl + V” while the button is selected.
How to Create a Macros in Excel
Unfortunately, VBA programming is beyond the scope of a simple tutorial. Your best bet is to create simple macros via the “Record” Macro feature, then see what happens when you change parameters and lines. You can refer to Microsoft’s VBA Learn platform for more details.
Was this helpful?
Excel
- Corrupted Excel files, faulty add-ins, and outdated printer drivers are some of the most common causes of this issue.
- Try printing another Excel file to ensure the issue is not limited to a specific spreadsheet.
- You can try running the Microsoft Office repair tool if nothing else works.
Fix 1: Save Your Excel File in XPS Format and Try Again
If Excel can’t respond to print requests, save your file in the XPS format and try again. Several users on Microsoft Community post reported fixing the issue with this simple workaround. So, if you’re in a rush, try this method.
Step 1: Open the Excel file you wish to print and click the File menu at the top left corner.
Step 2: Navigate to the Print tab and use the drop-down menu under Printer to select Microsoft XPS Document Writer . Then, click the Print button.
Step 3: When the Save Print Output As dialog appears, save your Excel file in the XPS format. It should print without issues.
Fix 2: Open Excel in Safe Mode
You can try printing an Excel file in safe mode to see if one of the third-party add-ins is causing the problem. For that, press the Windows key + R to open the Run dialog. Type excel -safe in the box and press Enter .
Check to see if Excel prints your file in safe mode. If it does, one of the third-party add-ins is to blame. You can disable all add-ins and re-enable them individually to isolate the culprit.
Step 1: Open Microsoft Excel and go to File > Options .
Step 2: In the Excel Options window, switch to the Add-ins tab from the left column. Select COM Add-ins in the Manage drop-down menu and click the Go button.
Step 3: Uncheck all the Add-ins and click OK .
After this, restart Excel and enable your add-ins one at a time. Print a test page after enabling each add-in to identify the one causing the issue.
Fix 3: Update Printer Driver
Issues with your printer driver can affect Excel’s ability to print spreadsheets and lead to problems. To avoid this, you should ensure that your printer driver is up to date and functioning properly.
Step 1: Right-click on the Start icon and select Device Manager from the menu that appears.
Step 2: Double-click on Print queues to expand it. Right-click on your printer and select Update driver .
Follow the on-screen prompts to finish updating the printer drivers. After that, try printing your file again.
Fix 4: Remove and Reinstall Your Printer
If Microsoft Excel still can’t print, try removing your printer and re-adding it on Windows. Here’s how to do it.
Step 1: Press the Windows key + I to open the Settings app.
Step 2: Select Bluetooth & devices from the left sidebar and go to Printers & scanners .
Step 3: Select your printer from the list.
Step 4: Click the Remove button at the top to delete your printer.
Step 5: After that, return to the Printers & scanners menu and click on Add device . Then, follow the on-screen prompts to add your printer again.
Fix 5: Repair Microsoft Office
Microsoft Office offers a handy repair tool for any issues with Office apps. If nothing else works, consider repairing Microsoft Office by following the steps below.
Step 1: Right-click on the Start icon and select Installed apps from the list.
Step 2: Scroll down to locate the Microsoft Office product on the list. Click the three-dot menu icon next to it and select Modify .
Step 3: Select Online Repair and hit Repair .
