Macro to run

[Stu]

~Is it possible to set up a macro to run if something is put in a
certain cell? (eg. B3 to run macro "All").

Thanks

Stu

 

[Stu]

Actually if a value changes in a certain cell

 

[Chip Pearson]

Stu,

You can use the Change event of the worksheet. In the code module for the
appropriate worksheet, use the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
If StrComp(Target.Text, "All", vbTextCompare) = 0 Then
' your code here
End If
End If
End Sub

 

[Bob Phillips]

Stu,

You can trap a change to a cell using worksheet event code

Private Sub Worksheet_Change(ByVal Target As Range)

Application .EnableEvents = False
On Error GoTo ws_exit
If (Not Intersect(Target, Range("B3")) Is Nothing) Then
'add a call to your macro here
End If

ws_exit:
Application.EnableEvents = True
End Sub

To add this, right-click on the sheet name tab, select View Coide from the
menu, and paste the code in .

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[zantor]

Hi Stu,

In the 'Worksheet Change Event', put something like this:

Dim MonitorCell As Variant

Private Sub Worksheet_Activate()
MonitorCell = Cells(1, 1)
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Cells(1, 1) <> MonitorCell Then
RunMacro
MonitorCell = Cells(1, 1)
End If
End Sub

Add a module to your project with the macro for example:

Sub RunMacro()
MsgBox "RunMacro has just been launched"
End Sub