VBA code causes excel to crash when saved and/or (file has external links)

[jonfen100]

Hey guys!

Thank you for the immense help you have already given by being so active.

I have an excel file with some VBA code (fairly new to the (for me) more advanced stuff).

The code is working fine, and was working when I was creating it. Now when I make a change in the file, save, close, reopen, and enable content it crashes. If I don't enable content and try to open the VBA Editor it crashes.

The file has external connections to a website, and I'm using ActiveX controls. I have tried disabling connections, saving in different formats, compiled the code.

I have determined that the error is cause by the next block of code.

Private Sub Worksheet_Calculate()
Static oldval
If Range("E9").Value <> oldval Then
    oldval = Range("E9").Value


If Range("E9").Value <> PrevVal Then


If Sheets("Mail Template").Range("E9").Value = "UK" Then


Sheets("Main Texts").Range("A6:B36").Copy


Sheets("Mail Template").Range("A7").PasteSpecial
Range("A33:B35").ClearContents
PrevVal = Range("E9").Value


Range("B2").Select
    Range("B2").ClearContents
    SendKeys "{F2}"
    SendKeys "{NumLock}"


End If


If Sheets("Mail Template").Range("E9").Value = "DK" Then


Sheets("Main Texts").Range("D6:E36").Copy


Sheets("Mail Template").Activate


Range("A7").PasteSpecial
PrevVal = Range("E9").Value


Range("B2").Select
    Range("B2").ClearContents
    SendKeys "{F2}"
    SendKeys "{NumLock}"
End If
If Sheets("Mail Template").Range("E9").Value = "SE" Then


Sheets("Main Texts").Range("G6:H36").Copy


Sheets("Mail Template").Range("A7").PasteSpecial
PrevVal = Range("E9").Value


Range("B2").Select
    Range("B2").ClearContents
    SendKeys "{F2}"
    SendKeys "{NumLock}"
End If
If Sheets("Mail Template").Range("E9").Value = "US" Then


Sheets("Main Texts").Range("M6:N30").Copy


Sheets("Mail Template").Range("A7").PasteSpecial
Range("A32:B35").ClearContents


Range("B2").Select
    Range("B2").ClearContents
    SendKeys "{F2}"
    SendKeys "{NumLock}"
End If
If Sheets("Mail Template").Range("E9").Value = "EU" Then


Sheets("Main Texts").Range("K6:L30").Copy


Sheets("Mail Template").Range("A7").PasteSpecial
Range("A32:B35").ClearContents
PrevVal = Range("E9").Value


Range("B2").Select
    Range("B2").ClearContents
    SendKeys "{F2}"
    SendKeys "{NumLock}"
End If
If Sheets("Mail Template").Range("E9").Value = "NO" Then


Sheets("Main Texts").Range("I6:J36").Copy


Sheets("Mail Template").Activate


Range("A7").PasteSpecial
PrevVal = Range("E9").Value


Range("B2").Select
    Range("B2").ClearContents
    SendKeys "{F2}"
    SendKeys "{NumLock}"
End If
End If
End If
End Sub

Hope someone can help. I have a feeling the problem is with the first few lines of code.
Now I'll og and try to help others with other Excel problems in this forum

/jonfen100