General Betting
There is currently 1 person viewing this thread.
08 Apr 10
18:18
Joined:
Date Joined:
27 Apr 04
I want to call some code when a cell value has been changed by an outside event (such as Gruss)
Private Sub Worksheet_Change(ByVal Target As Range) only seems to work if you enter the cell value manually.
Is there an alternative that will detect an automatic change to a cell value?
Private Sub Worksheet_Change(ByVal Target As Range) only seems to work if you enter the cell value manually.
Is there an alternative that will detect an automatic change to a cell value?
sort by:
Show
per page
Replies: 11
|
By:
It should trigger from a change with Gruss, does the cell you're logging have a formula in it ?
|
|
By:
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. |
|
By:
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 |
|
By:
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 |
|
By:
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 |
|
By:
Beyond me I'm afraid, have you tried asking on the gruss forum? Gary's usually very helpful
|
|
By:
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 |
|
By:
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 |
|
By:
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/ |
|
By:
Ah - exactly. Calcs and conditionals. I've never looked at the screen update commands etc. so I'll give that a go too.
cheers. |
|
By:
Better forget my post for the time being
It's is not as simple as I thought it might be. |