Hi all,

I created a custom ribbon which changes based on the worksheet based on information I got from Ron de Bruin. It took me sometime to figure it out as I kept getting an error which not directly related to the Ribbon customization per se.

When the workbook is opened, everything works fine. The issue occurs when I open the workbook. Upon opening the workbook, I use Workbook_open to perform some actions in the workbook but I also use Workbook_SheetActivate to change the customized Ribbon on the fly.

I believe the issue is that when workbook_open() is executed, the customized ribbon is not loaded yet. Upon opening the workbook, Rib = Nothing. Thus, my question is how can I handle the Workbook_SheetActivate to be executed after the workbook_open or similarly is there a way to force loading the customized ribbon as a first set in workbook_open().


In This.Workbook:



Private Sub Workbook_Open()
    Application.Calculation = xlAutomatic
    Application.TransitionNavigKeys = True

    ' SetZoom is simple macro for resetting the workbook sheets to 100% and to reset all sheets position to A1
    
    Call SetZoom
    
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        
    Select Case Sh.name
        Case "Overview": Call ShowOverviewToC
        Case "Inputs - Gen, Efficacy, Safety": Call ShowGenInputsToC
        Case "Inputs - Utilities and Costs": Call ShowHUCostsToC
        Case "Inputs - PSA": Call ShowPSAInputsToC
        Case "Incremental Analysis": Call ShowIncAnalysisToC
        Case Else: Call HideEveryTabGroupControl
    End Select
    
    
End Sub


'Ribbon module code

Option Explicit

Dim Rib As IRibbonUI
Public MyTag As String

'Callback for customUI.onLoad
Sub RibbonOnLoad(ribbon As IRibbonUI)
    Set Rib = ribbon
End Sub

Sub GetVisible(control As IRibbonControl, ByRef visible)
    If MyTag = "show" Then
        visible = True
    Else
        If control.Tag Like MyTag Then
            visible = True
        Else
            visible = False
        End If
    End If
End Sub

Sub RefreshRibbon(Tag As String)
    MyTag = Tag
    If Rib Is Nothing Then
        MsgBox "Error, Save/Restart your workbook"
    Else
        Rib.Invalidate
    End If
End Sub

'Note: Do not change the code above


'*************************************************************************************************
'Examples to show only the Tab, Group or Control you want with getVisible and tag in the RibbonX.
'*************************************************************************************************


Sub ShowDSAToC()

    Call RefreshRibbon(Tag:="ToC_DSA")
End Sub


Sub ShowOverviewToC()

    Call RefreshRibbon(Tag:="ToC_Overview")
End Sub


Sub ShowGenInputsToC()

    Call RefreshRibbon(Tag:="ToC_GenInputs")
End Sub


Sub ShowHUCostsToC()

    Call RefreshRibbon(Tag:="ToC_HUCosts")
End Sub

Sub ShowPSAInputsToC()

    Call RefreshRibbon(Tag:="ToC_PSAInputs")
End Sub


Sub ShowIncAnalysisToC()

    Call RefreshRibbon(Tag:="ToC_IncAnalysis")
End Sub


'Note: in this example every macro above will show you the custom tab.
'If you add more custom tabs this will be different

Sub HideEveryTabGroupControl()
'Hide every Tab, Group or Control(we use Tag:="")
    Call RefreshRibbon(Tag:="")
End Sub


Public Sub SetZoom()
    
    Application.ScreenUpdating = False
    
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        On Error Resume Next
        ws.Select
        ActiveWindow.Zoom = 100
        ActiveWindow.ScrollRow = 1
        ActiveWindow.ScrollColumn = 1
    Next ws
    
    Call tab_cover
    
End Sub


Kindest regards,

Dominic.