Events don't seem to trigger after deleting cell values

[ward376]

I have a workbook with the following code in a sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
Application.EnableEvents = False
If Target.Count > 1 Then Exit Sub
If Right(Target.Address, 2) = "$1" Then Exit Sub
With Me
If Left(Target.Address, 3) = "$A$" Then
With Target.Cells.Offset(0, 1)
.FormulaR1C1 =
"=IF(ISNA(IF(ISBLANK(RC[-1]),"""",VLOOKUP(RC[-1],SAPplantno,
2,FALSE)))," & _
"""Plant Description not found."",
(IF(ISBLANK(RC[-1]),"""",VLOOKUP(RC[-1],SAPplantno,2,FALSE))))"
.Value = .Value
End With
'MsgBox Target.Address & " a"
ElseIf Left(Target.Address, 3) = "$C$" Then
With Target.Cells.Offset(0, 1)
.FormulaR1C1 =
"=IF(ISBLANK(RC[-1]),"""",IF(ISNA(VLOOKUP(RC[-1],matdesclu,2,FALSE)),"
& _
"""Description not found."",
(VLOOKUP(RC[-1],matdesclu,2,FALSE))))"
.Value = .Value
End With
'MsgBox Target.Address & " c"
ElseIf Left(Target.Address, 3) = "$D$" Then
With Target.Cells.Offset(0, -1)
.FormulaR1C1 =
"=IF(ISBLANK(RC[1]),"""",IF(ISNA(VLOOKUP(RC[1],MatNumlu,2,FALSE))," &
_
"""Description not found."",
(VLOOKUP(RC[1],MatNumlu,2,FALSE))))"
.Value = .Value
End With
'MsgBox Target.Address & " d"
ElseIf Left(Target.Address, 3) = "$F$" Then
With Target.Cells.Offset(0, 1)
.FormulaR1C1 =
"=IF(ISNA(IF(ISBLANK(RC[-1]),"""",VLOOKUP(RC[-1],SAPplantno,
2,FALSE)))," & _
"""Plant Description not found."",
(IF(ISBLANK(RC[-1]),"""",VLOOKUP(RC[-1],SAPplantno,2,FALSE))))"
.Value = .Value
End With
'MsgBox Target.Address & " f"
'Else
End If
End With
Application.EnableEvents = True

Exit Sub
errHandler:
MsgBox Err.Number & " " & Err.Description
End Sub

It all works great unless you go back and delete (clearcontents)
something out of multiple cells; then it seems like nothing will
trigger. (before save, before print etc.) No errors, no action. I've
tried manually setting enableevents to true (and in multiple events),
verifying targets and other things but it won't start working again
until I close and re-open the file. I feel like I may be overlooking
something simple. I think it has to do with exiting if the target
count is more than one, but leaving that out produces other
undesirable actions.

I really need to make automation as transparent as possible and limit
interactions/complexity as much as possible to the users.

A somewhat related double post: Whenever I have code in the beforesave
event and close the app with multiple workbooks open, the workbook(s)
won't close even if the code is as simple as me.save. Learned to live
with this one, but if anyone has an explanation, I'd be interested.

Thanks!
ward376

 

[ward376]

code didn't wrap right if you try it

 

[Dave Peterson]

I bet the event fires when you update/clear multiple cells, but as soon as it
gets to this line:

If Target.Count > 1 Then Exit Sub

The routine exits. And even worse, it exits with .enableevents = false. That
means that the event won't fire for the next change.

Try this order:

If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False

========
As for the second question, I'd guess that there was an error in the code--maybe
with events again???? I think you'll have to post some of the offending code
for anyone to really help.

I have a workbook with the following code in a sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
Application.EnableEvents = False
If Target.Count > 1 Then Exit Sub
If Right(Target.Address, 2) = "$1" Then Exit Sub
With Me
If Left(Target.Address, 3) = "$A$" Then
With Target.Cells.Offset(0, 1)
.FormulaR1C1 =
"=IF(ISNA(IF(ISBLANK(RC[-1]),"""",VLOOKUP(RC[-1],SAPplantno,
2,FALSE)))," & _
"""Plant Description not found."",
(IF(ISBLANK(RC[-1]),"""",VLOOKUP(RC[-1],SAPplantno,2,FALSE))))"
.Value = .Value
End With
'MsgBox Target.Address & " a"
ElseIf Left(Target.Address, 3) = "$C$" Then
With Target.Cells.Offset(0, 1)
.FormulaR1C1 =
"=IF(ISBLANK(RC[-1]),"""",IF(ISNA(VLOOKUP(RC[-1],matdesclu,2,FALSE)),"
& _
"""Description not found."",
(VLOOKUP(RC[-1],matdesclu,2,FALSE))))"
.Value = .Value
End With
'MsgBox Target.Address & " c"
ElseIf Left(Target.Address, 3) = "$D$" Then
With Target.Cells.Offset(0, -1)
.FormulaR1C1 =
"=IF(ISBLANK(RC[1]),"""",IF(ISNA(VLOOKUP(RC[1],MatNumlu,2,FALSE))," &
_
"""Description not found."",
(VLOOKUP(RC[1],MatNumlu,2,FALSE))))"
.Value = .Value
End With
'MsgBox Target.Address & " d"
ElseIf Left(Target.Address, 3) = "$F$" Then
With Target.Cells.Offset(0, 1)
.FormulaR1C1 =
"=IF(ISNA(IF(ISBLANK(RC[-1]),"""",VLOOKUP(RC[-1],SAPplantno,
2,FALSE)))," & _
"""Plant Description not found."",
(IF(ISBLANK(RC[-1]),"""",VLOOKUP(RC[-1],SAPplantno,2,FALSE))))"
.Value = .Value
End With
'MsgBox Target.Address & " f"
'Else
End If
End With
Application.EnableEvents = True

Exit Sub
errHandler:
MsgBox Err.Number & " " & Err.Description
End Sub

It all works great unless you go back and delete (clearcontents)
something out of multiple cells; then it seems like nothing will
trigger. (before save, before print etc.) No errors, no action. I've
tried manually setting enableevents to true (and in multiple events),
verifying targets and other things but it won't start working again
until I close and re-open the file. I feel like I may be overlooking
something simple. I think it has to do with exiting if the target
count is more than one, but leaving that out produces other
undesirable actions.

I really need to make automation as transparent as possible and limit
interactions/complexity as much as possible to the users.

A somewhat related double post: Whenever I have code in the beforesave
event and close the app with multiple workbooks open, the workbook(s)
won't close even if the code is as simple as me.save. Learned to live
with this one, but if anyone has an explanation, I'd be interested.

Thanks!
ward376

 

[ward376]

You're exactly right. Thank you very much!