在 Excel 中使用 VBA 自定义功能区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18522048/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Customizing a ribbon with VBA in Excel
提问by oneindelijk
I've already learned that creating a custom tab is in Excel not possible in this! post (unlike i.e. MSProject)
我已经了解到，创建一个自定义选项卡在Excel中不可能这样！发布（不同于即 MSProject）
Specifically, can I change the paths of the macros to the current location ?
具体来说，我可以将宏的路径更改为当前位置吗？
Edit It seems that this pagemay 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.
用户将下载一个文件，其中包含材料、设备、劳动力的清单。每列包含有关数量、定价等的信息当用户单击我想要创建的此按钮（我已手动创建）时，另一个工作簿中名为“Main”的宏启动并将整个工作表（内容）复制到自身并执行一些程序做的事情，就可以了。
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).
所以我面临的问题是，当我向客户发送新版本时，他必须将其放在确切的位置，否则将无法工作。由于涉及 Mac 和 Windows 计算机的混合，我宁愿看到这样一种情况，即当用户打开 WorkBook B（包含代码的那个）时将按钮分配给过程。
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.
这样，新版本只需打开一次，然后继续使用，用户只需打开下载的文件，单击相应的按钮，WorkBook B 将自行打开并执行。
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...
也许还有其他方法可以解决这个问题。我还没有检查将按钮分配给快速访问工具栏是否更容易...
回答by Simon
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"
'OnAction' 控制运行的宏...如果您想扩展它以在特定工作簿上运行宏，请使用“whatever.xls!MacroName”
