Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Events don't seem to trigger after deleting cell values
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Events don't seem to trigger after deleting cell values
code didn't wrap right if you try it
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Events don't seem to trigger after deleting cell values
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. ward376 wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Events don't seem to trigger after deleting cell values
You're exactly right. Thank you very much!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting cell values | Excel Programming | |||
Recurring annual events using a specific date as a trigger date | Excel Worksheet Functions | |||
Re - Excel Loading Multiple Instances to Trigger Events amidst Switching-from-One-to-Another | Excel Programming | |||
Time events to trigger VB (corrected) | Excel Programming | |||
Tim events to trigger VB programs | Excel Programming |