Change event macros, macro crashes when row deleted

L

Legacy 96851

Guest
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.
Code:
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?
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 
L

Legacy 96851

Guest
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Try turning off events.
Code:
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.:)
 
L

Legacy 96851

Guest

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Did I say a macro deleted the row?:eek:

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.
 
L

Legacy 96851

Guest

ADVERTISEMENT

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
You would put the Disable Events in the Event code...
something like

Code:
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
 
L

Legacy 96851

Guest
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top