这是基于 Ron de Bruin 的代码“启用或禁用自定义功能区按钮”。
以下示例功能区包含两个选项卡:
- “工具”:暂时为空
- “宏”:3组
所有按钮均由tag
和构建getEnabled
。默认情况下,禁用“Group6”和“Group7”,启用“Group8”和“Button57”。我想使用“Button57”(受密码保护)启用“Group6”和“Group7”。
EnableAllControls
通过在 VBA 编辑器中按“播放”来运行宏可以按预期工作。
但使用Call
不起作用:
'Callback for Button57 onAction
Sub c001_01_EnableTabMacros(control As IRibbonControl)
Call EnableAllControls
End Sub
你怎么Call EnableAllControls
?
' -- XML
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
onLoad="LoadRibbon">
<ribbon>
<tabs>
<tab id="ToolsV1.0.0" label="Tools" insertAfterMso="Developer">
' built-in controls + a couple of macro buttons
</tab>
<tab id="MacrosV4.0.0" label="Macros" insertAfterMso="ToolsV1.0.0">
<group id="Group6"
label="Chart Editing">
<button id="Button3_1" label="Del Textbox"
size="normal"
onAction="a003_01_DeleteTextBoxesFromChart_v1_0"
imageMso="ClearFormatting"
tag="Group6Button3_1"
getEnabled="GetEnabledMacro"/>
<button id="Button3_2" label="Del Zeroes"
size="normal"
onAction="a003_02_DeleteLabelsValueZero_v1_0"
imageMso="ChartDataLabel"
tag="Group6Button3_2"
getEnabled="GetEnabledMacro"/>
</group>
<group id="Group7"
label="Protect">
<button id="Button17" label="ChtLock"
size="large"
onAction="a003_03_ChartProtectFormatting_v1_0"
imageMso="Lock"
tag="Group7Button17"
getEnabled="GetEnabledMacro"/>
</group>
<group id="Group8"
label="Code">
<button id="Button57" label="Macros 4.0"
size="large"
onAction="c001_01_EnableTabMacros"
imageMso="MicrosoftVisualFoxPro"
tag="Group8Button57"
getEnabled="GetEnabledMacro"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
' -- Standard Module "RibbonModule"
Option Explicit
Public RibUI As IRibbonUI
Public MyTag As String
Sub LoadRibbon(ribbon As IRibbonUI)
Set RibUI = ribbon
RibUI.InvalidateControl "xy"
RibUI.ActivateTab "ToolsV1.0.0"
Call EnableControlsWithCertainTag8
End Sub
Sub GetEnabledMacro(control As IRibbonControl, ByRef Enabled)
If MyTag = "Enable" Then
Enabled = True
Else
If control.Tag Like MyTag Then
Enabled = True
Else
Enabled = False
End If
End If
End Sub
Sub RefreshRibbon(Tag As String)
MyTag = Tag
If RibUI Is Nothing Then
MsgBox "Error, Save/Restart your workbook" & vbNewLine & _
"Visit this page for a solution for Win Excel : _
http://www.rondebruin.nl/ribbonstate.htm"
Else
RibUI.Invalidate
End If
End Sub
'Note: Do not change the code above
Sub EnableControlsWithCertainTag8()
'Enable only the controls with a Tag that start with "Group8"
Call RefreshRibbon(Tag:="Group8*")
End Sub
Sub EnableAllControls() '<- works: press play in vbe
'Enable all controls
Dim MyPW As String
MyPW = "pw"
If InputBox("Enter password to continue.", _
"Enter Password") <> MyPassword Then
Exit Sub
End If
Call RefreshRibbon(Tag:="*")
End Sub
' -- Standard Module "SubModule"
Option Explicit
' =========
' Callbacks
' =========
'Callback for Button57 onAction
Sub c001_01_EnableTabMacros(control As IRibbonControl)
Call EnableAllControls
End Sub
'Callback for Button3_1 onAction
Sub a003_01_DeleteTextBoxesFromChart_v1_0(control As IRibbonControl)
'...
End Sub
'Callback for Button3_2 onAction
Sub a003_02_DeleteLabelsValueZero_v1_0(control As IRibbonControl)
'...
End Sub
'Callback for Button3_3 onAction
Sub a003_03_ChartProtectFormatting_v1_0(control As IRibbonControl)
'...
End Sub
' -- ThisWorkbook
Option Explicit
Private Sub Workbook_Open()
MyTag = "Enable"
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
RibUI.InvalidateControl "xy"
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
RibUI.InvalidateControl "xy"
End Sub
通过一些试验和排除,似乎因为回调子名称以字母c开头,所以
c001_01_EnableTabMacros
编译器无法看到该名称。将前导c更改为另一个(例如d ),它确实有效。