ExcelBanter - If Statement to run a macro

There are events that excel monitors that you can tie into. Worksheet events
are specific to each worksheet and the code is placed in the worksheet's
module. If you rightclick on the worksheet tab and select view code, you'll see
where this is.

Then on the righthand side window, you can use the dropdown on the top left to
choose worksheet. Then use the dropdown on the top right to see all the events
associated with the worksheet.

If the change is made by the user, then worksheet_Change seems appropriate. If
the change is made by a formula reevaluating, then worksheet_calculate would
seem appropriate.

You can find more info about these events at these sites:

Chip Pearson has some instructions on events:
http://www.cpearson.com/excel/Events.aspx

David McRitchie has some notes, too:
http://www.mvps.org/dmcritchie/excel/event.htm

======
For instance, this puts the date and time in B1 if A1 changes to 0.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'target is the range of cells being changed

'I quit if the users changes more than one cell at once
If Target.Cells.Count 1 Then Exit Sub

'I quit if the user isn't changing A1
If Intersect(Target, Me.Range("a1")) Is Nothing Then
Exit Sub
End If

'if the A1 cell is empty, I quit
If IsEmpty(Target.Value) Then
Exit Sub
End If

'if there's an error, then don't blow up
On Error GoTo ErrHandler:

If Target.Value = 0 Then
'call your macro or do the steps here
MsgBox "Hey A1 was 0"
'stop excel from looking for changes
'I don't want my change from firing the _change event
Application.EnableEvents = False
With Target.Offset(0, 1)
.NumberFormat = "mmm dd, yyyy hh:mm:ss"
.Value = Now
End With
End If

'drop down to this even if everything is ok
ErrHandler:
'tell excel to start looking for changes again
'for the next time
Application.EnableEvents = True

End Sub

Be aware that with this kind of event macro (or any macro that does anything
important), that the clipboard contents will be lost and the Edit|Undo/Redo
stack will be lost.

Arup C wrote:

Hi Dave,
Where could i find the worksheet events?

"Dave Peterson" wrote:

Formulas in cells can't do this kind of thing.

Maybe you could use a worksheet event (_change or _calculate????) that would do
what you want.

Secret Squirrel wrote:

How would I create an IF statement to fire a VBA macro? I want to be to run
the macro if a cell value = "0".

--

Dave Peterson

--

Dave Peterson