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

 

[mhessnm]

Hi Akuini, thanks for the code, I'll try it out this week! I'll report back.

 

[mhessnm]

Hey Akuini, I was able to modify your code to work in my workbook. Thank you so much!

I have a question if you would be willing to indulge me. I understand what these lines do, but I'm not quite sure how they do it. Can you step through them for me?

[FONT=&quot] For Each z In c
If Len(z) = 0 Then tx = tx & "," & z.Address(0, 0)
Next

MsgBox "You need to fill in cells: " & Right(tx, Len(tx) - 1)
Cancel = True[/FONT]

 

[Akuini]

Ok, here:

[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, tx [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]

[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 [I][COLOR=seagreen]'loop through each cell in c[/COLOR][/I]
        
            [I][COLOR=seagreen]'If Len(z) = 0  - means if the cell is empty. If a cell is empty then the length is 0[/COLOR][/I]
            [I][COLOR=seagreen]'tx = tx & "," & z.Address(0, 0) - populating the empty cells address to tx, each separated by comma[/COLOR][/I]
            [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]
        
        
        [I][COLOR=seagreen]'Right(tx, Len(tx) - 1) - remove the first character in tx, which is a comma[/COLOR][/I]
        MsgBox [COLOR=brown]"You need to fill in cells: "[/COLOR] & Right(tx, Len(tx) - [COLOR=crimson]1[/COLOR])
        
        Cancel = [COLOR=Royalblue]True[/COLOR]  [I][COLOR=seagreen]' cancelling saving process[/COLOR][/I]
    
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[mhessnm]

Thank you! I think I was confused by the z.Address(0,0). I appreciate your help and your explanation - thank you!

 

[Akuini]

z.Address(0,0) - means you get the address without the $ sign, like A1 not $A$1
So does the code work according to your needs?

 

[mhessnm]

Hi Akuini, yes, it works great! Thank you for the code - I am implementing it today for our staff. You were really helpful, and I appreciate your time and guidance on this!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.