Re-Run UI Ribbon on Worksheet Activate event

[Stephen W Allen]

This process is being run from an Add-In rather than a macro enabled workbook.

I have generated a custom UI Ribbon, where the controls of the ribbon are enabled/disabled depending on the workbook environment. Essentially, if the workbook, on being opened, contains a named control worksheet then most of the UI controls are enabled, otherwise they aren’t.

As a secondary control, I also want to handle the case where the named control worksheet is, or becomes, the active sheet. In this context, some of the Ribbon controls should remain enabled but others not.

I have a Class Module ready and waiting to trap the SheetActivate event and this passes information back to the “RibbonX” module. How can I re-run the UI Ribbon while the active workbook remains open?

Option Explicit
Public WithEvents xlApp As Application

Private Sub xlApp_SheetActivate(ByVal Sh As Object)
'If Test_CreateControl(ActiveWorkbook) = False Then
'    MsgBox Sh.Name
'End If
Call Test_IsControlSheet(Sh)
End Sub

 

[GWteB]

Perhaps something like the class module below (not tested...)

Option Explicit

Public WithEvents xlApp As Application

Private Type TLocals
    ControlSheet    As Worksheet
End Type
Private this As TLocals

' Stephen W Allen: (1) Essentially, if the workbook, on being opened, contains a named control worksheet
'                      then most of the UI controls are enabled, otherwise they aren’t.

'                  (2) As a secondary control, I also want to handle the case where the named control worksheet is,
'                      or becomes, the active sheet. In this context, some of the Ribbon controls should remain enabled but others not.

' after a wb is opened, it's activated as well
Private Sub xlApp_WorkbookActivate(ByVal Wb As Workbook)
    If HasAControlSheet(Wb) Then
        '
        ' do some Ribbon stuff (1)
    End If
End Sub

Private Sub xlApp_SheetActivate(ByVal Sh As Object)
    If Not this.ControlSheet Is Nothing Then
        If Sh.Name = this.ControlSheet.Name Then
            '
            ' do additional Ribbon stuff (2)
        End If
    End If
End Sub

Private Function HasAControlSheet(ByVal argWb As Workbook) As Boolean
    Dim oWs As Worksheet
    Set this.ControlSheet = Nothing

    For Each oWs In argWb
        If oWs.Name = "MyControlWorksheet" Then         ' <<<<<< change to suit
            Set this.ControlSheet = oWs
            HasAControlSheet = True
            Exit For
        End If
    Next oWs
End Function

 

[Stephen W Allen]

Hi GWteB

Thank you for your reply. I think my explanation of the problem is at fault. Here is significant tranche of the code in the standard "RibbonX" module.

On opening a workbook the Sub IsBtnEnabled(control As IRibbonControl, ByRef enabled) runs as expected. The crucial question is - how can I get it to run again when the active worksheet changes, after the after the initial opening of the workbook?

Option Explicit
Option Private Module

Public gobjRibbon As IRibbonUI
Const CONWSNAME As String = "DataList_and_MappingControl"
Const MAPWSNAME As String = "DataList_and_MappingIndex"
Dim IsControlledBook As Boolean
Dim IsMapBook As Boolean
Dim IsControlSheet As Boolean
Dim IsMappingIndex As Boolean
Dim IsMapCommentary As Boolean
Public xlApplication As MyEventsTrap

Sub TrapApplicationEvents()
Dim Sh As Object
Set xlApplication = New MyEventsTrap

'Assign the Excel Application object to the xlApp property
Set xlApplication.xlApp = Application

End Sub

Sub RibbonOnLoad(ribbon As IRibbonUI)
   Set gobjRibbon = ribbon
End Sub


Sub IsBtnEnabled(control As IRibbonControl, ByRef enabled)

    Select Case control.ID
        Case "CreateMapBookandControl"
            enabled = CreateMapBookandControl_Enabled
        Case "AmendMapBookandControl"
            If CreateMapBookandControl_Enabled = True Then
                enabled = False
            Else
                enabled = True
            End If
        Case "CollectMappingData"
            enabled = GetWorksheetByName()
        Case "CollectWsFormulae"
            If IsControlledBook = True And IsControlSheet = False Then
                enabled = True
            Else
                enabled = False
            End If
       Case "CollectWsNumbers"
            If IsControlledBook = True And IsControlSheet = False Then
                enabled = True
            Else
                enabled = False
            End If
       Case "CollectWsText"
            If IsControlledBook = True And IsControlSheet = False Then
                enabled = True
            Else
                enabled = False
            End If
       Case "CollectWbFormulae"
            If IsControlledBook = True Then
                enabled = True
            Else
                enabled = False
            End If
        Case "CollectWbText"
            If IsControlledBook = True Then
                enabled = True
            Else
                enabled = False
            End If
        Case "CollectWbErrors"
            If IsControlledBook = True Then
                enabled = True
            Else
                enabled = False
            End If
        Case "CollectWbNames"
            If IsControlledBook = True Then
                enabled = True
            Else
                enabled = False
            End If
        Case "CollectWbShapes"
            If IsControlledBook = True Then
                enabled = True
            Else
                enabled = False
            End If
        Case "CollectWbValidation"
            If IsControlledBook = True Then
                enabled = True
            Else
                enabled = False
            End If
        Case Else
            enabled = True ' assume everything else with
            'getEnabled="IsBtnEnabled" is enabled by default

    End Select
Call TrapApplicationEvents
End Sub

Function GetWorksheetByName() As Boolean

    If Test_CreateControl(ActiveWorkbook) = False And _
        ActiveSheet.Name <> CONWSNAME Then
        GetWorksheetByName = True
    Else
        GetWorksheetByName = False
    End If

End Function

Function CreateMapBookandControl_Enabled() As Boolean
    
    If Test_CreateControl(ActiveWorkbook) = True And Test_CreateMapBook(ActiveWorkbook) = True Then
        CreateMapBookandControl_Enabled = True
    Else
        CreateMapBookandControl_Enabled = False
    End If

End Function

Function Test_CreateControl(Wb As Workbook) As Boolean
Dim TestSheet1 As Worksheet
On Error GoTo MyError
'Set TestSheet = wb.Worksheets(MAPWSNAME)
    Set TestSheet1 = Wb.Worksheets(CONWSNAME)
    Test_CreateControl = False
    Call Set_IsControlledBook(True)
        Set TestSheet1 = ActiveSheet
        If ActiveSheet.Name = CONWSNAME Then
            Call Set_IsControlSheet(True)
        Else
            Call Set_IsControlSheet(False)
        End If
        
    Exit Function
    
MyError:
    Test_CreateControl = True
    Call Set_IsControlledBook(False)
    Call Set_IsControlSheet(False)
End Function

Function Test_CreateMapBook(Wb As Workbook) As Boolean
Dim TestSheet1 As Worksheet
On Error GoTo MyError
    Set TestSheet1 = Wb.Worksheets(MAPWSNAME)
    'Set TestSheet1 = wb.Worksheets(CONWSNAME)
    Test_CreateMapBook = False
    Exit Function
MyError:
    Test_CreateMapBook = True
End Function

Sub Set_IsControlledBook(Val As Boolean)
Dim StoreVal As Variant

StoreVal = IsControlledBook

    IsControlledBook = Val
    
End Sub

Sub Set_IsControlSheet(Val As Boolean)
    IsControlSheet = Val

End Sub

Sub Test_IsControlSheet(Sh As Object)

If Sh.Name = CONWSNAME Then
    Call Set_IsControlSheet(True)
Else
    Call Set_IsControlSheet(False)
End If
'set gobjRibbon.Invalidate
End Sub

Thank you,

Stephen

 

[Stephen W Allen]

My apologies. I had the solution in front of me but did not recognise it.

The crucial code is Sub Test_IsControlSheet(Sh as Object), the last Sub from above. It should read.

Sub Test_IsControlSheet(Sh As Object)

If Sh.Name = CONWSNAME Then
    Call Set_IsControlSheet(True)
Else
    Call Set_IsControlSheet(False)
End If
gobjRibbon.Invalidate
End Sub

This has been tested and operates as expected.

 

[GWteB]

Glad it's sorted. Thanks for letting me know.