Excel VBA help please

It should trigger from a change with Gruss, does the cell you're logging have a formula in it ?

I think you need to keep an oldvalue copy of the cell/s you are monitoring.
Use a timer function to check every few seconds if oldvalue equals cell currentvalue. If it does not then a change has been made. Do what you require then set oldvalue to currentvalue and set timer going again.

Worksheet calculate will detect a change in formula just amend the following


Private Sub Worksheet_Calculate()

If Range("A1") 100 Then MsgBox "changed"

End Sub


or alternatively use the Private Sub Worksheet_Change(ByVal Target As Range) and log one of the cells that changes to affect the folmula, then just use an if statement to kick off any other routines

I forgot the forum strips out greater than signs



Private Sub Worksheet_Calculate()
If Range("A1") = 10 Then MsgBox "Cell A1 is 10"
End Sub

Thanks Ghetto Joe

Thanks, I just discovered Private Sub Worksheet_Calculate() but it slows the gruss updates down to a crawl.

I'd be happier to use a user-defined function triggered by an 'if' statement in a cell - but I can only semm to make these work at a module level.

Without me going into why, is there any way to put UDFs at a worksheet level? Sorry - not a 'natural' programmer

Beyond me I'm afraid, have you tried asking on the gruss forum? Gary's usually very helpful

You could use conditional formatting with the use of the =now()
If (now()is greater than whatever

Looking into it to see if I can figure it out

I know it works with date() so it should work with now()
http://en.kioskea.net/forum/affich-202505-conditional-formatting-or-if-using-dates

I'm not too sure why Private Sub Worksheet_Calculate() is slowing everything down to a crawl unless you have lots of formulas,conditional cells etc and you can always turn off the calcultions and screen updates if thats whats slowing it down


Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual



If Range("A1") = 10 Then MsgBox "Cell A1 is 10"


Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

THanks all of you.

SLowness was due to my having half a million existing formulas on the sheet which I was going to cut out. (Writing from scratch instead) now.)

Think I have it sorted now. tx again/

Ah - exactly. Calcs and conditionals. I've never looked at the screen update commands etc. so I'll give that a go too.

cheers.

Better forget my post for the time being

It's is not as simple as I thought it might be.