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.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx

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

On Tue, 15 Jan 2008 11:00:03 -0800, Michelle
<(E-Mail Removed)> wrote:

>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.

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.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx


"Gord Dibben" wrote:

> 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
>
> On Tue, 15 Jan 2008 11:00:03 -0800, Michelle
> <(E-Mail Removed)> wrote:
>
> >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.
>
>

>> Private Sub Worksheet_Calculate()
type of event is on calculate
>> On Error GoTo stoppit
self-explanatory use of On Error to do something if an error takes place
>> Application.EnableEvents = False
events are disabled to prevent infinite looping
>> With Me.Range("A1")
looks at A1 in Me, which is same as ActiveSheet or Sheets("Sheetname")
>> If .Value <> "" Then
if A1 is not blank then the macro is called
>> Call macroname
>> End If
had an If above so now end that because the macro was called successfully
>> End With
the job has been done so we release A1 and operation of this event code
>> stoppit:
>> Application.EnableEvents = True
re-enable events so they can run again on this or any other sheet or workbook
>> End Sub

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

On Tue, 15 Jan 2008 11:37:03 -0800, Michelle
<(E-Mail Removed)> wrote:

>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.
>
>--
>Cheers,
>Michelle
>"Anyone who says he can see through women is missing a lot." Groucho Marx
>
>
>"Gord Dibben" wrote:
>
>> 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

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

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx


"Gord Dibben" wrote:

> >> Private Sub Worksheet_Calculate()
> type of event is on calculate
> >> On Error GoTo stoppit
> self-explanatory use of On Error to do something if an error takes place
> >> Application.EnableEvents = False
> events are disabled to prevent infinite looping
> >> With Me.Range("A1")
> looks at A1 in Me, which is same as ActiveSheet or Sheets("Sheetname")
> >> If .Value <> "" Then
> if A1 is not blank then the macro is called
> >> Call macroname
> >> End If
> had an If above so now end that because the macro was called successfully
> >> End With
> the job has been done so we release A1 and operation of this event code
> >> stoppit:
> >> Application.EnableEvents = True
> re-enable events so they can run again on this or any other sheet or workbook
> >> End Sub
>
> 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