用于在运行宏时更改多个功能区按钮标签的VBA代码

Question

我已经使用here提供的电子表格创建了一个功能区，只要是为按钮分配宏，它就能工作得很好。但是，我希望在一组选项卡中包含配置详细信息，以使其始终可见，而不会占用excel表的实际空间。这就是将来的状态：Group in the custom tab

我有一个小的宏用来选择环境并确定图像中显示的各个字段的值。下面是代码的样子：

Sub Change_Configuration() 
    Dim ConfigButton As Object 
    Dim Labeling As String 
     'SelectEnvironment.Show
    Environment = "PROD" 
    If Environment = "QAS" Then 


        SourceSystem = "ABC" 
        TargetSystem = "DEF" 
        SourceSchema = "EFG" 
        TargetSchema = "GHI" 
    ElseIf Environment = "PROD" Then 
        SourceSystem = "IJK" 
        TargetSystem = "JKL" 
        SourceSchema = "LMN" 
        TargetSchema = "NOP" 
    End If 
    GetLabel ConfigButton, Labeling 'I do not know what to do here
End Sub 

我知道Get Label是一种方法，但我就是不知道如何实现我想要实现的目标。当我打开excel文件并正确填充默认值( IF部分)时，我当前的GetLabel回调可以正常工作。我试图包含"Else“部分来更新标签，但它就是不起作用。

原始代码：

Sub GetLabel(ByVal control As IRibbonControl, ByRef Labeling)
    Select Case control.ID

      Case "CustomTab": Labeling = "TEST_RIBBON"

      Case "GroupE": Labeling = "Configuration Details"
      Case "eButton01": Labeling = "Change Environment"
      Case "eButton02": Labeling = "Selected Environment" & " - " & "QAS"
      Case "eButton03": Labeling = "Source System" & " - " & "ABC"
      Case "eButton04": Labeling = "Source Schema" & " - " & "DEF"
      Case "eButton05": Labeling = "Target System" & " - " & "GHI"
      Case "eButton06": Labeling = "Target Schema" & " - " & "IJK"

    End Select
 End Sub

当我打开excel时，所有标签都有如上定义的值。

下面是修改后的代码(修改为基于Change_Configuration宏更改标签的尝试失败)。请注意，当我的工作簿打开并正确显示值时，修改后的代码也不会抛出任何错误。

Sub GetLabel(ByVal control As IRibbonControl, ByRef Labeling) 


    If Environment = vbNullString Then 


        Select Case control.ID 

        Case "CustomTab": Labeling = "My Tab" 


        Case "GroupE": Labeling = "Configuration Details" 
        Case "eButton01": Labeling = "Change Environment" 
        Case "eButton02": Labeling = "Selected Environment" & " - " & "QAS" 
        Case "eButton03": Labeling = "Source System" & " - " & "ABC" 
        Case "eButton04": Labeling = "Source Schema" & " - " & "DEF" 
        Case "eButton05": Labeling = "Target System" & " - " & "EFG" 
        Case "eButton06": Labeling = "Target Schema" & " - " & "GHI" 

        End Select 

    Else 
        Select Case ConfigButton 
        Case "eButton02": Labeling = "Selected Environment" & " - " & Environment 
        Case "eButton03": Labeling = "Source System" & " - " & SourceSystem 
        Case "eButton04": Labeling = "Source Schema" & " - " & SourceSchema 
        Case "eButton05": Labeling = "Target System" & " - " & TargetSystem 
        Case "eButton06": Labeling = "Target Schema" & " - " & TargetSchema 
        End Select 
    End If 

End Sub 

非常感谢您的帮助。

-Jevich

Answer 1

解决了这个问题-在调整XML之后，必须包含对Ribbon的onload回调，然后使函数无效。

Answer 2

相关代码太少，无法确保帮助您完成

但是试着改变一下：

Select Case ConfigButton

至：

Select Case control.ID

看看会发生什么..。