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

D

Douglas

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
 
F

Frank Kabel

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
 
T

Tom Ogilvy

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

Douglas

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


.
Click to expand...
 
Top