Cell update to run macro

[Guest]

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

 

[Guest]

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