Script Crashes when run with anything but MsgBox test text.

[epjcnd]

I'm new to excel VBA but I have now tested each part and they work correctly. What I'm trying to do is to simply delete the contents of a cell when the cells formula value is changed(no selection on entering key needed). The code is located under the sheet it runs in .

Private Sub Worksheet_Calculate()
Static MyoldVal
If Range("e6").Value <> MyoldVal Then
'Range("a1") = "start"
'MsgBox "Start"
'Call Macro_1
'Range("g6").ClearContents
MsgBox "end"
MyoldVal = Range("e6").Value
End If
End Sub

It works perfectly if the only actions are MsgBox's. Anything else and it will crash, loop or not process the rest of the code. For example, it both "start" and "stop" messages are enabled it will work fine. it and the clear contents is add in it will often work once but then it will never get to the "end".

 

[johnnyL]

did you try disabling events at the beginning and reenable events at the end?

 

[epjcnd]

did you try disabling events at the beginning and reenable events at the end?

Fairly new to this stuff so not sure exactly what you mean. It would make sense that something is running several times as it would explain why it never gets to the second message. My understanding though is it should exicute the line once only all the msgbox's do behave as expected. it is just when you and in the Clearcontence or any other function. Be have the same if a call a macro too.

 

[johnnyL]

Try something like:

Private Sub Worksheet_Calculate()
'
    Application.EnableEvents = False                                                                ' Disable Events
'
' Your code you want to execute
'
    Application.EnableEvents = True                                                                 ' Enable Events
End Sub

 

[epjcnd]

Thanks that seem to have worked

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Static MyoldVal
If Range("e7").Value <> MyoldVal Then
'MsgBox "start"
Range("g6").Value = ""
'MsgBox "End"
MyoldVal = Range("e7").Value
End If
Application.EnableEvents = True
End Sub

I think the root problem was that the cell that is being cleared also triggers some other values creating odd behaviors and crashes. The cell is the output of a secondary dropbox and is cleared when the primary is changed. There is a noticeable time delay between the clearing of the cell to the clearing of the drop box. Not a major issue as the box is cleared it just takes half a second. I was looking are redoing a big piece of the spreadsheet to get around the issue so thanks again for your help.