Automatic calling of a macro

[Michelle]

Hello all!

I would like to know if there is a way to call a macro to run when a
particular cell, say A1 on Sheet1 for ease, is changed. A1 will be changed
by a formula calculation, not by a user hard coding an input. I currently
have a button to call the macro and do its thing, but I would like to
streamline the worksheet a bit. I know that I found a solution to this
problem somewhere on here previously, but I've been looking for the
particular post for a while with no luck. I'm hoping that somebody could
help me out with this. Any help would be much appreciated. Thanks in
advance.

 

[Gord Dibben]

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("A1")
If .Value <> "" Then
Call macroname
End If
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Michelle]

Thank you very much. That works like a charm. Would you mind explaining
your code to me so I understand what is going on? Not sure I see what is
going on. Thanks.

 

[Gord Dibben]

Private Sub Worksheet_Calculate()
type of event is on calculate
For more on events see Chip Pearson's site

http://www.cpearson.com/excel/Events.aspx

Check out especially the section on "Order of Events"


Gord

 

[Michelle]

Thank you for taking time to explain that to me. It was very helpful. I
hadn't really done anything with events yet.