How can we enable / disable a Worksheet_SelectionChange Event?

Lai Kan Leon 596 Reputation points
2021-08-29T09:31:43.127+00:00

Hello,

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.


HOW CAN WE DO THIS???


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
.Worksheet.Cells.FormatConditions.Delete
.EntireRow.FormatConditions.Add xlExpression, , "TRUE"
.EntireRow.FormatConditions(1).Interior.Color = vbCyan
' ///
.EntireColumn.FormatConditions.Add xlExpression, , "TRUE"
.EntireColumn.FormatConditions(2).Interior.Color = vbCyan
.FormatConditions.Delete
'////
.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?

Thanks
Leon

0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 120K Reputation points
    2021-08-29T13:26:00.56+00:00

    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
    2021-08-29T15:25:43.9+00:00

    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,
    Leon

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.