Auto-Run

[AP]

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!

[mudraker]

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

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
>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!
>

[AP]

Tq norman

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> Tq norman
>