Macro to add button to ribbon?

TattYY

New Member
Joined
Apr 6, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. MacOS
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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


The above code creates a temporary new ribbon, which would normally vanish if you were to close then re-open Excel.
By putting this in the Add-in and naming the sub Auto_Open it creates the toolbar every times you start Excel.

Bit of a dirty way to do it, but it does the job in a simple way and requires no messing about with XML or CustomUI


Thanks
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top