How can we enable / disable a Worksheet_SelectionChange Event?

Lai Kan Leon 596 Reputation points


I have a worksheet called "Sheet2" which contains a Worksheet_SelectionChange event.

What it does?
Each time we click a cell on "Sheet2", it highlights both the column and row of the active cell.
Very useful for large worksheets.
The sub works correctly.

Now, I want to give our users the possibility of disabling this sub if it annoys them.

So, I put a button (toggle) on my UserForm called "Disable Highlighting". When we click on it, it should deactivate the Sub.


Here is my (failed) attempt:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Me.tgl_Highlight.Value = True Then '// DEBUGGING DHOWS ERROR HERE!!!

If Target.Cells.Count > 1 Then Exit Sub
With Target
.EntireRow.FormatConditions.Add xlExpression, , "TRUE"
.EntireRow.FormatConditions(1).Interior.Color = vbCyan
' ///
.EntireColumn.FormatConditions.Add xlExpression, , "TRUE"
.EntireColumn.FormatConditions(2).Interior.Color = vbCyan
.FormatConditions.Add xlExpression, , "TRUE"
.FormatConditions(1).Interior.Color = vbYellow

End With

ElseIf Me.tgl_Highlight.Value = True Then

End If

End Sub

My code does not recognise the Tgl_Highlight button.

Any ideas how to make my program work?


0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 113.7K Reputation points

    Try 'If UserForm1.tgl_Highlight.Value Then...', where UserForm1 is the name of your form.

    0 comments No comments

  2. Lai Kan Leon 596 Reputation points

    Hi, Viorel-1

    Thanks for reply.

    I found a solution:

    Button_Click event adds a note in cell A1 of another worksheet: either "ENABLED" or "DISABLED" depending on which command button (not toggle) is clicked.

    Then the Worksheet_SelectionChange event will read cell "A1" and decide its course of action.

    This trick works well. I don't know if there are better methods.

    Best Regards,

    0 comments No comments