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

 

[Fluff]

Hi, welcome to the board.
Untested but try

Private Sub Worksheet_Change(ByVal Target As Range)

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

End Sub

Either remove your existing code, or turn it into a comment

 

[mumps]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("B13:B19")) Is Nothing Then Exit Sub
    If Target = "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 Sub

 

[TPRSCHM]

Mumps,

when using your code the message pops up as expected but when you remove it from the sheet I get a Run-time error '13' when clicking on debug the following row is highlighted
If Target.Value = "LANE CLOSURE (MIN. OF 8 HOURS)" Then

Thanks
Jim

 

[TPRSCHM]

Fluff

Your code behaved the same way as mumps. It work to give the message but returns a error if LANE CLOSURE (MIN. OF 8 HOURS) is removed from the sheet

Thanks
Jim

 

[Scott T]

Do you really want to run the macro any time the selection changes? I think you want Worksheet_Change event like in Fluff's reply so the code runs when one of the values is actually change.

 

[mumps]

What are you removing that gives you the error?

 

[TPRSCHM]

I'm OK with it only displaying one time but do not have the VBA knowledge to figure out how to make it work.

 

[Fluff]

Neither my code not the code supplied my @mumps gives me an error, when removing the text from the sheet.
Have you changed either of the codes?
or do you have other code running on that sheet?

 

[mumps]

What do you mean by:

only displaying one time