Data Validation List is Empty (2002)

Topic

New Reply

I’m using the Worksheet_Change event and I’m having trouble making the target (which happens to be a cell that is a data validation list) select the first item in the list whenever the user deletes the selected value? I’m trying to prevent empty or spaces from being entered in the cell. Or even better, to reselect the value in the list that the user deleted.

Reply | Quote

Replies

You could use Application.Undo to restore the previously selected value.

Reply | Quote

Thanks Hans! This works great if the user deletes the value. I’m having trouble handing the situation if the user blanks the cell out. The second IF statement below doesn’t meet the condition.

If I use the Data Validation Error routine, I run into the problem if the user presses the Retry button and the entire list is missing. I’d rather capture it in the macro.

Any idea what I’m doing wrong?

This seems so simple that I must be brain dead today.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("TargetDate")) Is Nothing Then
         If Target = " " Then   ‘not working
            MsgBox "You must select a date", Title:="Error Message", Buttons:=vbExclamation
            Application.Undo
        End If
         If IsEmpty(Target) Then  ‘this works when the cell is deleted 
            MsgBox "You must select a date", Title:="Error Message", Buttons:=vbExclamation
            Application.Undo
        End If
    End If
End Sub

Reply | Quote

I’d use

If Range("TargetDate") = "" Then

Note that there is no space between the quotes "".

Reply | Quote

I put no spaces between the quotes. When I blank the cell out it did not catch the condition.

Reply | Quote

What exactly do you mean by “blank the cell out”?

Reply | Quote

The user can use the space bar to erase the data in the cell / list.

Reply | Quote

That’s not a good way to erase data, but try this:

If Trim(Range(“TargetDate”)) = “” Then

Reply | Quote

That’s not a good way to erase data. I agree but unfortunately some do it. Your suggestion worked great. Thank you!

I also noticed that if I use… If Trim(TARGET) = “” Then
will also work. Which is better to use, TargetDate or the VBA supplied Target variable? Or does it not matter?

Reply | Quote

Target could be more than one cell, for example if the user selects a range of cells and presses Delete. This could cause problems if you use Target – the value of a multi-cell range is undefined. Assuming that the TargetDate range is a single cell, it’s better to use Range(“TargetDate”).

Reply | Quote

I completely forgot about target being a multi-cell range. Thanks very much for the help Hans. Have a great day.

Reply | Quote

Just a thought but when you use Data Validation there is a checkbox labelled [ ]Ignore blank.

It seems to me that you need to uncheck this setting.

zeddy

Reply | Quote

If you clear the check box “Ignore Blank”, Excel will display an error message when the user edits the cell and clears the value using backspace/delete. But the user can still clear the cell by selecting it and pressing Delete (i.e. without editing the cell).

Reply | Quote

You are so correct.

Bit of a waste of time that checkbox then

zeddy

Reply | Quote

I’ve always wondered why they took the trouble to create it…

Reply | Quote

It was half-finished.
I’m sure they meant it to work with the Delete.

The number of times you need to disallow ’empty’ entries!
I always end up having to use conditional formatting to show ‘missing’ required entries.

zeddy

Reply | Quote