Change event macros, macro crashes when row deleted

[Legacy 96851]

Let's see how quickly the collective brain of the most spreadsheet-advanced forum on the internet can fix this. Actually, I figure it's just a basic command or if statement I'm missing, but I don't know how to phrase it.

This is my worksheet's change event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AD:AF")) Is Nothing Then
        Call Summary.summarize
    End If
    If Not Intersect(Target, Sheets("ProjectData").UsedRange) Is Nothing Then
        edited = Target.Row
        SingleVal Sheets("ProjectData").Range(edited & ":" & edited)
    End If
End Sub

As you can see, if any cell in columns AD:AF is changed, both of these macros get called. In and of itself that's no problem, but whenever I delete a row, Excel locks up and doesn't come back. Deleting columns, even those three targeted, doesn't cause any problems. Is there a way to put a something like "if target was not deleted" in the code?

 

[Jonmo1]

Try putting in a condition that counts how many cells are changed at once?

Put this as the first line

IF Target.Count > 1 Then Exit Sub

HTH

 

[Legacy 96851]

Thanks, nice call. There's still an issue if an individual cell in rows AD:AF gets deleted, but I'll personally punch in the face whatever users decides he's going to delete individual cells.

 

[Norie]

Try turning off events.

Application.EnableEvents = False
' your code
Application.EnableEvents = True

But be careful where and how you use this as VBA doesn't automatically turn them back on again.

Oh, and it might actually help if you explained what the called code does, perhaps even post it.

Because that's probably were the problem lies rather than in the event code.

 

[Legacy 96851]

A macro doesn't delete the rows, that's manual, so I don't see how I could turn off events. As for the two macros run on change, they're really long, and at the moment I don't have the time to trim them down for posting.

 

[Norie]

Did I say a macro deleted the row?

My main point is that the problem is most likely in the code you are calling not the code you've posted.

But without seeing that other code it's hard to be sure about that.

 

[Legacy 96851]

Right, I get your point. My question is, where do you want me to turn events on and off?

 

[Norie]

Well I don't want you to do that, it's a suggestion that might fix your problem.

If the code is locking up I suggest you try and find out why using the various debug facilities available in VBA.

Try setting breakpoints, watches, stepping through with F8 etc.

 

[Jonmo1]

You would put the Disable Events in the Event code...
something like

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("AD:AF")) Is Nothing Then
        Call Summary.summarize
    End If
    If Not Intersect(Target, Sheets("ProjectData").UsedRange) Is Nothing Then
        edited = Target.Row
        SingleVal Sheets("ProjectData").Range(edited & ":" & edited)
    End If
Application.EnableEvents = True
End Sub

 

[Legacy 96851]

Well I don't want you to do that, it's a suggestion that might fix your problem.

If the code is locking up I suggest you try and find out why using the various debug facilities available in VBA.

Try setting breakpoints, watches, stepping through with F8 etc.

Face = palm. It's like asking the help button.
You made a suggestion, and I'm just trying to figure out what you meant, I don't understand why you've gotten cryptic and mildly passive-aggressive.
I could ask you again where you think Events On/Off should go, but you may simply direct me to use the keyboard and tell me "that's how you type", since you seem to be treating me as though I'm a gorilla. Honestly, I know there are plenty of people here who join just to ask one stupid question, and you really can't gauge someone's experience by their count of 50 posts. Nonetheless, telling me how to use the VB Editor's basic features to look for issues barely qualifies as help. For the record, I never post here unless I've already A.) spent at least a half hour, oftentimes longer, trying to correct the problem myself and B.) done an extensive Google search, to whatever extent possible.

Sorry for coming off as upset, I'm really not. I'm just trying to clarify where I'm coming from. I don't expect you guys to magically solve all of my problems, or even solve all of them at all. But when I ask for clarification, making sarcastic remarks about my choice of words seems unnecessary and childish. Oh, well, maybe I was a little insulted yesterday when you told me "you have to change A1 to your range"