Data validation to disallow blank entry when using drop down list

[mhessnm]

Hello,

I can't believe nobody has asked this question before but I've looked through past posts on this topic and have scoured Google and cannot find an answer.

I have data validation on a cell that uses a range on another sheet to populate the drop-down list. All I want to do is make it impossible for users to leave the cell blank. I want them to choose one of the items on the list or, if the cell is blank, get the data validation message.

I have looked at making it a custom data validation with a formula, but then I lose the drop down list. But it won't accept any formula when I use the "list" option in data validation.

I was hoping for a non-macro way and thought this would be pretty straightforward, and it may be, but I'm not seeing it. Any help will be appreciated!

Michael Hess

 

[DSCfromCFA]

G'day Michael,

I suspect that the lack of response to this seemingly simple matter is that there is a lack of understanding of just what are the consequences of not having data in the cell. You say you want to make it impossible for the user to leave it blank, but that means once they open the worrksheet they cannot leave until something is selected in that cell, whcih seems pretty draconian.

However, I suspect you are not actually intending to be that harsh, and if I am correct you need to let the forum know under what circumstances the cell is accessed and what you want to happen once it is, and what are the spreadsheet consequences of leaving the cell blank.

I suspect that if you want things to happen without the cell being selected, then you will not find a way to do that with functions alone, you will have to go VBA. At its simplest you could have a message box to pop up anytime the cell is blank.

Cheers

shane

 

[Jaafar Tribak]

I think the OP meant to not show blank entries in the DV dropdown.

If that is the case, and not using VBA, I think, you could have a helper column along side the original list and another helper cell that counts the total non-blank entries.


EXAMPLE:

1- Give the original list range the named range of : Data_Source (A2:A100)

2- Give the Helper list range the named range of: DV_List (B2:B100)

3- Enter the following array formula in the range DV_List :
{=IFERROR(INDEX(Data_Source,SMALL(IF(""<>Data_Source,ROW(Data_Source)-ROW(OFFSET(Data_Source,-1,0,1)),""),ROW()-ROW(OFFSET(DV_List,-1,0,1)))),"")}

4- Enter the following Array Formula in a Helper Cell say for example Cell C2
{=MAX((IF(DV_List="",0,ROW(DV_List)-ROW(OFFSET(DV_List,-1,0,1)))))}

5- Define a new Dynamic Named Range via the Name Manager as follows :
=OFFSET(DV_List,0,0,Sheet1!$C$2,1)

6-Define your Validation List as follows: For example in Cell E2:
Data validation => Allow List =>Source: =MyList


Here is a workbook example.

Not sure if there is a simpler way but this method works for me.

 

[mhessnm]

Hi Shane and Jaafar,

I work on a Detox campus, and staff must fill out incident reports whenever an incident occurs, i.e. clients become unruly, clients break the rules, clients have a health emergency, etc. I have built a sheet that staff use to input these incident reports. We have found that certain key information is being left blank - this information must be tracked because of certification requirements but we are finding many "non-entries" in some of these key fields - all of the key fields have drop down lists that in the cell that I installed through the data validation feature. These lists can be as simple as Yes/No, or contain a list of up to 15 selections, as in the incident report type, or where the incident occurred. My superiors want these fields filled.

We realize that we have a training issue here, and that staff need to be trained and made aware of the importance of filling in these cells, but we are also very understaffed on a 24 hour campus and do not have the leeway at the moment to provide a refresher training. So, for the interim, what we want is for these fields to be mandatory so that they must be answered.

I had hoped that it would be a simple thing to do with data validation - if the cell is left blank after the user selects it and then deselects it, a message comes up that says that the field is blank and you must pick an option from the list before you can go to the next field. There seems to be data validation where it does that if you enter text into a number cell, for instance. But I have not been able to figure out a way to do this within the context of a drop down list. They can leave the cell blank and there is not even a message or warning.

So Shane, you are right, my superiors would like me to be somewhat draconian. They have had experience with certain databases where you must fill in a field and they would like to do the same thing with this Excel solution. I had thought it possible, but when I couldn't seem to find an answer through the data validation, I posted to the board. Eventually this issue will go away, as we are in the process of acquiring a new client management system where these incident reports can be built into our new system and fields can be made mandatory. But for now, we are stuck with this Excel solution I built.

Is that more clear? I can build VBA message boxes, but that still doesn't mean that they will fill in the cell. My question - is there a way, if a cell has a drop down list, to require that a user picks something from the list using data validation?

Thanks for your consideration of this question.

Michael

 

[mikerickson]

When setting the Data Validation, un check the Ignore Blanks box.

 

[Jaafar Tribak]

You can use the Before Cloe event of the workbook to verify if all fields are filled.

Or it is probably better if you could use a userform with comboboxes for fields and then have some code in the Query Close event of the UserForm that checks if the fields are all filled.

 

[DSCfromCFA]

G'day Michael,

Thanks for the detailed response. I suspect even more strongly than previously that this can only be achieved via VBA and in that aspect I am of little help to you, however, I think that Jaafar has already given you the basis for the VBA answer in his response, that of using the Before Close event.

Good luck,

shane

 

[Akuini]

Hi, mhessnm.
I suggest a more general "trapping" to be set. What I mean is this:
When you finish filling in the sheet, is there any mandatory action to do next? maybe exit the active sheet, save the workbook, hit a command button to do something (with vba), etc?
Let's say it's 'saving the workbook', then we can write a code in 'Private Sub Workbook_BeforeSave' to check:
if any of certain cells, say A1:A10, is empty then a message box will pop up saying "You need to fill cell A1:A10....." & you can't save the workbook. So basically it will force you to fill up the mandatory cells before you can save the workbook.

Let me know if you're interested in this method.

 

[mhessnm]

Hi everyone! Thank you all for the suggestions! I really appreciate this forum.

Akuini, I think that is what I would be interested in - a notification that certain cells need to be filled before the workbook can save. That would certainly meet the need. Thank you! I may not answer a response right away as I will be traveling this weekend, but will be back on Monday.

Thanks again!

Michael

 

[Akuini]

Ok, try this:
This code must be placed in ThisWorkbook code module.
I set the range at 'Sheets("Sheet1").Range("A1:A5,C2:C4,E1")', you need to change that to suit. So:
If ALL cells in that range are empty (this is the initial condition) then you can save the workbook.
If ALL of them are filled then you can save the workbook.
If one or some of them are empty & you try to save the workbook then a message box will pop up saying "You need to fill cell: ....." & the saving process will be canceled.

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Workbook_BeforeSave([COLOR=Royalblue]ByVal[/COLOR] SaveAsUI [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Boolean[/COLOR], Cancel [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Boolean[/COLOR])
[COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range, z [COLOR=Royalblue]As[/COLOR] Range

[COLOR=Royalblue]Set[/COLOR] c = Sheets([COLOR=brown]"Sheet1"[/COLOR]).Range([COLOR=brown]"A1:A5,C2:C4,E1"[/COLOR])
    [COLOR=Royalblue]If[/COLOR] WorksheetFunction.CountA(c) = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    [I][COLOR=seagreen]'do nothing[/COLOR][/I]
    [COLOR=Royalblue]ElseIf[/COLOR] c.Cells.Count <> WorksheetFunction.CountA(c) [COLOR=Royalblue]Then[/COLOR]
    
        [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] z [COLOR=Royalblue]In[/COLOR] c
        [COLOR=Royalblue]If[/COLOR] Len(z) = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR] tx = tx & [COLOR=brown]","[/COLOR] & z.Address([COLOR=crimson]0[/COLOR], [COLOR=crimson]0[/COLOR])
        [COLOR=Royalblue]Next[/COLOR]
        
        MsgBox [COLOR=brown]"You need to fill in cells: "[/COLOR] & Right(tx, Len(tx) - [COLOR=crimson]1[/COLOR])
        Cancel = [COLOR=Royalblue]True[/COLOR]
    
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

I may not answer a response right away as I will be traveling this weekend, but will be back on Monday.

Have a nice weekend.