Multiple MsgBox based on cell criteria

[=HPSF=RMP]

Hello again,
I've searched the intewebz and thought I found my answer, but i'm obviously doing something wrong. Basically, I have checklist that is filled out, and to make it easier, condenced to 3 cells (A1,B1 & C1).

If the cell is left blank, do nothing.
If something is added to the cell, I need the following pop up boxes.

If something is entered into A1 the popup box should say "Message #1"
If something is entered into B1 the popup box should say "Message #2"
If something is entered into C1 the popup box should say "Message #3"

These would go in order, so A1 would be first. If pop up appears, you would close it out, then go to B1, and so on.

Below is the code i'm using. It works with one, but obiously not mulitple based on how I put it together.

Please help!!
Thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rng As Range
   Set rng = Range("A1")
   If Not Intersect(Target, rng) Is Nothing Then
       If rng = "" Then
       End If
        MsgBox "Message #1"
          End If

   Set rng = Nothing

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rng As Range
   Set rng = Range("b1")
   If Not Intersect(Target, rng) Is Nothing Then
       If rng = "" Then
       End If
        MsgBox "Message #2"
          End If

   Set rng = Nothing

End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rng As Range
   Set rng = Range("c1")
   If Not Intersect(Target, rng) Is Nothing Then
       If rng = "" Then
       End If
        MsgBox "Message #3"
          End If

   Set rng = Nothing

End Sub

 

[=HPSF=RMP]

After playing around with the code, it looks like I stumbled on the answer.
This seems to work.
thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rng As Range
   Set rng = Range("A1")
   If Not Intersect(Target, rng) Is Nothing Then
       If rng = "" Then
       End If
        MsgBox "Message #1"
          End If

   Set rng = Nothing
   Set rng = Range("b1")
   If Not Intersect(Target, rng) Is Nothing Then
       If rng = "" Then
       End If
        MsgBox "Message #2"
          End If

   Set rng = Nothing
   Set rng = Range("c1")
   If Not Intersect(Target, rng) Is Nothing Then
       If rng = "" Then
       End If
        MsgBox "Message #3"
          End If

   Set rng = Nothing

End Sub

 

[AlphaFrog]

Or try something like this...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

   If Not IsEmpty(Target) Then
        Select Case Target.Address(0, 0)
            Case "A1": MsgBox "Message #1"
            Case "B1": MsgBox "Message #2"
            Case "C1": MsgBox "Message #3"
        End Select
    End If

End Sub

 

[=HPSF=RMP]

Awesome!
Thank you.

One other question.

What if I want to add different criteria to one cell. Say I want Case "C1" to pop up "Message #3" based on whether or not C1 = YES, instead of blank.

Hope that makes sense?

thanks

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

   If Not IsEmpty(Target) Then
        Select Case Target.Address(0, 0)
            Case "A1"
                MsgBox "Message #1"
            Case "B1"
                MsgBox "Message #2"
            Case "C1"
                If LCase(Target.Value) = "yes" then MsgBox "Message #3"
        End Select
    End If

End Sub

You could also use the Data Validation feature to do the same thing instead of this macro.

 

[=HPSF=RMP]

okay, seems to work for the word "yes", but doesnt work if I change the criteria to another name?
The name i'm using on my sheet is actually "retain" instead of "yes".

 

[AlphaFrog]

It should work for "retain" as well. I can't tell what's wrong without seeing your code. My 1st guess is you may have changed somthing else in the code.

 

[=HPSF=RMP]

okay..
It works for "retain", but not "Retain" haha
Does the LCase = Lower Case? Is that the problem, because its capitalized?

 

[=HPSF=RMP]

Dang!
I capitalized it in the code!!
Made that lower case and its fixed.

thank you for your help.

 

[Rasm]

If you use code below


Then your capital letter problem goes away

Option compare text