vba code for clearing cells when race goes IP

[tedcarapook]

Hey guys

Struggling with a bit of code to automatically clear the status cells. I have created a a true/false cell for when "true" is present that the status cells should be cleared.

I currently have a basic if statement but it is not triggering, any help would be much appreciated


Sub CheckIt()
If Range("G5") = "TRUE" Then Range("O9").ClearContents
End Sub

[nigelk]

Right click on the sheet tab and select 'view code' and put this in

Code: Select all

Private Sub Worksheet_Calculate()

If Range("G5") = "TRUE" Then
Application.EnableEvents = False
Range("O9").ClearContents
Application.EnableEvents = True
End If


End Sub

Note: It's case sensitive, so "True" wouldn't be recognised.

[tedcarapook]

Thanks heaps for the reply Nigel! I've give it a go!

[ShaunWhite]

Right click on the sheet tab and select 'view code' and put this in

Code: Select all

Private Sub Worksheet_Calculate()

If Range("G5") = "TRUE" Then
Application.EnableEvents = False
Range("O9").ClearContents
Application.EnableEvents = True
End If

End Sub

Note: It's case sensitive, so "True" wouldn't be recognised.

Doesn't this get executed whenever anything changes anywhere if AutoRecalc is set in the options ?

I found that Guardian updates so many fields this method tended to slow things down. It's why I changed to checking only the fields I was interested in, once every 200ms or 1s or 5s or whatever.

[nigelk]

Yes you're quite right, with the calculation event you cannot isolate any particular cell.

[ShaunWhite]

Yes you're quite right, with the calculation event you cannot isolate any particular cell.

It's a good solution for someone starting to get to grips with it all, wasn't a critisism .