Try 'If UserForm1.tgl_Highlight.Value Then...', where UserForm1 is the name of your form.
How can we enable / disable a Worksheet_SelectionChange Event?
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
2 answers
Sort by: Most helpful
-
-
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