VBA code to change label of multiple ribbon buttons on running a macro

Question

I have used the spreadsheet provided here to create a ribbon and it worked great as long as it was all about assigning macros to the buttons. However, I wanted to include configuration details in a group of the tab to make it visible all the time without consuming excel sheet real estate. Here is the to-be state: Group in the custom tab

I have a small macro to select environment and identify the values of various fields shown in the image. Here is what the code looks like:

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 

I know that Get Label is the way to do it but I just do not have any clue as to how to achieve what I want to achieve. My current GetLabel callback works fine when I open the excel file and populates default values correctly (The IF part). I tried to include "Else" part to update label but it just does not work.

Original Code:

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

When I open the excel, all labels have values as defined above.

Below is the Modified code (unsuccessful effort to modify to change the label based on Change_Configuration Macro). Please note that modified code also does not throw any error when my workbook is opened and displays values correctly.

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 

Your help is greatly appreciated.

-Jevich

Answer 1

Solved it - Had to include onload call back for Ribbon after adjusting XML and then Invalidate function.