about worksheet_change

[Guest]

Dear all,

I want to start programming a sub when a value in a cell changed, the sub
will be run. How to do it ?

Thanks,
Viesta

 

[Guest]

Right click on your sheet tab, select view code and paste this into the code
window. "MyRange" is a named range that refers to the cell you want to track
(the code will adjust to the user inserting/deleting rows/columns which cause
your cell to move around - VBA doesn't track this like XL formulae do).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("MyRange")) Is Nothing Then
MsgBox "The cell changed"
Else
MsgBox "The cell did not change"
End If
End Sub

 

[Guest]

You use a worksheet change macro. The worksheet change is not in a VBA
module, but in VBA worksheet code page. Go to the excel worksheet you want
to run the code, then right click the tab on the bottom of the page (normally
sheet1). Select View Code. Then paste the code below

Sub worksheet_change(ByVal Target As Range)

test = Target

End Sub

 

[Guest]

Do I need to define the "Myrange" at advanced?

Right click on your sheet tab, select view code and paste this into the code
window. "MyRange" is a named range that refers to the cell you want to track
(the code will adjust to the user inserting/deleting rows/columns which cause
your cell to move around - VBA doesn't track this like XL formulae do).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("MyRange")) Is Nothing Then
MsgBox "The cell changed"
Else
MsgBox "The cell did not change"
End If
End Sub

 

[Guest]

worksheet change doesn't need any range restrictions. I can work in every
cell on a worksheet or any combination of cells on a worksheet. the code
below is one way of restricting the cells. Other examples of restrictions


if Target.Row = 5


if Target.Column = 6


if Target.Row = 5 and Target.Column = 3

 

[Guest]

If you want to use the named range, you would select the cell you want to
keep track of, then click in the name box in the top left corner (right above
column A) and type in your range name.

You could use

If Target.Address = "$A$5" Then
instead of
If Not Intersect(Target, Range("MyRange")) Is Nothing Then

but if a row is inserted above row 5, then your cell is now actually A6.
The macro will still be hardcoded to look for cell A5, which is not a problem
if you are certain that cell will not get moved (or are okay w/changing the
code whenever it does move).