Re-Run UI Ribbon on Worksheet Activate event

Stephen W Allen

New Member
Joined
Jul 21, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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?

VBA Code:
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,442
Office Version
  1. 2013
Platform
  1. Windows
Perhaps something like the class module below (not tested...)

VBA Code:
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

New Member
Joined
Jul 21, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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?

VBA Code:
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

New Member
Joined
Jul 21, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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.
 
Solution

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,442
Office Version
  1. 2013
Platform
  1. Windows
Glad it's sorted. Thanks for letting me know.
 

Forum statistics

Threads
1,147,962
Messages
5,744,057
Members
423,843
Latest member
alex2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top