Macro to add button to ribbon?

[TattYY]

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

 

[yinkajewole]

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

 

[TattYY]

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

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 Autpen 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