Auto-Run
 

I want Excel to run the macro "Update" automatically whenever the value in
the cell named "Target" < 0. Can Excel do this without me having to run the
macro manually?

Thanks!

Auto-Run
 

Yes it is possible

One way

On the module sheet that is for the speadsheet you want to trigger the
macro you need a change event macro

Private Sub Worksheet_Change(ByVal Target As Range)
'add target.address testing if required
If Target.Value < 0 Then
Application.EnableEvents = False
Call MyMacro
Application.EnableEvents = True
End If
End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=522046

Auto-Run
 

Hi AP,

Assuming that your intention is that the macro should only be triggered when
the cell of interest changes from zero to another value, try:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim currCell As Range, currRng As Range
Dim oldVal As Variant
Dim newVal As Variant

Set currCell = ActiveCell
Set currRng = Selection

Set rng = Intersect(Range("myTarget"), Target)

If Not rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
newVal = rng.Value
Application.Undo
oldVal = rng.Value
Target.Value = newVal
Target.Select
currCell.Activate

If oldVal = 0 And newVal < 0 Then
Call Update
End If

End If

XIT:
Application.EnableEvents = True
End Sub
'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.

As Target is a reserved word, and to avoid possible confusion, I have
changed the named cell to myTarget


---
Regards,
Norman



"AP" wrote in message
...
I want Excel to run the macro "Update" automatically whenever the value in
the cell named "Target" < 0. Can Excel do this without me having to run
the macro manually?

Thanks!

Auto-Run
 

Tq norman

Auto-Run
 

Hi AP,

The suggested code can be simplied.

Change:

Dim currCell As Range, currRng As Range

to

Dim currCell As Range

and delete:

Set currRng = Selection

---
Regards,
Norman



"AP" wrote in message
...
Tq norman