prevent blank cell

How do I prevent users of my workbook from making a cell blank? I want them
to be able to edit it so have left it unprotected but I donot want them to
be able to leave it blank. It looks as if the Data Validation command should
do it, but I can't get it to work.

Your repeat post appears as a reply to the thread in my browser. As a
result, I almost didn't read your post on the assumption that your
question had already been answered. I suspect many others did ignore
your post for the same reason.

Assuming that you know how to set up the validation condition (uncheck
"Ignore blank", set up a "Custom" validation, such as =ISNUMBER(A1)
where A1 is the cell to validate, etc.) then I presume that you are
hitting a known weakness of Data Validation, where deleting data from
the cell (without replacing with anything new) leaves the cell empty, so
the Data Validation event does not fire (presumably the faulty logic was
that there is no data to validate ...). You can roll your own patch in
VBA along the lines of
http://groups-beta.google.com/group/...945c7bccd60819

Jerry

Ken Stratford wrote:

> How do I prevent users of my workbook from making a cell blank? I want them
> to be able to edit it so have left it unprotected but I donot want them to
> be able to leave it blank. It looks as if the Data Validation command should
> do it, but I can't get it to work.

I like to use an adjacent column and put a formula like:

=if(trim(a1)<>"","","<-- Don't leave blank")

I bold it in big red letters.



Ken Stratford wrote:
>
> How do I prevent users of my workbook from making a cell blank? I want them
> to be able to edit it so have left it unprotected but I donot want them to
> be able to leave it blank. It looks as if the Data Validation command should
> do it, but I can't get it to work.

--

Dave Peterson

"Jerry W. Lewis" wrote:

> Your repeat post appears as a reply to the thread in my browser. As a
> result, I almost didn't read your post on the assumption that your
> question had already been answered. I suspect many others did ignore
> your post for the same reason.
>
> Assuming that you know how to set up the validation condition (uncheck
> "Ignore blank", set up a "Custom" validation, such as =ISNUMBER(A1)
> where A1 is the cell to validate, etc.) then I presume that you are
> hitting a known weakness of Data Validation, where deleting data from
> the cell (without replacing with anything new) leaves the cell empty, so
> the Data Validation event does not fire (presumably the faulty logic was
> that there is no data to validate ...). You can roll your own patch in
> VBA along the lines of
> http://groups-beta.google.com/group/...945c7bccd60819
>
> Jerry
>
> Ken Stratford wrote:
>
> > How do I prevent users of my workbook from making a cell blank? I want them
> > to be able to edit it so have left it unprotected but I donot want them to
> > be able to leave it blank. It looks as if the Data Validation command should
> > do it, but I can't get it to work.
>
> Thanks - a good solution - I knew there had to be a way! Ken

"Dave Peterson" wrote:

> I like to use an adjacent column and put a formula like:
>
> =if(trim(a1)<>"","","<-- Don't leave blank")
>
> I bold it in big red letters.
>
>
>
> Ken Stratford wrote:
> >
> > How do I prevent users of my workbook from making a cell blank? I want them
> > to be able to edit it so have left it unprotected but I donot want them to
> > be able to leave it blank. It looks as if the Data Validation command should
> > do it, but I can't get it to work.
>
> --
>
> Dave Peterson
> Thanks Dave - that's a useful way round it..
Ken

Ken Stratford wrote:
....

>Thanks - a good solution - I knew there had to be a way! Ken


You're welcome, glad it helped.

Jerry