Automatic calling of a macro
 

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

Automatic calling of a macro
 

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

Automatic calling of a macro
 

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

Automatic calling of a macro
 

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

Automatic calling of a macro
 

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