Customizing a ribbon with VBA in Excel

Question

I've already learned that creating a custom tab is in Excel not possible in this! post (unlike i.e. MSProject)

Specifically, can I change the paths of the macros to the current location ?

Edit It seems that this page may lead to the answer, but I still don't know how, though

Some more in-detail description of the situation:

The user will download a file, which contains a list of materials, equipment, labor. Each column contains information about quantities, pricing etc When the user click on this button I want to create (I have created manually), a macro called 'Main' in another workbook launches and copies the whole sheet (the contents) to itself and performs some things that procedures do, on it.

So the problem I'm facing, is twhen I'm sending a new version to the client, he has to put it in the exact location or it won't work. Since there's a mix of Mac and Windows computers involved, I'd rather see a situation where the button is assigned to the procedure when the user opens WorkBook B (the one that contains the code).

This way, a new version has to be openened only once, and then for continuous use, the user can just open the downloaded file, click the appropriate button and WorkBook B will open itself and execute.

Maybe there's other ways to go about this. I haven't checked if it's not easier to assign a button to the quick access toolbar...

Answer 1

This is some code I use to add a custom toolbar:

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

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

With ctButton1
    .Style = msoButtonIconAndCaption
    .Caption = "Set &Picklists"
    .FaceId = 176
    .OnAction = "SetPicklist"
End With

With ctButton2
    .Style = msoButtonIconAndCaption
    .Caption = "Set &Defaults"
    .FaceId = 279
    .OnAction = "SetDefaults"
End With

With ctButton3
    .Style = msoButtonIconAndCaption
    .Caption = "&Visibility Settings"
    .FaceId = 2174
    .OnAction = "VisibilitySettings"
End With


With cbToolbar
    .Visible = True
    .Protection = msoBarNoChangeVisible
End With

The 'OnAction' controls the macro that runs... If you wanted to expand that to run a macro on a specific workbook, use "whatever.xls!MacroName"