Custom ribbon Delete button status at runtime

[tiredofit]

I have a workbook containing two mandatory worksheets.

I also have a custom ribbon with a single Delete button.

I want the button to be activated if the activesheet is NOT one of the two manadatory sheets.

This is in ThisWorkbook:

Option Explicit

Private Sub Workbook_Open()

    Call Temp

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  
    Call Temp

End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    
    Call Module1.UpdateCombo
    
End Sub

Private Sub Temp()

    Dim Flag As String
    
    Select Case ActiveSheet.CodeName
    
        Case wksMandatory1.CodeName, wksMandatory2.CodeName
    
            Flag = "Disable"
        
        Case Else
        
            Flag = "Enable"
    
    End Select

    Call Module1.UpdateDelete(Tag:=Flag)
    
End Sub

and this is Module1:

Option Explicit
   
    Dim MyRibbon As IRibbonUI

    Dim Status As String

Public Sub Initialise(ByRef ribbon As IRibbonUI)

    Set MyRibbon = ribbon
       
End Sub
       
Public Sub UpdateCombo()

    MyRibbon.InvalidateControl "Combo"

End Sub

Public Sub Delete_Sheet(ByRef control As IRibbonControl)

    ActiveSheet.Delete
    
End Sub

Public Sub UpdateDelete(Tag As String)

    Status = Tag
    
    If Not MyRibbon Is Nothing Then Call MyRibbon.Invalidate
        
End Sub

Private Sub GetEnabledMacro(ByRef control As IRibbonControl, _
                            ByRef returnedVal As Variant)

    If control.Tag = "Delete" And _
       Status = "Enable" Then
        
        returnedVal = True
    
    Else
        
        returnedVal = False
    
    End If

End Sub

This is the xml:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
         onLoad="Initialise">

    <ribbon startFromScratch="false">

            <tabs>

              <tab id="Test"
                      label="Test">

                <group id="Group7"
                        label="Sheets">

                    <button id="customButton7"
                            label="Delete Sheet"
                            image="DeleteSheet"
                          size="large"
                          onAction="Delete_Sheet"
                          getEnabled="GetEnabledMacro"
                            tag="Delete"/>

                </group>

               </tab>

            </tabs>

      </ribbon>

</customUI>

Everything works as expected but I don't understand this sub:

Public Sub UpdateDelete(Tag As String)

    Status = Tag
    
    If Not MyRibbon Is Nothing Then Call MyRibbon.Invalidate
        
End Sub

The program crashes if it were:

Public Sub UpdateDelete(Tag As String)

    Status = Tag
    
    Call MyRibbon.Invalidate
        
End Sub

This is caused (I think) by:

Private Sub Workbook_Open()

    Call Temp

End Sub

which IS necessary because this Workbook_Open event is NOT present, if the workbook is saved with the activesheet NOT being one the two mandatory sheets, (then closed and reopend, the delete button will be disabled.

But why would MyRibbon = Nothing when the workbook is first opened?

Thanks

 

[John_w]

Where are wksMandatory1 and wksMandatory2 declared and defined?

I don't think you need Workbook_Open because the ribbon's onLoad routine should handle any initialisation - call the Temp routine (an odd name) from the Initialise routine.

 

[tiredofit]

wksMandatory1 and wksMandatory2 are the codenames of the two mandatory worksheets, defined in the Project Explorer in the VB editor.

 

[John_w]

OK, understood. Try the changes I suggested then:

Public Sub Initialise(ByRef ribbon As IRibbonUI)
    Set MyRibbon = ribbon
    Call Temp
End Sub

Move Private Sub Temp() ... End Sub to Module1 and change it to Public Sub Temp().

Comment out the Workbook_Open routine.

 

[tiredofit]

OK, understood. Try the changes I suggested then:

Public Sub Initialise(ByRef ribbon As IRibbonUI)
    Set MyRibbon = ribbon
    Call Temp
End Sub

Move Private Sub Temp() ... End Sub to Module1 and change it to Public Sub Temp().

Comment out the Workbook_Open routine.

Thanks, it worked!

One more related question.

Do you know if it's possible, using VBA, to change the name of the custom tab (called Test in this example), upon opening the workbook?

 

[John_w]

Do you know if it's possible, using VBA, to change the name of the custom tab (called Test in this example), upon opening the workbook?

In the XML, change:

<tab id="Test" label="Test">

to:

<tab id="Test" getLabel="GetLabelMacro">

Add the following code to Module1:

Private Sub GetLabelMacro(ByRef control As IRibbonControl, ByRef returnedVal As Variant)
    returnedVal = "My Tab Name"
End Sub

 

[tiredofit]

In the XML, change:

<tab id="Test" label="Test">

to:

<tab id="Test" getLabel="GetLabelMacro">

Add the following code to Module1:

Private Sub GetLabelMacro(ByRef control As IRibbonControl, ByRef returnedVal As Variant)
    returnedVal = "My Tab Name"
End Sub

Thanks, just what I wanted.

Early weekend for me!