Solved Excel keeps crashing when macro runs 2 times

April 20, 2017 at 16:36:43
Specs: Windows 7
I have two codes on two worksheets in the same book. if i run the codes twice the excel file crashes everytime.

See More: Excel keeps crashing when macro runs 2 times

Reply ↓  Report •

#1
April 20, 2017 at 18:56:30
You sure haven't given us much to work with.

"My car is making a noise. What's wrong with it?"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
April 22, 2017 at 11:02:34
Sorry you're right DerbyDad i should have explained a little better.

On one worksheet i have the following code

Private Sub Worksheet_Change(ByVal Target As Range)
    'Determine if "Complete" was enteed in a single cell in Column L
 If Target.Cells.Count = 1 Then
   If Target.Column = 12 Then
     If Target = "Complete" Then
On Error GoTo EventsTrue
'Disable Events to prevent code from firing again
       Application.EnableEvents = False
'Determine value for Destination Row
   dstRow = Cells(Target.Row, "A") + 1
'Copy, Insert, Paste, Delete
         Target.EntireRow.Copy
         Sheets("Complete").Range("A" & dstRow).Insert Shift:=xlDown
         Target.EntireRow.Delete
'Enable Events
EventsTrue:
       Application.EnableEvents = True
     End If
   End If
 End If
End Sub

and on another sheet i have this code

Private Sub Worksheet_Change(ByVal Target As Range)
    'Determine if "In Progress" was enteed in a single cell in Column L
 If Target.Cells.Count = 1 Then
   If Target.Column = 12 Then
     If Target = "In Progress" Then
On Error GoTo EventsTrue
'Disable Events to prevent code from firing again
       Application.EnableEvents = False
'Determine value for Destination Row
        dstRow = Cells(Target.Row, "A") + 1
'Copy, Insert, Paste, Delete
         Target.EntireRow.Copy
         Sheets("CurrentTasks").Range("A" & dstRow).Insert Shift:=xlDown
         Target.EntireRow.Delete
'Enable Events
EventsTrue:
       Application.EnableEvents = True
     End If
   End If
 End If
End Sub

All in all it's the same code just different criteria as to when to run. so on the first sheet if i change the cell to "complete" the macro runs. If i go to the other sheet and change that cell back to "In Progress" and then the macro runs. If i do these steps a second time the program crashes ad restarts.

Hopefully this helps a better picture.

message edited by MattExcel


Reply ↓  Report •

#3
April 22, 2017 at 12:45:48
✔ Best Answer
Well, since I can't get your code to fail or cause Excel to crash I can't offer any direct solutions. If Excel was crashing for me, I would add breakpoints to my code so that I could control the "run" as opposed to letting it just do it's own thing. By using a breakpoint the code will stop where I want it to and then I can single step through and watch what is happening. If I'm lucky, Excel with crash at the same instruction each time and that might give me a clue as to what is happening.

I would like to comment on the wording of your post, just to make sure that you understand how a Worksheet_Change macro works. Maybe you just worded it poorly, and you do understand, but I just want to make sure. Maybe the interaction between the Worksheet_Change macro in each sheet is part of the problem.

You said: so on the first sheet if i change the cell to "complete" the macro runs. If i go to the other sheet and change that cell back to "In Progress" and then the macro runs.

In reality, as long as Events are enabled, the Worksheet_Change macro for a given sheet runs when you make any change to the sheet. For example, if you enter Fred in Complete!A1, VBA is going to see that the sheet has changed and the code stored in that sheet is going to run. The first instruction is:

If Target.Cells.Count = 1 Then

The If is going to be TRUE because you changed 1 cell, so the next instruction is going to be executed:

  If Target.Column = 12 Then

That If is going to be FALSE because the change was made to Column 1, so the code will jump down to the End If for that instruction and eventually Exit. The macro didn't do anything other than execute those 2 instructions, but it most definitely ran.

The key point here is that the macro doesn't just run when you make the change that it is watching for, it runs whenever any change is made because it is watching for a change. Which instructions it executes will then depend on what the change was. You can verify that for yourself by using a breakpoint in the code and then making a change.

A subtle point here is that Worksheet_Change macro in the target sheet - the one where the row is pasted - does not run when the row is pasted, even though that is a change to that sheet. Doesn't that contradict what I just said?

Pop quiz: To prove that you understand how a Worksheet_Change macro works, explain to us why the Worksheet_Change macro in the target sheet doesn't run when the row is pasted into that sheet. Isn't that considered a change?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
April 25, 2017 at 18:20:57
Yes it does contradict what you said but i think that what you're saying is just trying to be misleading, now bare in mind i am a complete novice at macros, and i am trying to learn this on my own in my own free time so i can improve in my job. I think the target sheet macro does run as you stated the Worksheet_Change is always looking for a change to be made. I just believe right off the first If (if Target.Cells.Count = 1 then) the macro exits because with the paste there is more than one cell changed. I dont know i could be completely wrong but that is my take. By the way thank you for taking the time out to explain things DerbyDad it is helping in my learning process and is much appreciated. Do you teach this stuff?

Reply ↓  Report •

#5
April 25, 2017 at 19:35:27
re: "Yes it does contradict what you said but i think that what you're saying is just trying to be misleading"

No, I was not trying to be misleading. The Change event code in the target sheet does not run when the Paste operation is done, even though that is change.

The reason the code does not run is because the Change code that is currently running has disabled Events via this instruction:

Application.EnableEvents = False

Since this instruction is an Application level instruction, Events are now disabled in every sheet in every workbook that is currently open. Therefore the Paste operation (as well as any other change you might make) is "ignored" even though it is a change. All changes are ignored by VBA.

Once the Paste is complete, the code that did the pasting then executes this:

Application.EnableEvents = True

which enables events in all sheets so that the code will fire with the next change.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#6
April 25, 2017 at 21:21:44
Very cool, and apologies for the misleading comment just thought you were trying to throw me a curve ball. Again, explanation is very detailed and greatly appreciated thanks again for taking the time to explain things, you should really teach a course in this stuff.

Reply ↓  Report •

Ask Question