Data Validation List That Cannot Be Left Blank

[vwhite]

Hi All

I can't believe this question hasn't been asked before, yet I can't find anything for Googling or in previous posts.
If anyone would know the answer though, it's the people in this great forum

I'm trying to find out if there's a way to use data validation to reference a range of cells for a dropdown list, whilst also disallowing the user to leave the cell blank

So I have this dropdown list setup. But it's essential a report frequency is always selected.
How can I stop the user from deleting the cell contents and leaving it blank?

Picture1.png

Any advice welcome
All the best
Vicky

[maniacb]

Theoretically one could add that list of items to the Data Validation value so that only the values in the list can populate the cell.

[vwhite]

Hi Maniacb

I'm not sure I follow you?

I currently have a data validation list set to point at a named ranged called 'Frequency' on another sheet, and the option checked to show an error message if the user tries to enter anything that's not on the list

Picture4.png

But how do I stop the user leaving the cell blank?

All the best
V

[HappierThan]

I have used conditional formatting in the past to ensure that certain things get filled in to complete a sheet. One way is to have a warning which is switched on after the 'user' has commenced input. I have done this with white formatted text on the sheet which is triggered to red via the CF rule. An example would be similar to "INPUT is REQUIRED - use dropdown".

[maniacb]

HappierThan has a good solution and you are correct that blanks are allowed. Another way You can resolve this is with a worksheet-change code like this below:

Please Login or Register  to view this content.

Where K65 is the range in question and you force a value before exiting the sub. I hope it helps

[vwhite]

Ha! Excellent! That sounds like it should work. I shall give it a go in the morning. Thank you

[maniacb]

If that takes care of your original question, please select*Thread Tools*from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.