run macro based on cell value

[Ian E]

Hi,

I would like to be able to run a macro (already created
and fully working) based on the value of a particular cell.

e.g. if cell A1=TRUE, then run the macro.

is this possible?

Thanks
Ian

 

[papou]

Hello Ian
Absolutely ;-)
The Worksheet_Change event will allow this.
Here's a sample code (Right-click on your worksheet tab, choose View code
and paste the example below and amend accordingly)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target.Value = True Then
macro1
End If
End Sub

HTH
Regards
Pascal

 

[Bernie Deitrick]

Ian,

You can use the worksheet calculate or change event. In either case, if you
only want to run the macro once, you could store a record in cell B1, for
example:

Private Sub Worksheet_Calculate()
If Range("A1").Value And Range("B1").Value <> "Already Run" Then
MyMacro
Range("B1").Value <> "Already Run"
End If
End Sub

Copy the code, right click on the sheet tab, select "View Code", and paste
the code into the window that appears.

If you want to manually enter TRUE into cell A1, the simply use the change
event, but check that the target address is "$A$1"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value Then
MyMacro
End If
End If
End Sub

HTH,
Bernie
MS Excel MVP