This cusxtomised ribbon has been built to re-valuate itself on changes of workbook and worksheet. However, the expected revaluation - see IsBtnEnabled code - no longer operates as worksheet change events take place.
The XML Code is as follows
<customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="ACBAMapping" label="ACBA-Mapping" insertAfterMso="TabView">
<group id="MapBooks" label="Parent Books">
<button id="CreateMapBookandControl" label="Create New" size="large" onAction="CreateMapBookandControl" imageMso="UpgradeWorkbook" screentip="Generates a new map book and control sheet" getEnabled="IsBtnEnabled"/>
<button id="AmendMapBookandControl" label="Amend" size="large" onAction="AmendMapBookandControl" imageMso="FormControlEditBox" screentip="Amends parent control sheet for added worksheets" getEnabled="IsBtnEnabled"/>
<toggleButton id="TGlButHideControl" label="Hide/ Unhide" size="large" onAction="HideMapControlSheet" imageMso="VisibilityVisible" getPressed="GetPressed" screentip="Hides or unhides the mapping control sheet" getEnabled="IsBtnEnabled"/>
</group>
There are several groups
</tab>
</tabs>
</ribbon>
</customUI>
There is a standard module called "RibbonX" which includes the following code.
Public gobjRibbon As IRibbonUI
Sub RibbonOnLoad(ribbon As IRibbonUI)
Set gobjRibbon = ribbon
MyHideEvent = True
End Sub
Sub IsBtnEnabled(control As IRibbonControl, ByRef enabled)
Select Case control.Id
Case "CreateMapBookandControl"
enabled = CreateMapBookandControl_Enabled
(There are several Case statements)
Case Else
enabled = True ' assume everything else with
'getEnabled="IsBtnEnabled" is enabled by default
End Select
Call TrapApplicationEvents
End Sub
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 ReSet_MyRibbon()
On Error GoTo Escape
gobjRibbon.Invalidate
Exit Sub
Escape:
Err.Clear
' MsgBox "Ribbon Error"
End Sub
A separate class module handles the Excel events "MyEventsTrap"
Option Explicit
Public WithEvents xlApp As Application
Private Sub xlApp_SheetActivate(ByVal Sh As Object)
Dim wb As Workbook
Set wb = ActiveWorkbook
If MyHideEvent = True Then
Call Test_IsMapBook(wb)
Call Test_IsControlSheet(Sh)
Call Test_IsMappingIndex(Sh)
Call Test_IsMapCommentary(Sh)
Call ReSet_MyRibbon
End If
MyHideEvent = True
End Sub
Private Sub xlApp_WorkbookActivate(ByVal wb As Workbook)
Dim Sh As Object
Set Sh = ActiveSheet
Call Test_IsMapBook(wb)
Call Test_IsControlSheet(Sh)
Call Test_IsMappingIndex(Sh)
Call Test_IsMapCommentary(Sh)
If MyHideEvent = True Then
Call ReSet_MyRibbon
End If
MyHideEvent = True
End Sub
The application events initiates the ReSet_MyRibbon call, but the review of the status of the ribbon buttons - IsButEnabled - is not activated when moving from one worksheet to the next within a workbook.
I'd grateful for advice or a solution.
Bookmarks