VBA Worksheet Change Script

[smlevron]

I am trying to creat a Worksheet Change Script (open to other ideas) that will compare a value from sheet 1 cell b9 to sheet 7 cells H1:H10, and if the value in cell b9 is equal to one of the values in cells h1:h10, it will pop up a message to notify the user... if possible i want the message to read a text value from sheet 7 cell I1:I10.

Thanks
Shawn

 

[Smitty]

Welcome to the Board!

This might get you started:

<font face=Calibri><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:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> MatchIt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("B9")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> oops<br>                MatchIt = Application.WorksheetFunction.Match(Target.Value, Sheets("Sheet7").Range("H1:H10"), 0)<br>                <br>                MsgBox "You have a matching value"<br>            <br>oops:<br>    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[smlevron]

Nope... it doesn't seem that the script is even trying to run.... I am about 10 years out of practice, so you might have to dumb it down for me.

 

[smlevron]

This is the beginning of what I had attempted, it just continues to compare the cells then ends....


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B9").Value = Sheets("References").Range("G1").Value Then
MsgBox(vbOK, vbOKOnly, RED_FLAG_RAILCAR, , Sheets("References").Range("H1")) = vbOK
Else
If Range("B9").Value = Sheets("References").Range("G2").Value Then
MsgBox(vbOK, vbOKOnly, RED_FLAG_RAILCAR, , Sheets("References").Range("H2")) = vbOK
Else
If Range("B9").Value = Sheets("References").Range("G3").Value Then
MsgBox(vbOK, vbOKOnly, RED_FLAG_RAILCAR, , Sheets("References").Range("H3")) = vbOK
Else

 

[Smitty]

If all you're trying to do is check for a matching value, then there's no need to evaluate each cell, let the function do it for you.

If you want to see if the code I posted is working, comment out Exit Sub. Match will throw an error if it doesn't find anything. You can also add "Debug.Print Matchit" after the function call to see if it returns a value if true.

 

[smlevron]

OK, commented it out, and no error.... I am correct in assuming that this script should execute as soon as i make a change to cell b9 correct? excel isn't running the script; now what?

 

[Smitty]

Yup, any time you change B9.

Put a message box in before the Match function. If you don't get it when you change B9, then it could be several different things: the code could be in the wrong module, or EnableEvents could be off...

 

[smlevron]

I figured out why it was running (user error)
I finally got it to work, I did it the long winded way, but i guess the outcome is all that matters....thanks for the help

This is the code I ended up using:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim alert1 As Variant
Dim alert2 As Variant
Dim alert3 As Variant
Dim alert4 As Variant
Dim alert5 As Variant
Dim alert6 As Variant
Dim alert7 As Variant
Dim alert8 As Variant
Dim alert9 As Variant
Dim alert10 As Variant
Dim flag1 As Variant
Dim flag2 As Variant
Dim flag3 As Variant
Dim flag4 As Variant
Dim flag5 As Variant
Dim flag6 As Variant
Dim flag7 As Variant
Dim flag8 As Variant
Dim flag9 As Variant
Dim flag10 As Variant

Set rng = Target.Parent.Range("B9")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
On Error GoTo oops
flag1 = Sheets("References").Range("G1").Value
flag2 = Sheets("References").Range("G2").Value
flag3 = Sheets("References").Range("G3").Value
flag4 = Sheets("References").Range("G4").Value
flag5 = Sheets("References").Range("G5").Value
flag6 = Sheets("References").Range("G6").Value
flag7 = Sheets("References").Range("G7").Value
flag8 = Sheets("References").Range("G8").Value
flag9 = Sheets("References").Range("G9").Value
flag10 = Sheets("References").Range("G10").Value
alert1 = Sheets("References").Range("H1").Value
alert2 = Sheets("References").Range("H2").Value
alert3 = Sheets("References").Range("H3").Value
alert4 = Sheets("References").Range("H4").Value
alert5 = Sheets("References").Range("H5").Value
alert6 = Sheets("References").Range("H6").Value
alert7 = Sheets("References").Range("H7").Value
alert8 = Sheets("References").Range("H8").Value
alert9 = Sheets("References").Range("H9").Value
alert10 = Sheets("References").Range("H10").Value
If rng = flag1 Then
MsgBox alert1, vbOKOnly, "RED_FLAG_RAILCAR"
Else
If rng = flag2 Then
MsgBox alert2, vbOKOnly, "RED_FLAG_RAILCAR"
Else
If rng = flag3 Then
MsgBox alert3, vbOKOnly, "RED_FLAG_RAILCAR"
Else
If rng = flag4 Then
MsgBox alert4, vbOKOnly, "RED_FLAG_RAILCAR"
Else
If rng = flag5 Then
MsgBox alert5, vbOKOnly, "RED_FLAG_RAILCAR"
Else
If rng = flag6 Then
MsgBox alert6, vbOKOnly, "RED_FLAG_RAILCAR"
Else
If rng = flag7 Then
MsgBox alert7, vbOKOnly, "RED_FLAG_RAILCAR"
Else
If rng = flag8 Then
MsgBox alert8, vbOKOnly, "RED_FLAG_RAILCAR"
Else
If rng = flag9 Then
MsgBox alert9, vbOKOnly, "RED_FLAG_RAILCAR"
Else
If rng = flag10 Then
MsgBox alert10, vbOKOnly, "RED_FLAG_RAILCAR"
Else
Exit Sub
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

oops:
Exit Sub

End Sub

 

[shg]

Another way:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i           As Long
 
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$B$9" Then Exit Sub

    On Error GoTo Oops
    With Worksheets("References")
        i = Application.Match(Target.Value, .Range("G1:G10").Value)
        MsgBox .Cells(i, "H"), vbOKOnly, "RED_FLAG_RAILCAR"
    End With
Oops:
End Sub

 

[Smitty]

Youch!

Well, if you're gonna' do that, you might as well make it a bit more efficient:

<font face=Calibri><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> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> alert(10) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> flag(10) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("B9")<br>    <SPAN style="color:#007F00">' Only look at single cell changes</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#007F00">' Only look at that range</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#007F00">' Action if Condition(s) are met (do your thing here...)</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 10<br>            flag(i) = Sheets("References").Range("G" & i).Value<br>            alert(i) = Sheets("References").Range("H" & i).Value<br>            <SPAN style="color:#00007F">If</SPAN> rng = flag(i) <SPAN style="color:#00007F">Then</SPAN><br>                MsgBox alert(i), vbOKOnly, "RED_FLAG_RAILCAR"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>