Worksheet_SelectionChange events not fire from add-in excel

Viewed 37

I created 2 files cusHighLight.xlsm and TestBook.xlsx I have executed in file cusHighLight some operations:

  1. 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> 
    
  2. 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
  1. 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

0 Answers
Related