Run

S

SteveS

Look at Worksheet_Change(ByVal Target As Range).

Target will be the range where the change that tripped this code happened. Check if the range is the cell you are interested in and process accordingly.

- Steve
 
M

merjet

Is it possible to run a macro each time the value changes
in a cell?
Click to expand...

If the value change is by user input, then yes. Each worksheet
has a Worksheet_Change event procedure. Put your code there
or call the macro from there, e.g.:

Sub Worksheet_Change(Target As Range)
If Target.Address = "$A$1" then Macro1
End Sub

If the value change is by a calculation, then it isn't easy. Find a
workaround or repost.

HTH,
Merjet
 
G

Guest

is there a reason why this macro is not completing? I am
using your suggested change event to open or run my other
macro. I probably could use it in the change event macro,
but it does not work there - so I tried this. The problem
is that it stops running the second macro at the
clearcontents and does not complete placing the ones in
the column B. Also, the GETVALS macro does work alone on a
sheet that is not part of the change event macro


Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.Run ("GETVALS")
Else
End
End If
End Sub

''''then in a module''''

Sub GETVALS()
Range("B:B").Select
Selection.Clear
Range("B1").Select
Vals = Range("a1").Value
Range("B1", Cells(Vals, 2)).Value = "1"
End Sub
 
M

merjet

The problem is that GetVals produces changes on the
same worksheet that results in Worksheet_Change
starting again before it was completes its first execution.

HTH,
Merjet
 
M

merjet

Is there a workaround to allow GetVals to complete

You can try putting this at the start:

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
End With

and this at the end:

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
End With

HTH,
Merjet
 
Top