Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting a change in a cell
Hi all,
I'm fairly new to VBA programming. I'm using Excel 2003. I need to know when a particular cell (it's actually a single cell "range" called MYRANGE) has changed. When I detect the change, I need to see if the new numeric value in that cell is not zero; if it's non-zero then I need to activate some code. Unfortunately that cell is changed via a formula - so using Worksheet_Change is apparently not going to work. That is, Worksheet_Change doesn't get triggered for cells of this type. I tried using .OnTimer to repeatedly check the value of that cell, but I couldn't get it to work very well. It seems like there has got to be an easy way to trigger some code to activate when a cell has changed (even for a cell that changes via a formula). Thanks for any help! -Brad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting a change in a cell
Brad,
You can still use the worksheet change event but you must use the cell that changes your cell to zero. For example this on worksheet2 uses the change event for cell A1 but then goes on to look at Sheet 1 a1 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then If Sheets("Sheet1").Range("A1").Value = 0 Then MsgBox "A1 on sheet 1 just changed to " & Sheets("Sheet1").Range("A1").Value ' do something End If End If End Sub Mike "Brad Stone" wrote: Hi all, I'm fairly new to VBA programming. I'm using Excel 2003. I need to know when a particular cell (it's actually a single cell "range" called MYRANGE) has changed. When I detect the change, I need to see if the new numeric value in that cell is not zero; if it's non-zero then I need to activate some code. Unfortunately that cell is changed via a formula - so using Worksheet_Change is apparently not going to work. That is, Worksheet_Change doesn't get triggered for cells of this type. I tried using .OnTimer to repeatedly check the value of that cell, but I couldn't get it to work very well. It seems like there has got to be an easy way to trigger some code to activate when a cell has changed (even for a cell that changes via a formula). Thanks for any help! -Brad |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting a change in a cell
Unfortunately the cell I'm interested in is changed by the summation of dozens
of other cells (i.e. a formula is used). And those cells in turn are changed by other formulas. And since Worksheet_Change doesn't work on cells with formulas, it's very difficult to figure out how to use Worksheet_Change to figure out if the cell I'm interested is currently a zero or not. Seems like Excel should provide a way to sense if a cell has changed, regardless if that cell was modified manuall or via a formula. If I could sense that my cell has changed then I could see if it's a zero or not and execute code accordingly. Worksheet_Change would be perfect if it worked for cells that change via formulas. Thanks, Brad Mike H wrote: Brad, You can still use the worksheet change event but you must use the cell that changes your cell to zero. For example this on worksheet2 uses the change event for cell A1 but then goes on to look at Sheet 1 a1 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then If Sheets("Sheet1").Range("A1").Value = 0 Then MsgBox "A1 on sheet 1 just changed to " & Sheets("Sheet1").Range("A1").Value ' do something End If End If End Sub Mike "Brad Stone" wrote: Hi all, I'm fairly new to VBA programming. I'm using Excel 2003. I need to know when a particular cell (it's actually a single cell "range" called MYRANGE) has changed. When I detect the change, I need to see if the new numeric value in that cell is not zero; if it's non-zero then I need to activate some code. Unfortunately that cell is changed via a formula - so using Worksheet_Change is apparently not going to work. That is, Worksheet_Change doesn't get triggered for cells of this type. I tried using .OnTimer to repeatedly check the value of that cell, but I couldn't get it to work very well. It seems like there has got to be an easy way to trigger some code to activate when a cell has changed (even for a cell that changes via a formula). Thanks for any help! -Brad |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting a change in a cell
Just to add if Brad is new to the topic - Worksheet event code goes in the
relevant sheet module. Rt-click the sheet tab - View code takes you directly into it. Look at the top-mid drop down and select worksheet, then select the desired event from the top right dropdown. Although normally it's best to qualify a range to a particular sheet, in the case of code in the sheet's object module it's best not to, unless the intention is to refer to a range on another sheet.. Range("A1") in a worksheet module will always refer to it's own sheet, not necessarily the activesheet or even a sheet in activeworkbook. Unfortunately that cell is changed via a formula - so using Worksheet_Change is apparently not going to work. That is, Worksheet_Change doesn't get triggered for cells of this type. You need to trap the change of one or more constant cells directly or indirectly referenced by the formula. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range On Error GoTo errExit Application.EnableEvents = False Set rng = Range("C1").Precedents If Not Intersect(Target, rng) Is Nothing Then If Range("C1").Value < 0 Then ' do something MsgBox Range("C1").Value End If End If errExit: Application.EnableEvents = True End Sub The above is looking for any change in cell(s) linked to C1. Big caveat, unfortuantely rng.Precedents does not return cells on other sheets, more work to do if necessary It might be worth experimenting with similar code in the sheet's Calculate event (not code in each of course) Regards, Peter T "Mike H" wrote in message ... Brad, You can still use the worksheet change event but you must use the cell that changes your cell to zero. For example this on worksheet2 uses the change event for cell A1 but then goes on to look at Sheet 1 a1 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then If Sheets("Sheet1").Range("A1").Value = 0 Then MsgBox "A1 on sheet 1 just changed to " & Sheets("Sheet1").Range("A1").Value ' do something End If End If End Sub Mike "Brad Stone" wrote: Hi all, I'm fairly new to VBA programming. I'm using Excel 2003. I need to know when a particular cell (it's actually a single cell "range" called MYRANGE) has changed. When I detect the change, I need to see if the new numeric value in that cell is not zero; if it's non-zero then I need to activate some code. Unfortunately that cell is changed via a formula - so using Worksheet_Change is apparently not going to work. That is, Worksheet_Change doesn't get triggered for cells of this type. I tried using .OnTimer to repeatedly check the value of that cell, but I couldn't get it to work very well. It seems like there has got to be an easy way to trigger some code to activate when a cell has changed (even for a cell that changes via a formula). Thanks for any help! -Brad |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting a change in a cell
This is just an outline of a possible method:
1. Capture the value of the target cell at the beginning of the procedure and store it in a variable, exmpl: compVal = Range("D10").Value. 2. Incorporate an If ... Then statement at the end of each sub routine that might change the value of cell D10, exmpl: If Range("D10").Value < compVal Then Opt = MsgBox "Range("D10") value has changed to " _ & Range("D10").Value, Run Macro?, vbYesNo, "Choose" If Opt = vbYes Then myMacro End If End If 3. If you will need to check the value again before the porcedure ends then you could just reset the compVal value within the If...Then statement. It is kind of a rustic way to do it. But it can work. "Brad Stone" wrote: Hi all, I'm fairly new to VBA programming. I'm using Excel 2003. I need to know when a particular cell (it's actually a single cell "range" called MYRANGE) has changed. When I detect the change, I need to see if the new numeric value in that cell is not zero; if it's non-zero then I need to activate some code. Unfortunately that cell is changed via a formula - so using Worksheet_Change is apparently not going to work. That is, Worksheet_Change doesn't get triggered for cells of this type. I tried using .OnTimer to repeatedly check the value of that cell, but I couldn't get it to work very well. It seems like there has got to be an easy way to trigger some code to activate when a cell has changed (even for a cell that changes via a formula). Thanks for any help! -Brad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell value - detecting change | Excel Worksheet Functions | |||
script detecting the change of active cell | Excel Programming | |||
Detecting Input Change on User Form | Excel Programming | |||
Detecting Worksheet change | Excel Programming |