在Excel中使用VBA自定义功能区 [英] Customizing a ribbon with VBA in Excel

问题描述

我已经知道，在这个！ post（不同于MSProject）

具体来说，我可以将宏的路径更改为当前位置吗？

编辑
似乎这个页面可能会导致答案，但我仍然不知道如何，虽然

一些更详细的情况描述：

用户将下载一个文件，其中包含材料，设备，劳动力的列表。每列包含关于数量，定价等的信息
当用户单击此按钮时，我想创建（我已手动创建），另一个工作簿中称为主的宏启动并复制整个工作表（内容）对于自己，并执行程序做的一些事情，在它上面。

所以我面临的问题是twhen我向客户端发送一个新版本，他必须将其放在确切的位置，否则将无法正常工作。由于涉及到Mac和Windows电脑的混合，我宁愿看到一个情况，当用户打开WorkBook B（包含代码的那个）时，该按钮被分配给该过程。

这样一来，一个新版本只能打开一次，然后为了持续使用，用户只需打开下载的文件，点击相应的按钮，WorkBook B就会打开自己并执行。 >

也许还有其他方法可以解决这个问题。我没有检查是否更容易为快速访问工具栏分配按钮...

解决方案

这是我用来添加自定义工具栏的一些代码：

 设置cbToolbar = Application.CommandBars.Add（csToolbarName，msoBarTop，False，True）
 
使用cbToolbar 
设置ctButton1 = .Controls.Add（Type：= msoControlButton，ID：= 2950）
设置ctButton2 = .Controls.Add（Type：= msoControlButton，ID：= 2950）
设置ctButton3 = .Controls.Add（Type：= msoControlButton，ID：= 2950）
结束
 
与ctButton1 
 .Style = msoButtonIconAndCaption 
 .Caption =设置&选择列表
 .FaceId = 176 
 .OnAction =SetPicklist
结束
 
带有ctButton2 
 .Style = msoButtonIconAndCaption 
 .Caption =Set& Defaults
 .FaceId = 279 
 .OnAction =SetDefaults 
结束与
 
与ctButton3 
 .Style = msoButtonIconAndCaption 
 .Caption =& Visibility Settings
 .FaceId = 2174 
 .OnAction =VisibilitySettings
 End with 
 
 
带cbToolbar 
 .Visible = True 
 .Protection = msoBarNoChangeVisible 
结束
  

OnAction控制运行的宏...如果要扩展以在特定工作簿上运行宏，请使用whatever.xls！MacroName

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

解决方案

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"

这篇关于在Excel中使用VBA自定义功能区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！