Checkbox with IRibbon Control for SheetSelectionChange

[hikeboy]

I have an addin that uses SheetSelectionChange event to highlight the entire row and column in a sheet when I click on a cell. I would like the option to turn the highlighting on/off by selecting a checkbox. I added a checkbox by customizing the ribbon, and I have code that passes the value of the CheckBox pressed state into my SheetSelectionChange event, but the toggle on/off is not working. The highlighting either stays on the pressed state that exists when I open the sheet, and it won't change. Below is the code I have.

Code in ThisWorkBook:

Public WithEvents App As Application
Public Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 If Target.Cells.Count > 1 Then Exit Sub
    
    If CheckBoxResult = True Then
    Application.ScreenUpdating = False
    ' Clear the color of all the cells
    Cells.Interior.ColorIndex = 0
    With Target
        ' Highlight the entire row and column that contain the active cell
        .EntireRow.Interior.Color = RGB(250, 250, 250)
        .EntireColumn.Interior.Color = RGB(250, 250, 250)
    End With
    Application.ScreenUpdating = True
    
    End If
End Sub

Private Sub Workbook_Open()
     Set App = Application
End Sub

Code in Module1:

Public CheckBoxResult As Boolean
Function CheckBoxAction(control As IRibbonControl, pressed As Boolean)
    
If pressed = True Then
CheckBoxResult = True
End If
End Function

XML for Checkbox:

<customUI xmlns="[URL]http://schemas.microsoft.com/office/2006/01/customui[/URL]">
  <ribbon>
    <tabs>
      <tab idMso="TabHome">
        <group idMso="GroupFont" visible="false" />
      </tab>
      <tab id="CustomTab" label="UTILITIES">
        <group id="HighlightRowColumn" label="HighlightRowColumn">
          <checkBox id="CheckBox1" label="Highlight On/Off" 
            screentip="This is a check box" 
            onAction="CheckBoxAction" />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

Thank you for helping with this.

 

[hikeboy]

Sorry, the xml code didn't come through correctly. Here it is again:

 

[Andrew Poulsom]

You don't seem to be setting CheckBoxResult to False anywhere.

 

[hikeboy]

Wow. That worked. Thank you Andrew. I don't quite understand why it worked. By default Excel doesn't highlight rows/columns. So when the Checkbox is pressed it runs the code to highlight. When it's not pressed I assumed it wouldn't run the code to highlight, so there would be no highlighting, and hence no need to define the Checkbox = False. Nevertheless, adding an Else statement to the Sub and the Function worked. Here is the code:

Public CheckBoxResult As Boolean
Function CheckBoxAction(control As IRibbonControl, pressed As Boolean)
    
If pressed = True Then
CheckBoxResult = True
Else: CheckBoxResult = False
End If
End Function

Public WithEvents App As Application
Public Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 
 If CheckBoxResult = True Then
    Application.ScreenUpdating = False
    ' Clear the color of all the cells
    Cells.Interior.ColorIndex = 0
    With Target
        ' Highlight the entire row and column that contain the active cell
        .EntireRow.Interior.Color = RGB(250, 250, 250)
        .EntireColumn.Interior.Color = RGB(250, 250, 250)
    End With
    Application.ScreenUpdating = True
    
   Else
    
  End If
End Sub
Private Sub Workbook_Open()
     Set App = Application
End Sub

Thanks so much Andrew.