Launching Macro when Cell Changes

[alexdetrano]

Hello there (I'm back with the beginner questions)

Is there a way I could launch macros according to the value of a
cell? Being that I want a certain macro to launch if p82 is equal to
1, and a different to launch if p82 is equal to 2, etc... Visual Basic
is so frusturating (I wish it were C !! ;-) )

 

[Guest]

maybe something like this. Right click on the tab, then select view code.
Then paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$P$82" Then
Select Case Target.Value
Case 1
MsgBox "call a macro here"
Case 2
MsgBox "call a different macro here"
End Select
End If
End Sub

 

[alexdetrano]

maybe something like this. Right click on the tab, then select view code.
Then paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$P$82" Then
Select Case Target.Value
Case 1
MsgBox "call a macro here"
Case 2
MsgBox "call a different macro here"
End Select
End If
End Sub

That only works if I change it myself...is there a way it could launch
the macro when the cell link changes (p82) ? I think it has something
to do with the worksheet_calculate but I can't get it. thank you for
the quick response !

 

[Guest]

Maybe you can check the value of P82 everytime the worksheet calculates...
something like this:

Private Sub Worksheet_Calculate()
Static vOldValue As Variant

With Range("P82")
If .Value <> vOldValue Then
vOldValue = .Value

Select Case .Value
Case 1
MsgBox "call a macro here"
Case 2
MsgBox "call a different macro here"
End Select
End If
End With

End Sub

 

[Norman Jones]

Hi Alexdetr,

'----------------
That only works if I change it myself...is there a way it could launch
the macro when the cell link changes (p82) ? I think it has something
to do with the worksheet_calculate but I can't get it. thank you for
the quick response !
'----------------

Try something like:
'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim Rng2 As Range
Dim Rng3 As Range

Set Rng = Me.Range("A1") '<<==== CHANGE

On Error Resume Next
Set Rng2 = Rng.Precedents
On Error GoTo 0

If Not Rng2 Is Nothing Then
Set Rng2 = Intersect(Rng2, Target)
End If

If Not Rng2 Is Nothing Then
Select Case Rng.Value
Case 1: Call Macro1
Case 2: Call Macro2
Case 3: Call Macro3
End Select
End If
End Sub
'<<=============

 

[Norman Jones]

Hi Alexdetr,

Set Rng = Me.Range("A1") '<<==== CHANGE

I used A1 in my test file but you should change this to P82.