Automate a macro based on the value of a cell? (call Sub)
 

Here it is:

1. I have a muti-sheet workbook

2. I have buttons on several sheets that call Subroutines
that assign default values -- they work fine.

The problem: How can I run one of those macros/Sub
automatically when some cell goes, say TRUE to FALSE?

Specifically, from Sheet1 I want to run the code attached
to Button2 on Sheet2.

Help,

Doug

Automate a macro based on the value of a cell? (call Sub)
 

Hi
you need an event procedure for this. E.g. use the worksheet change
event for this. Put the following code in your worksheet module (tests
cell A1):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .value then
Application.EnableEvents = False
' enter your code or call a different sub
end if
End With

CleanUp:
Application.EnableEvents = True
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Douglas wrote:
Here it is:

1. I have a muti-sheet workbook

2. I have buttons on several sheets that call Subroutines
that assign default values -- they work fine.

The problem: How can I run one of those macros/Sub
automatically when some cell goes, say TRUE to FALSE?

Specifically, from Sheet1 I want to run the code attached
to Button2 on Sheet2.

Help,

Doug

Automate a macro based on the value of a cell? (call Sub)
 

Unless the change from True to False is done by DDE or through a user making
a change in a cell, you probably want to use the calculate event. If you
don't want the macro to run everytime a calculate occurs, you would have to
add code to maintain what the last state of the cell was to see it it was
changed in this calculate.

--
Regards,
Tom Ogilvy

"Frank Kabel" wrote in message
...
Hi
you need an event procedure for this. E.g. use the worksheet change
event for this. Put the following code in your worksheet module (tests
cell A1):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .value then
Application.EnableEvents = False
' enter your code or call a different sub
end if
End With

CleanUp:
Application.EnableEvents = True
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Douglas wrote:
Here it is:

1. I have a muti-sheet workbook

2. I have buttons on several sheets that call Subroutines
that assign default values -- they work fine.

The problem: How can I run one of those macros/Sub
automatically when some cell goes, say TRUE to FALSE?

Specifically, from Sheet1 I want to run the code attached
to Button2 on Sheet2.

Help,

Doug

Frank & Tom -- Thanks!
 

You've given me hope -- I'll try it later

Doug


-----Original Message-----
Hi
you need an event procedure for this. E.g. use the
worksheet change
event for this. Put the following code in your worksheet
module (tests
cell A1):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
On Error GoTo CleanUp:
With Target
If .value then
Application.EnableEvents = False
' enter your code or call a different sub
end if
End With

CleanUp:
Application.EnableEvents = True
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

Douglas wrote:
Here it is:

1. I have a muti-sheet workbook

2. I have buttons on several sheets that call
Subroutines
that assign default values -- they work fine.

The problem: How can I run one of those macros/Sub
automatically when some cell goes, say TRUE to FALSE?

Specifically, from Sheet1 I want to run the code
attached
to Button2 on Sheet2.

Help,

Doug

.