Should Cell Validation Trap Null Entries?

[Ark68]

I have a cell ("G3") that has a list cell validation associated to it. If the user deletes the default value and doesen't select another value, tabs out or clicks elsewhere, the worksheet change event triggers. Because the code relies on a value being in that cell, it errs. Does the fact that I have a cell validation limiting the user to only certain values not prevent the user from proceeding without a value in the cell?

]
If Not mbevents Then Exit Sub
     'If not Target.Address = "$G$3" as nothing Then
     If Not Intersect(Target, Range("$G$3")) Is Nothing Then
        dSDay = Target.Value
        dSMonth = Format(Month(DateValue("1 " & Range("E3") & " 2020")), "00")
        dSYear = Range("C3").Value
        dNDate = DateSerial(dSYear, dSMonth, dSDay) 'dSDay is empty ... it shouldn't have got this far?
        mbevents = False
        Unprotect
        Range("C4") = UCase(Format(dNDate, "DDDD"))
        Range("C" & dcomprow) = Format(dNDate, "dd-mmm-yy")
        mbevents = True
        Protect
     End If

 

[Joe4]

Just check to see that there is a value in there, i.e.
amend this line:

If Not Intersect(Target, Range("$G$3")) Is Nothing Then

to this:

If (Not Intersect(Target, Range("$G$3")) Is Nothing) And (Target <> "") Then

 

[Ark68]

Hi Joe. Thank you for sharing a new concept with me. I didn't realize I could do that. I can use that in so many places now.
I am however getting a "type mismatch" error when this line is encountered.

If (Not Intersect(Target, Range("$G$3")) Is Nothing) And (Target <> "") Then

 

[mikerickson]

When setting up the Validation, un-check the IgnoreBlanks in the dialog.

 

[Joe4]

Hi Joe. Thank you for sharing a new concept with me. I didn't realize I could do that. I can use that in so many places now.
I am however getting a "type mismatch" error when this line is encountered.

If (Not Intersect(Target, Range("$G$3")) Is Nothing) And (Target <> "") Then

Maybe try splitting it over two lines instead, i.e.

If Not Intersect(Target, Range("$G$3")) Is Nothing Then
    If Range("$G$3") = "" Then Exit Sub

 

[Ark68]

Hi Mike ...

When setting up the Validation, un-check the IgnoreBlanks in the dialog.

My list cell validation is created and applied with VBA as the list is dynamic. I didn't use the Excel dialogue for that. Can that setting ("ignoreBlanks") be coded?

Maybe try splitting it over two lines instead, i.e.

If Not Intersect(Target, Range("$G$3")) Is Nothing Then
    If Range("$G$3") = "" Then Exit Sub

Hi Joe. Yes ... this works. Thank you!

 

[Joe4]

You are welcome.

 

[mikerickson]

Hi Mike ...

My list cell validation is created and applied with VBA as the list is dynamic. I didn't use the Excel dialogue for that. Can that setting ("ignoreBlanks") be coded?

I got this from the Macro Recorder

With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="cat,dog,Charlie"
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

 

[Ark68]

Hi Mike ... I'm interested in your code as it provided an answer to what was to be my next question. (customizing the error message).

I have added your suggested code. If I enter an invalid value, the error is reported. However, if I delete the value in the cell and tab out, the error isn't reported. "IgnoreBlank" is indeed set to false, which if I understand correctly, means that if the cell is empty, that the validation won't be ignored and as a blank is not part of the list values, will trigger the error. This isn't happening in my case. It seems as though the blank cell is being accepted.

Then I realized that I was still using Joe4's suggestion from post 5, which was trapping that error any exiting the sub. (this works but I haven't figured out how to return the original value to the cell ... ie cancel).

So, with Joe4's code commented out, when I delete the value in the cell and tab out, I get an error as the change event for that cell triggers assuming a valid value (which of course is not ... it's blank).

Am I misunderstanding the behaviour of "ignoreBlank"? Do I still need Joe4's code? And if so, what to modify in it to return that cell's value back to the previous value.