Execute Macros From Formulas (Or Cell Value)

[indi visual]

I would like to execute macros if specific cells change.

I have 25 cells I would like to apply this to.

After hours upon hours of failure, I would really just like to know if this even possible.

If "Z1" equals "1", then run macro
If value is not "1" do nothing
If "Z2" equals "2", then run macro
If value is not "2" do nothing

 

[mvptomlinson]

Perhaps something along the lines of:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Z1:Z25")) Is Nothing Then
    Select Case Target.Address
        Case "$Z$1"
            If Target.Value = 1 Then
                ' Put your code here
            End If
        Case "$Z$2"
            If Target.Value = 2 Then
                ' Put your code here
            End If
        .
        .
        .
        Case "$Z$25"
            If Target.Value = "Bob" Then
                ' Put your code here
            End If
    End Select
End If
End Sub

 

[jim may]

Paste this code into the VBA code sheet behind the sheet of concern...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$Z$1" Or Target.Address = "$Z$2" Then
If Target.Value = 1 Or Target.Value = 2 Then
MsgBox "Relace this line of code with what you want"
End If
End If
End Sub

 

[indi visual]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Z39:Z63")) Is Nothing Then
    Select Case Target.Address
        Case "$Z$39"
            If Target.Value = 1 Then
    Sheets("Financial.Reports").Select
            End If
        Case "$Z$40"
            If Target.Value = 2 Then
    Sheets("Archived.Records").Select
            End If

        Case "$Z$63"
            If Target.Value = "Bob" Then
    Sheets("Access.Portal").Select
            End If
    End Select
End If
End Sub

I placed (not the sub I intend to use), but a simple macro in to test the code and I couldn't get it to work. These cells contain formulas, but the formulas result in the number '0' or '1'. Any idea on what I'm doing wrong?

 

[indi visual]

@ Jim May

Forgive me on this one...

But does my code go in the parentheses, or does it replace everything on the line of code you provided including MsgBox?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$Z$39" Or Target.Address = "$Z$40" Then
If Target.Value = 1 Or Target.Value = 2 Then


mycodehere


End If
End If
End Sub

 

[jim may]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$Z$1" Or Target.Address = "$Z$2" Then
If Target.Value = 1 Or Target.Value = 2 Then

+++ Your code Here+++


End If
End If
End Sub

 

[Peter_SSs]

... simple macro in to test the code and I couldn't get it to work. These cells contain formulas, but the formulas result in the number '0' or '1'. Any idea on what I'm doing wrong?

The problem here is that the Worksheet_Change event is not triggered by a cell changing as the result of a formula.

It may be possible to use Worksheet_Calculate but that would not be my first choice since it can often cause the code to reun too often and impact performance.

What are the formulas in Z1 and Z2? If we can track back to some cell(s) that are changed by the user or by other code that cause Z1 and Z2 to change, then we can use the Worksheet_Change event to trap that and determine if the macros are to be run.

Just as an example, in a blank sheet, follow these steps:

1. In Z1 put the foumula
=SUM(Y1:Y5)*(X1+5)

2. Right click the sheet name tab and choose 'View Code' and paste the code below in the main right hand pane of the VB window.

3. Go back to the sheet and try entering some numbers into Y1:Y5 or X1.

Edit: Posted incomplete code - see next post for (hopefully) correct code.

 

[Peter_SSs]

Sorry, posted incomplete code. We should be checking, and only doing something when the relevant cells change, not just any cell.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> myTargets <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> myTargets = Range("Y1:Y5,X1") <SPAN style="color:#007F00">'Cells feeding into Z1</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, myTargets) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Range("Z1").Value > 100 <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "Z1 is > 100" <SPAN style="color:#007F00">' Could put code here instead</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            MsgBox "Z1 <= 100" <SPAN style="color:#007F00">' Could put code here instead</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>