I created 2 files cusHighLight.xlsm and TestBook.xlsx I have executed in file cusHighLight some operations:
Custom Ribbon with Custom IU Ribbon application with codes:
<customUI onLoad = "ControlRibbon" xmlns="http://schemas.microsoft.com/office/2009/07/customui"> <ribbon> <tabs> <tab id="HighLight" label="High Light"> <group id="grpHighLight" label="Lighting"> <checkBox id="cbHighLight" label="Turn On High Light" onAction="HighLightRow"/> </group > </tab> </tabs> </ribbon> </customUI>into Workbook code module class, I want to execute sub Worksheet_SelectionChange following:
Option Explicit
Dim WithEvents mws As Worksheet
Private Sub Workbook_Open()
Set mws = ActiveSheet
End Sub
Private Sub mws_SelectionChange(ByVal Target As Range)
ActiveWindow.VisibleRange.FormatConditions.Delete
Dim rgRow As Range
Dim rgCol As Range
Dim rgOnScreen As Range
Dim intRow, intCol As Integer
Dim cbBox As Boolean
cbBox = cbCheck
If cbBox Then
Set rgOnScreen = ActiveWindow.VisibleRange
intRow = rgOnScreen.Row
intCol = rgOnScreen.Column
Set rgRow = Range(Cells(Target.Row, intCol), Target.Address)
Set rgCol = Range(Cells(intRow, Target.Column), Target.Address)
rgCol.FormatConditions.Add Type:=xlExpression, Formula1:=True
rgCol.FormatConditions(1).Interior.Color = RGB(226, 226, 150)
rgRow.FormatConditions.Add Type:=xlExpression, Formula1:=True
rgRow.FormatConditions(2).Interior.Color = RGB(226, 226, 150)
End If
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Set mws = ActiveSheet
CheckHighLight
End Sub
- into a Standard module have some Subs following:
Option Explicit
Public MyRibbon As IRibbonUI
Public cbCheck As Boolean
Sub ControlRibbon(Ribbon As IRibbonUI)
Set MyRibbon = Ribbon
End Sub
Sub CheckHighLight()
MyRibbon.InvalidateControl ("cbHighLight")
End Sub
Sub HighLightRow(control As IRibbonControl, Pressed As Boolean)
cbCheck = Pressed
End Sub
I run in file cusHighLight.xlsm that's oke but when I save as in .xlam to use in other workbooks. I created "TestBook.xlsx" and add the add-in but it do not work.
How do I fix it. Thank you so much