Multiple Worksheet_Change () error

[VARD]

Hi,
I had a Worksheet_Change(ByVal Target As Range) event, then I needed to add another event to the same worksheet, which I did. Then I read that it is a good practice to have an Application.EnableEvents = False and Application.EnableEvents = True statements in the code but I wasn't sure where exactly to put those, so I was experimenting and..... my both events stopped working even when I deleted "Application.EnableEvents =" statements and returned to have just the first event.

I would highly appreciate if you could help to correct and improve the code.

Private Sub Worksheet_Change(ByVal Target As Range)
' Unhide spouse column based on filing type.
Dim pctRng As Range

If Target.Address = ("$B$1") Then
If Target.Text = "T1" Then
Columns("C").EntireColumn.Hidden = True

ElseIf Target.Text = "T2" Then
Columns("C").EntireColumn.Hidden = False

ElseIf Target.Text = "T3" Then
Columns("C").EntireColumn.Hidden = False

ElseIf Target.Text = "T4" Then
Columns("C").EntireColumn.Hidden = True

ElseIf Target.Text = "T5" Then
Columns("C").EntireColumn.Hidden = True

End If
End If

' Enter range to watch for new entries in
Set pctRng = Range("B4:B12")

' Exit if updated cell not in watched range
If Not Intersect(Target, pctRng) Is Nothing Then

End If

' Check for each condition
If Range("Age_1") <= 30 Then
If Range("$B$18") > 10000 Then
MsgBox "You have entered ...............", _
vbRetryCancel + vbCritical, "WARNING"
End If
Else
If Range("$B$18") > 12000 Then
MsgBox "You have entered ............", _
vbRetryCancel + vbCritical, "WARNING"
End If

End If

End If

End Sub

When the code works, I also need to add cell "$C$18" to the below part of the code. So if you could please incorporate it into your solution, that would be great.

If Range("$B$18") > 10000 Then
MsgBox "You have entered ...............", _
vbRetryCancel + vbCritical, "WARNING"
End If
Else
If Range("$B$18") > 12000 Then
MsgBox "You have entered ............", _
vbRetryCancel + vbCritical, "WARNING"
End If

P.S. this is related to this post with updated cell addresses.

 

[StephenCrump]

Then I read that it is a good practice to have an Application.EnableEvents = False and Application.EnableEvents = True statements in the code but I wasn't sure where exactly to put those, so I was experimenting and.....

It's not a matter of good practice. It's something you may need to do, or that you may choose to do, depending on the circumstances.

Here's a simple example. If you run this code (don't!) it will get stuck in a loop, with each iteration changing A1 and triggering another call of the Sub.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Range("A1").Value = "This sheet last changed " & Format(Now, "d mmm yyyy hh:mm")

End Sub

So if you are going to make changes to a worksheet inside a Worksheet_Change Sub, you need to wrap like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    Range("A1").Value = "This sheet last changed " & Format(Now, "d mmm yyyy hh:mm")
    Application.EnableEvents = True

End Sub

Similarly, you may have code lines elsewhere that will trigger other event code, and depending on circumstances you may wish to prevent that code running:

Application.EnableEvents = False
'Code that will trigger an event, and you don't want the event code to run
Application.EnableEvents = True

If your code errors, and .EnableEvents is stuck on False, you'll need to reset to True for your event based code to work, e.g. by typing Application.EnableEvents = True in the Immediate Window.

A couple of other observations:

- I'm not sure what you intend to do with C18, but is should be easy to add in to your code?

- At the moment, you have two references to B18, so if it's >12000, you'll trigger two identical messages.

'- Rather than this:
If Not Intersect(Target, pctRng) Is Nothing Then

End If
'Do things


'You need:
If Not Intersect(Target, pctRng) Is Nothing Then
    'Do things with Intersect(Target, pctRng)
End If

'- This line allows only for the user changing a single cell, B1:
If Target.Address = ("$B$1") Then


'If you want to allow for more than one cell being changed
If Not Intersect(Target, Range("B1")) Then

 

[johnnyL]

If your code errors, and .EnableEvents is stuck on False, you'll need to reset to True for your event based code to work, e.g. by typing Application.EnableEvents = True in the Immediate Window.

That was what I was thinking.

 

[VARD]

It's not a matter of good practice. It's something you may need to do, or that you may choose to do, depending on the circumstances.

Here's a simple example. If you run this code (don't!) it will get stuck in a loop, with each iteration changing A1 and triggering another call of the Sub.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Range("A1").Value = "This sheet last changed " & Format(Now, "d mmm yyyy hh:mm")

End Sub

So if you are going to make changes to a worksheet inside a Worksheet_Change Sub, you need to wrap like this:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Application.EnableEvents = False
    Range("A1").Value = "This sheet last changed " & Format(Now, "d mmm yyyy hh:mm")
    Application.EnableEvents = True

End Sub

Similarly, you may have code lines elsewhere that will trigger other event code, and depending on circumstances you may wish to prevent that code running:

Application.EnableEvents = False
'Code that will trigger an event, and you don't want the event code to run
Application.EnableEvents = True

If your code errors, and .EnableEvents is stuck on False, you'll need to reset to True for your event based code to work, e.g. by typing Application.EnableEvents = True in the Immediate Window.

A couple of other observations:

- I'm not sure what you intend to do with C18, but is should be easy to add in to your code?

- At the moment, you have two references to B18, so if it's >12000, you'll trigger two identical messages.

'- Rather than this:
If Not Intersect(Target, pctRng) Is Nothing Then

End If
'Do things


'You need:
If Not Intersect(Target, pctRng) Is Nothing Then
    'Do things with Intersect(Target, pctRng)
End If

'- This line allows only for the user changing a single cell, B1:
If Target.Address = ("$B$1") Then


'If you want to allow for more than one cell being changed
If Not Intersect(Target, Range("B1")) Then

Thank you for the explanations. I deleted the second part of the code and used

Sub ReEnableEvents()
Application.EnableEvents = True
End Sub

and first part started working. I am still to add back and test the second part.