I will put it out there that my VBA skills are nada. So I apologize if this is a basic question, as I have looked at different YouTube videos and excel forums and the examples they provided did not work when trying to write the code.

I need a VBA code for when the following words are entered into sheet 1, cells D11 and D13: New York, Kansas, and Ohio and their abbreviations NY, KS, and OH. I need 3 separate text pop-up's as each state has it's on specific instructions.

I have tried running macro, however, since it is dependent on so many variables, I could not find an appropriate macro code that encompassed what I needed.

I tried these codes below...and I as able to get code 1 to work, but when I tried to add more than just one "word" the code stopped working. And when I went back to the original code, for whatever reason it would not work again.

With code 2 I either get an error, or it does not work...

I am beyond frustrated at this point. :-(

Code 1)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D11:D13")) Is Nothing Then
        If Target = "Kansas" Then
            MsgBox "test 1."
        End If
    End If
End Sub

Code 2)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Range("D11:D13").Find(what:="Kansas", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True) Is Nothing Then
        MsgBox "test 1"
    End If
End Sub

Code 1 worked initially, but after I tried adding more works and msg box scenarios it broke for whatever reason, even deleting and rewriting it, the code will not work now.

Code 2 either gives me a End Block error.

3 Answers

1
Ron Rosenfeld On

Here's one way to test for one of many entries:

EDIT to show multiple Case statements

NOTE: If you need case-insensitive comparisons, you can add the Option Compare Text line at the beginning

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim C As Range
    If Not Intersect(Target, Range("D11:D13")) Is Nothing Then
        For Each C In Intersect(Target, Range("D11:D13"))
            Select Case C.Value2
                Case "Kansas", "KS"
                    MsgBox "test 1."
                Case "Ohio", "OH"
                     ...some code
                etc
            End Select
        Next C
    End If
End Sub
1
DarXyde On

I think you gave up just before you got it... See below a sample based on yours... i`ve used SelectionChange instead...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("D11:D13")) Is Nothing Then
        Select Case Target
            Case "New York", "NY"
                MsgBox "New York found"
            Case "Kansas", "KS"
                MsgBox "Kansas found"
            Case "Ohio", "OH"
                MsgBox "Ohio found"
            Case Else
                MsgBox "Something found, but not:" _
                            & vbCrLf _
                            & "New York, Kansas, or Ohio.", _
                        vbCritical, _
                        "Just a message title"
        End Select
    End If
End Sub

0
5202456 On

Just to say that using 'case' and 'selection change' as shown in DarXyde answer whould be the way to go, but as you are learning then this simple method may be helpful.

Just to be aware that case sensitivity will be an issue, with a bit of research you should be able to amend the answers to your question to resolve case sensitivity.

You were very close with your code 1, here is how you can amend it to work for you.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D11:D13")) Is Nothing Then

    If Target = "New York" or "NY" Then
        MsgBox "test 1."
        Exit Sub
    End If

    If Target = "Kansas" or "KS" Then
        MsgBox "test 2."
        Exit Sub
    End If

    If Target = "Ohio" or "OH" Then
        MsgBox "test 3."
        Exit Sub
    End If

End If 
End Sub

The use of exit sub is so once a match has been found the sub is ended so no further checks have to be made.