Forum Discussion
DATA VALIDATION - DON'T ALLOW BLANK CELLS
Hello,
I'm trying to create a data validation error message, where by the message will appear when the user leaves a certain cell blank. I want the user to only be able to enter text within the required cell too.
Can anyone please help me, with what formula
9 Replies
- With permission from everyone involved, I also have three lines of VBA here.
Private Sub Worksheet_Change (ByVal Target As Range)
If Target.Address <> "d2" and Range ("d2") = "" Then msgBox "Blablabla please"
Range ("d2"). Select
end sub
You have to enter this directly in the worksheet:
ALT + F11 to insert VBA
-> right mouse button click on the table tab (eg "Sheet1")
-> Select "Show code"
-> copy in the code
-> Close VBA editor - done
However, the warning only comes when you exit the editing mode of any cell in the worksheet.
Of course you have to adjust "d2" and "blah blah" 🙂 ... or leave out the "'" sign.Hope I was able to help you.
I wish you continued success with Excel (the coolest invention since chocola... uh ... Microsoft! :-)))
And…Please keep asking here - I just taught myself Excel with the help of this forum...nearly :).
Nikolino
I know I don't know anything (Socrates)
If you want some other ideas, you can apply a conditional format to the input range so that any blanks are coloured to flag them as requiring data. If you wish to be more aggressive in the way you handle uncooperative users, then you could test the input range
= OR(ISNONTEXT(input))
and trash the calculation if it contains invalid data. For example, a workbook calculating tax liability could override the input numbers to set all tax rates to zero.
Hello Seaneboy2020,
That could be:
Please note that if a user does not attempt to enter text into the cell, it will not prompt the data validation. For example, a user must at least double-click or press F2 in a cell to prompt the data validation.
Hi PReagan
Thank you very much for your reply.
Do you know any other solutions which could help resolve this issue? As it's most likely that the user will simply ignore the required cell, which needs to be completed.
Many thanks
Unfortunately, without running a macro, Excel does not allow this. Please review the following post for a better explanation:
https://stackoverflow.com/questions/55616477/prevent-blank-cells-with-data-validation
