VBA query

[Wyndon]

Using the info in other threads, I've set up a worksheet calculate event to clear the status cells when the market is suspended. At the same time I also want to clear certain trigger cells that I use in a separate worksheet. I've tried using the same code (getting the "Suspended" flag to also appear on the 2nd worksheet) but everything seems to freeze when I attempt to use it. This is without being connected to BetAngel. What am I doing wrong?

[LinusP]

Need to see your code, it sounds like it's looping.

[Wyndon]

SHEET 1 (BetAngel)
Private Sub Worksheet_Calculate()
Dim i As Integer
If Range("H1").Value = "Suspended" Then
For i = 9 To 67 Step 2
Range("O" & i).ClearContents
Next i
End If
End Sub

SHEET 2 (H1 = ‘BetAngel’!H2 If “Suspended”, then I want to clear range B9:B67)
Private Sub Worksheet_Calculate()
Dim i As Integer
If Range("H1").Value = "Suspended" Then
For i = 9 To 67 Step 2
Range("B" & i).ClearContents
Next i
End If
End Sub

[LinusP]

Quickly looking at that I guess it is just going to just loop you need to add a bit of code to stop it if the cells are already clear.

[Wyndon]

Thanks Linus, I'll give it a go.

[nigelk]

I don't think your code is freezing, it's just not being called by the event.

See here for a quick overview of the main Excel events.

http://www.youtube.com/watch?v=B-y3KJdLhm4

Notice that the calculation event is only called when he preceeds his entry with an equals sign.

If you a the number 2 in a cell, that's a change event.

If you put =2 in a cell, that's a calculate event and also a change event.