VBA Help

[TPRSCHM]

I'm very new to using VBA so I'm sure this is a simple fix. I want a message box to display information when a defined item is picked from the validated drop down. I thought I had this all figured out when the message box started to haunt me. If I select the validated word all other drop down boxes will then give me the message no matter what is selected. Please see the VBA below and let me know what I have done wrong.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B13") = "LANE CLOSURE (MIN. OF 8 HOURS)" Then
MsgBox "When LANE CLOSURE is utilized, reduced and prorate down one(1) laborer from CATEGORY A"
Exit Sub

ElseIf Range("B14") = "LANE CLOSURE (MIN. OF 8 HOURS)" Then
MsgBox "When LANE CLOSURE is utilized, reduced and prorate down one(1) laborer from CATEGORY A"
Exit Sub

ElseIf Range("B15") = "LANE CLOSURE (MIN. OF 8 HOURS)" Then
MsgBox "When LANE CLOSURE is utilized, reduced and prorate down one(1) laborer from CATEGORY A"
Exit Sub

ElseIf Range("B16") = "LANE CLOSURE (MIN. OF 8 HOURS)" Then
MsgBox "When LANE CLOSURE is utilized, reduced and prorate down one(1) laborer from CATEGORY A"
Exit Sub
ElseIf Range("B17") = "LANE CLOSURE (MIN. OF 8 HOURS)" Then
MsgBox "When LANE CLOSURE is utilized, reduced and prorate down one(1) laborer from CATEGORY A"
Exit Sub
ElseIf Range("B18") = "LANE CLOSURE (MIN. OF 8 HOURS)" Then
MsgBox "When LANE CLOSURE is utilized, reduced and prorate down one(1) laborer from CATEGORY A"
Exit Sub
ElseIf Range("B19") = "LANE CLOSURE (MIN. OF 8 HOURS)" Then
MsgBox "When LANE CLOSURE is utilized, reduced and prorate down one(1) laborer from CATEGORY A"
Exit Sub
End If
End Sub

Thank You
Jim

 

[Scott T]

The only way I could get Fluffs code to produce the error was to change multiple cells at a time. You could add a check to the code to see how many cells are changed and exit if it is more then one.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B13:B19")) Is Nothing Then
    If Target.Value = "LANE CLOSURE (MIN. OF 8 HOURS)" Then
        MsgBox "When LANE CLOSURE is utilized, reduced and prorate down one(1) laborer from CATEGORY A"
    End If
End If

 

[TPRSCHM]

Scott T,

Thank you your code worked like a charm. I would also like to thank all that assisted on this problem. I have a lot to learn about VBA and I see that Mr Excel is a great place to get excellent advice

Jim