Cell update to run macro
 

I have a worksheet that has around 20 non-contiguous cells where we need to
enter data. Then I want to run a vba macro each time the data in any of these
cells is changed. Can someone tell me how to trigger the vba routine from a
cell update?
Thanks,
RD Wirr

Cell update to run macro
 

You could use a change event to do this eg:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim junct As Range
On Error GoTo ErrorHandler
Application.EnableEvents = False
Set myRange = Union(Range("A1"), Range("B4"), Range("C7")) 'etc
Set junct = Intersect(Target, myRange)
If Not junct Is Nothing Then
Call YourMacro
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

This is worksheet event code. Right click the sheet tab, select View Code
and paste the code in there. Change the union statement to include the
relevant cells (it will only take 30). Alternately you could manually select
the relevant cells and define them to a named range: Insert Name Define.
The code could then look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim junct As Range
On Error GoTo ErrorHandler
Application.EnableEvents = False
Set myRange = Range("NamedRange")
Set junct = Intersect(Target, myRange)
If Not junct Is Nothing Then
Call YourMacro
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

Hope this helps
Rowan

"RD Wirr" wrote:

I have a worksheet that has around 20 non-contiguous cells where we need to
enter data. Then I want to run a vba macro each time the data in any of these
cells is changed. Can someone tell me how to trigger the vba routine from a
cell update?
Thanks,
RD Wirr

Cell update to run macro
 

One way to do this is to check the address/location of the updating cel
to determine if it is the cell you want to process...

Assuming you only want to process changes in cells A5, C7, and J10, yo
can do:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
'process cell A5
ElseIf Target.Address = "$C$7" Then
'process cell C7
ElseIf (Target.Row = 10) And (Target.Column = 10) Then
'process cell J10
End If
End Sub


RD Wirr Wrote:
I have a worksheet that has around 20 non-contiguous cells where we nee
to
enter data. Then I want to run a vba macro each time the data in any o
these
cells is changed. Can someone tell me how to trigger the vba routin
from a
cell update?
Thanks,
RD Wir

--
T-®e
-----------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657
View this thread: http://www.excelforum.com/showthread.php?threadid=40067