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

  • Thread starter Thread starter Douglas
  • Start date Start date
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
 
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
 
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.
 
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...
 
Back
Top