VBA to create a pop up message, when text is entered in a cell

[FrancisM]

The following cell provides a pop up message when NO is entered in cell H5.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H5") = "No" Then
MsgBox "If there is pink in a previously filled cell, after checking No." & vbCrLf & _
"There is Scheduling conflict." & vbCrLf & _
"Choose another time or reschedule the first veteran" & vbCrLf & _
"Remember! New career Link Visits require 1 hour.", vbCritical, "Vocational Services Database - " & ActiveSheet.Name
End If
End Sub

However when I try to expand it the these ranges H6:H10, H13:H17. I get a error message. Run time error 13. Type mismatch. I have even tried range H:H & received the same error. What am I doing wrong & how do I correct it.

 

[LouisT]

Not sure if you have another purpose for using VBA but i just tested it using "Data Validation" and it works just fine for me. Just select the range you want and every time i type NO it prompts me with the message i wrote.

thanks
LouisT

 

[Logit]

.
Try this :

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCell As Range


    For Each myCell In Range("H6:H10", "H13:H17") ': Range ("H13:H17")
            If myCell = "No" Then
                MsgBox "If there is pink in a previously filled cell, after checking No." & vbCrLf & _
                "There is Scheduling conflict." & vbCrLf & _
                "Choose another time or reschedule the first veteran" & vbCrLf & _
                "Remember! New career Link Visits require 1 hour.", vbCritical, "Vocational Services Database - " & ActiveSheet.Name
                Exit Sub
            End If
        
    Next myCell
End Sub

 

[DanteAmor]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("H5:H10, H13:H17")) Is Nothing Then
        If LCase(Target.Value) = LCase("No") Then
            MsgBox "If there is pink in a previously filled cell, after checking No." & vbCrLf & _
            "There is Scheduling conflict." & vbCrLf & _
            "Choose another time or reschedule the first veteran" & vbCrLf & _
            "Remember! New career Link Visits require 1 hour.", vbCritical, "Vocational Services Database - " & ActiveSheet.Name
        End If
    End If
End Sub

 

[Logit]

.
DanteAmor

Much better macro ! Kudos.

 

[DanteAmor]

I'm sorry, I was posting at the same time that you did it.

 

[Logit]

No problem. Yours is a much better macro !

 

[FrancisM]

Thanks for the post. I forgot about the checking to see >1. I also see that I left out the )) on one of my attempts. The only question I have is regarding If LCase(Target.Value) = LCase("No") Then. What does that piece of code do? It s probably a dumb question, please humor me

 

[DanteAmor]

No problem. Yours is a much better macro !

I appreciate your kind comments

 

[DanteAmor]

Thanks for the post. I forgot about the checking to see >1. I also see that I left out the )) on one of my attempts. The only question I have is regarding If LCase(Target.Value) = LCase("No") Then. What does that piece of code do? It s probably a dumb question, please humor me

You can capture "NO", "No", "no", "nO", LCase convert it to lowercase: "no". So always compare "no" with "no"