How do I add a macro button to the ribbon in Excel?

Hi

I have created a macro that needs to be used by multiple people, most of which have quite a limited knowledge of excel so i'm trying to come up with a really simple way of sharing it.
It occurred to me i can turn the macro into an Add-In, then create another macro that installs the add-in and assign it to a nice big button, all they have to do it click on it and it's done. Super easy so far...

The next part is then putting that macro on ribbon somewhere so it is easy to use.
I was hoping i could simply record another macro to do this, but for some reason it doesn't record anything.

I have a hunch it just can't be done, but really hoping someone can tell me it can, or can point me in another direction to automate this step?


Thanks

you may have to use a CustomUI to add your macro to the ribbon. Learn from here

For those that might be interested, i managed to find a work around.

VBA Code:

Sub Auto_Open()

Set cbToolbar = Application.CommandBars.Add(csToolBarName, msoBarTop, False, True)

With cbToolbar
    Set ctButton1 = .Controls.Add(Type:=msoControlButton, ID:=2950)
End With

With ctButton1
    .Style = msoButtonIconAndCaption 'button style
    .Caption = "Organise &Sheet" 'Button name
    .FaceId = 2950 'Button icon
    .OnAction = "OrganseSheet" 'Macro name
End With


With cbToolbar
    .Visible = True
'    .Protection = msoBarNoChangeVisible This produces an error, i don't know why
End With

End Sub

And, we’ll use this code to assign to macro buttons which we placed in a module, it will return the used row numbers in our active sheet.

1. Using Control Button to Create a Macro Button

Firstly, we’ll apply the Control buttons from the Developer ribbon to build a macro button. You will get two types of buttons here, the Form Controls button and the ActiveX Controls button. Both perform the same kind of operations but ActiveX Controls provides more customizations, formats, and features.

1.1. Using Form Control Button

From the Form Controls section, we’ll apply the first control button named- Button.

Steps:

• So, click as follows: Developer > Insert > Form Controls > Button.

Then your cursor will look like this- a plus sign.

• Left-click your mouse and hold, then drag over the sheet where you want to place the button.

• After releasing the left click, the Assign Macro dialog box will appear automatically to set the macro.
• Select the macro name and press OK.

Macro is assigned successfully, you can change the button name too. By dragging the eight circular icons beside the box, we can edit the size of the macro button.

• Double-click on the button and type the name. We named it- Used Rows.

• If you right-click on the button you will get many options to edit the macro button like Cut, Copy, Paste,

• So now, if we just click on the button it will run the macro and show the output.

See, a notification box is showing the output.

1.2. Using ActiveX Control Button

Now we’ll use the Command Button from the ActiveX Controls buttons. But instead of assigning macro, we’ll have to type the code here.

Steps:

• Click as follows: Developer > Insert > ActiveX Controls > Button.

• Like the previous section, set the button size using the mouse.

• Next, right-click on the button and select View Code from the context menu.

By using the other menu you can edit the macro button as you required.

• After that, a VBA window will open up like the image below that will has the Sub and End procedures by default. We’ll have to place codes within it.

• Copy the codes from the module and place them here.
• Later, just press the Run icon to activate the codes for the button.

Now just by clicking the button, we’ll be able to run the codes.

Read More: Key Differences in Excel: Form Control Vs. ActiveX Control

2. Inserting Shapes to Create a Macro Button

We know, Excel has a Shapes feature in the Insert ribbon. The amazing part is, we can assign macro with any kind of shape too.

Steps:

• Go to Insert ribbon, then choose your preferred shape from the Shapes We chose Rectangle with Rounded Corners.

• Again by following the first section, set the button size.
• From the Shape Format ribbon, you can change the shape style too. We chose- Blue, Accent 5 style.

• Next, double-click on the button and type the name.

• After that, right-click on the button and select Assign Macro from the context menu.

A few moments later, the Assign Macro dialog box will appear.

• Select the macro and press OK.

• Nothing to do more, just click the button to run the macro.

Read More: How to Assign Macro to Button in Excel (2 Easy Methods)

Similar Readings

• How to Use VBA Code for Submit Button in Excel (with Easy Steps)
• Clear Cells in Excel with Button (with Detailed Steps)
• How to Hide Columns with Button in Excel (4 Suitable Methods)
• Create & Apply Option Button Click Event in Excel VBA
• How to Change Color of Toggle Button When Pressed in Excel

3. Using Quick Access Toolbar to Make a Macro Button

The previous method was to set a button in a sheet but what if we could make a button that we could use anytime for any sheet? Maybe it will be very useful for some particular situations. Here we’ll add this type of button to the Quick Access Toolbar.

Steps:

• First, click on the Customize Quick Access Toolbar icon from the upper border of the Excel window.
• Then select More Commands.

• At this moment, choose Macros from the Choose commands from drop-down box.
• Soon after, the available macros will appear here. Select our inserted macro name and press Add>>.

• By clicking on the Modify button we can change the button symbol.

• We used the Play icon for the button.

• Finally, nothing to do more, just press OK.

Have a look, the button is available in the toolbar. Now it doesn’t matter which sheet you are in, you will get it available for any sheet.

• Click on the button and it will work for the active sheet.

The output after clicking on the button.

Read More: How to Edit a Macro Button in Excel (5 Easy Methods)

Conclusion

That’s all for the article. We hope the procedures described above will be good enough to create a macro button in Excel. Feel free to ask any question in the comment section and please give us feedback. Visit ExcelDemy to explore more.

What Ribbon tab contain macro controls?

How to add a macro to excel?