Running Macro when value in spreadsheet is true

[Jonas]

Is there a way to run a VBA macro each time that a value in the activ
worksheet resolves as true etc?

Maybe there is a simpler way.

A machine process uses a DDE link to update values in a worksheet. Eac
time that the value (or values) change, I want to add them to
database.

Thanks for any help.

Jona

 

[Bob Phillips]

Hi Jonas,

Here is some example code. It is worksheet code, right-click on the sheet
tab, select View Code, and paste the code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
myMacro
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
you need an event macro for this. Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
'run your macro
End If

errhandler:
Application.EnableEvents = True
End Sub

 

[pgerrish]

When you have alot of links in a spreadsheet executing code based on
events can be problematic.

You would be better of running a routine based on time.

'This should be placed in the ThisWorkbook Module
Private Sub Workbook_Open()

Call Scan

End Sub

'Regular code module
Public Sub Scan()

'Every second evaluate Links to see if they have changed
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime earliesttime:=RunWhen, procedure:="Scan", _
schedule:=True

End Sub