How do I code this...

[mrkris1982]

Column A is a validation drop down menu where you pick a name. Lets say we choose Jack Smith (CTB)....how would I go about making a message pop up whenever (CTB) is behind someones name?

I'd just like a message like:
"1) Check Access List 2) Sign key out in binder"

Thank you.

 

[mrkris1982]

Thanks a million MrKowz....that works perfectly!!!

 

[mrkris1982]

How would I also inteegrate something for J:J with a completely different message?

Right now, its only set up for A:A. I tried copying/pasting and just changing it to J, but it didnt like it, so back to the drawing board!

 

[MrKowz]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If InStr(Target.Value, "(CTB)") > 0 Then
        MsgBox "1: Check Access List " & vbLf & _
            "2: Sign out in binder"
    ElseIf InStr(Target.Value, "?cable") > 0 Then
        MsgBox "1: What are your intentions with the server cabinet? " & vbLf & _
            "2: Are you going to work with cabling?" & vbLf & _
            "3: If YES, have you contacted the Infrastructure Support Team?"
    End If
ElseIf Not Intersect(Target, Range("J:J")) Is Nothing Then
    'Your code for column J
End If
End Sub

 

[mrkris1982]

Thanks again...one last question.

Is there any way to tell it not to worry about whether it is capitalized or not?

 

[mrkris1982]

I guess 2 things...1 above and this question....notice in the code below that I have 4 different pieces of code that all prompt the same message to appear. Is there any way to combine those?

As in...if it says MID4, MID6, MID7 or MID8....to prompt a message box to appear?

ElseIf Not Intersect(Target, Range("J:J")) Is Nothing Then
    If InStr(Target.Value, "MID4") > 0 Then
        MsgBox "1: Check Access List in the Binder " & vbLf & _
            "2: Log access in the CTB Portion of the key-sign out binder"
    ElseIf InStr(Target.Value, "MID6") > 0 Then
        MsgBox "1: Check Access List in the Binder " & vbLf & _
            "2: Log access in the CTB Portion of the key-sign out binder"
    ElseIf InStr(Target.Value, "MID7") > 0 Then
        MsgBox "1: Check Access List in the Binder " & vbLf & _
            "2: Log access in the CTB Portion of the key-sign out binder"
    ElseIf InStr(Target.Value, "MID8") > 0 Then
        MsgBox "1: Check Access List in the Binder " & vbLf & _
            "2: Log access in the CTB Portion of the key-sign out binder"
            End If

 

[MrKowz]

We need to change it to a vbTextCompare:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If InStr(1, Target.Value, "(CTB)", vbTextCompare) > 0 Then
        MsgBox "1: Check Access List " & vbLf & _
            "2: Sign out in binder"
    ElseIf InStr(1, Target.Value, "?cable", vbTextCompare) > 0 Then
        MsgBox "1: What are your intentions with the server cabinet? " & vbLf & _
            "2: Are you going to work with cabling?" & vbLf & _
            "3: If YES, have you contacted the Infrastructure Support Team?"
    End If
ElseIf Not Intersect(Target, Range("J:J")) Is Nothing Then
    If InStr(1, Target.Value, "MID4", vbTextCompare) > 0 Then
        MsgBox "1: Check Access List in the Binder " & vbLf & _
            "2: Log access in the CTB Portion of the key-sign out binder"
    ElseIf InStr(1, Target.Value, "MID6", vbTextCompare) > 0 Then
        MsgBox "1: Check Access List in the Binder " & vbLf & _
            "2: Log access in the CTB Portion of the key-sign out binder"
    ElseIf InStr(1, Target.Value, "MID7", vbTextCompare) > 0 Then
        MsgBox "1: Check Access List in the Binder " & vbLf & _
            "2: Log access in the CTB Portion of the key-sign out binder"
    ElseIf InStr(1, Target.Value, "MID8", vbTextCompare) > 0 Then
        MsgBox "1: Check Access List in the Binder " & vbLf & _
            "2: Log access in the CTB Portion of the key-sign out binder"
    End If
End If
End Sub

Or for shorter code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If InStr(1, Target.Value, "(CTB)", vbTextCompare) > 0 Then
        MsgBox "1: Check Access List " & vbLf & _
            "2: Sign out in binder"
    ElseIf InStr(1, Target.Value, "?cable", vbTextCompare) > 0 Then
        MsgBox "1: What are your intentions with the server cabinet? " & vbLf & _
            "2: Are you going to work with cabling?" & vbLf & _
            "3: If YES, have you contacted the Infrastructure Support Team?"
    End If
ElseIf Not Intersect(Target, Range("J:J")) Is Nothing Then
    If UCase$(Target.Value) Like "*MID[4678]*" Then
        MsgBox "1: Check Access List in the Binder " & vbLf & _
            "2: Log access in the CTB Portion of the key-sign out binder"
    End If
End If
End Sub