5

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

Community
  • 1
  • 1
oneindelijk
  • 606
  • 1
  • 6
  • 18
  • 1
    What does "paths of the macros" mean? – Mathieu Guindon Aug 29 '13 at 22:44
  • The icons/buttons in the Ribbon point to the macro in the excel-file at the location/path when I've assigned them, but when the user opens the same file from another directory (i.e. Downloads) and tries to run the macros with the button, it is still pointing to that previous file, hence the path of the macros. I guess there is a much better way to explain this :) – oneindelijk Sep 02 '13 at 21:13
  • Wouldn't it be simpler to be grouping them in an add-in then? – Mathieu Guindon Sep 02 '13 at 21:17
  • Hi, retailcoder, would (or could) an add-in be embedded in the particular excel file ? (It's an xlsm) – oneindelijk Sep 03 '13 at 14:59

1 Answers1

9

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"

Simon
  • 567
  • 5
  • 14
  • ... or use "'" & thisworkbook.name & "'!MacroName" maybe? (That's a single quote inside double quotes.... then single quote before exclamation) – Simon Aug 30 '13 at 00:32
  • 3
    While this *works* in Excel 2007+, the `CommandBars` do not really customize the `Ribbon` per se. To Truly customize the ribbon, it's necessary to modify the workbook's XML with the CustomUI XML editor. – David Zemens Aug 30 '13 at 01:23