Sub Execution

[VC2002]

Hi!

I want a sub to be triggered if an excel cell has a certain value.

The cell is linked via a dde link that constantly updates. I want it to run overnight too, so it rules out just using command buttons.

Any ideas?

Thanks!

 

[Molby]

Assuming the workbook is always open, insert this in the worksheet cahnge event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("a1").Value = "Your value" Then
Call macro1
End If
End Sub

 

[Fherrera]

Hi,

I'm not sure about DDE links or if this will work as you require. But events seem to pop to the top of my list of things to try:

You can read more about them at these two webpages:

http://www.cpearson.com/excel/events.htm

and

http://www.mvps.org/dmcritchie/excel/event.htm

You'd want to try something using the worksheet change event, something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Value = "Criteria here" Then
            Call TheFunctionThatNeedsToBeCalled
        End If
End Sub

Note, that this event is run after a cell value is changed... So i'm not sure if that is the case with your DDE links..

HTH

Frank

 

[Molby]

Hey great minds Frank....

 

[Fherrera]

Indeed Molby

I was just thinking that I like your code better as mine only checks the value of the cell that was just changed.

In any case, another option may be the SelectionChange event which fire's when a different cell is highlighted. But i'm not sure if this will be feasible as late at night if the sheet is open nobody will be around to select different cells or if someone is around and is selecting different cells the Macro will be run too many times... (depending on the speed of the macro)

In any case, VC2002 try the Change event and tell us how it goes.

Frank

 

[VC2002]

This website never ceases to amaze me, I barely had that post up 10 minutes and I've already got several replies!

I'll give it a go and let you know how I get on.

Thanks again!