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.

 

[RichardMGreen]

Have a look at the Worksheet_Change function in VBA. You could check what cell has been changed and then check for the last 5 charatcers in the cell being (CTB).
If they are, you could then display a messagebox.

 

[mrkris1982]

I know how to look at the code...but I really dont know how to do what you said.

Its under Sheet2(Data Center Access Log). When i double click it, this is all the code that is there right now:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CalculateFull
End Sub

Not sure if that helps any, but I am pretty much clueless coming up with any VBA on my own.

 

[RichardMGreen]

It's not the selectionchange event you want, it just the change event. In the dropdown in the top-right corner is a list of all the automatic things Excel can check for. Have a look in there for the change event.
Target will be the address of the cell you're checking for or the one that's changed.
Something like:-

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        If Right(Target.Value, 5) = "(CTB)" Then
            MsgBox "1: Check Access List " & Chr(10) & _
                "2: Sign out in binder"
        End If
    End If
End Sub

You'll need to change the cell address to whatever you need it to be.

 

[mrkris1982]

I selected Change in the dropdown and pasted your code with minor changes...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "A1:A10000" Then
        If Right(Target.Value, 5) = "(CTB)" Then
            MsgBox "1: Check Access List " & Chr(10) & _
                "2: Sign out in binder"
        End If
    End If
End Sub

I then went to the main worksheet, chose a name that has (CTB) behind their name and nothing happened.....i must be doing something wrong!

 

[MrKowz]

Target.Address is returning a single string value like $A$10 or $A$30. Since you are testing a string against a string, it won't fire properly. For a Worksheet_Change macro, I suggest using the If Not Intersect(Target, Range()) Is Nothing format:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Right(Target.Value, 5) = "(CTB)" Then
        MsgBox "1: Check Access List " & vbLf & _
            "2: Sign out in binder"
    End If
End If
End Sub

 

[mrkris1982]

BINGO!! That worked great...thank you!

This code seems like it is looking at the last 5 characters. Is there a way to check the entire string for something or does the character placements have to be specific? This works, but I have others with multiple things behind the name.

Thanks!

 

[mrkris1982]

Actually, if I want to add more of these types of statements, how do I go about doing it...I tried the following, to no avail:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Right(Target.Value, 5) = "(CTB)" Then
        MsgBox "1: Check Access List " & vbLf & _
            "2: Sign out in binder"
    End If
End If
End Sub
Private Sub Worksheet_Change2(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Right(Target.Value, 6) = "?cable" 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
End If
End Sub

 

[mrkris1982]

The problem seems to the there are two of these routines:

Private Sub Worksheet_Change2(ByVal Target As Range)

What lines do I need to delete in the second routine in order to add it as a continuation off the first routine?

 

[MrKowz]

Put them both into a single event:

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
End If
End Sub