Checking a cell

J

JPica

Is it possible to have VB continuously check a cell to
see if the value has changed?

JPica
 
D

David C.

That's the opposite,
when something change on the sheet (or in the workbook) you checj , then, if
it's your cell.

for any sheet, you will place your code on the ThisWorbook Excel Object's
code
(and no other module else !)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' your code
End Sub


for only one sheet, you can place the code on the shhet's code
(and no other module else)
Private Sub Worksheet_Change(ByVal Target As Range)
' your code
End Sub

IN BOTH CASES, Target is the changed range
The code could, for example be:

if not (Application.Intersect(Target, Range("A1")) Is Nothing) then
'what I want for this cell
end if
' if you try it from workbook (not sheet) think about also testing the
active sheet name !
for example add a
if (activesheet.name = Sheet1.name) and (...) then
end if
or for the range use the: sheet1.range("A1") (and not the range alone)

For more details, take a look on events' helps
(this is classical and quick to be found on internet, when looking for it)

David.
 
B

Bernie Deitrick

JPica,

You can do it using a worksheet event. Copy the code below, right click on
the worksheet tab, select 'View Code', then paste the code into the window
that appears. The sample code will monitor cell A1. Change as needed.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then MsgBox "Cell A1 changed!"
End Sub
 
B

Bernie Deitrick

JPica,

I should have also said that if the cell that you want to monitor contains a
formula, you will need to have a second cell that contains just the current
value of the cell, and then use the calculate event to compare the cell with
the formula with the value cell, as below.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
If Range("A1").Value <> Range("A2").Value Then
MsgBox "Cell A1 changed!"
Application.EnableEvents = False
Range("A2").Value = Range("A1").Value
Application.EnableEvents = True
End If
 
Top