Validating cells left blank

Hi all,

Any suggestions on how you can make it so when a line is 'exited' a particular cell or cells hae to be filled in (i.e not left blank).

For example:

A B C

1 BOB 1 4

2 CAT 2

3 DAD 5 5

In the case above cell C2 should not be left blank and the user cannot leave the line until C2 is filled in. I say line because he might not have clicked on the cell and so therefore it cannot be validated against if he left the cell.

I hope i made myself clear.

Thanks in advance for all the great help.

Chris

I think I'd just use another column and put a warning message (nice big bold red
letters).

=if(or(counta(a1:c1)=0,counta(a1:c1)=3),"","Please enter all the values!")



chris100 wrote:
>
> Hi all,
>
> Any suggestions on how you can make it so when a line is 'exited' a
> particular cell or cells hae to be filled in (i.e not left blank).
>
> For example:
>
> A B C
>
> 1 BOB 1 4
>
> 2 CAT 2
>
> 3 DAD 5 5
>
> In the case above cell C2 should not be left blank and the user cannot
> leave the line until C2 is filled in. I say line because he might not
> have clicked on the cell and so therefore it cannot be validated
> against if he left the cell.
>
> I hope i made myself clear.
>
> Thanks in advance for all the great help.
>
> Chris
>
> --
> chris100
> ------------------------------------------------------------------------
> chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
> View this thread: http://www.excelforum.com/showthread...hreadid=489585

--

Dave Peterson

Thanks Dave,

That wasn't exactly what i was thinking of but works just as well.
What i'm thinking of now is being able to use the results from above in an event procedure of that if a cell has "Please enter all the values!" a message box pops up and it stops the macro that was currently running.

This could then be used for stopping an archiving macro or print when everything is not as it should be.

Thanks again,

Chris

My personal opinion is I wouldn't use an event that would pop up a message.

If I want to fill in column A for 10 rows, then column B, ..., I could be
dismissing dialogs all day--not counting the swearing I'd be doing!

But you could use that extra column and count to see if there are any warning
message in the archive procedure:

dim myRng as range
dim WarningMessage as string
warningmessage = "Please enter all the values!"

with worksheets("Sheet1")
set myrng = .range("C2", .cells(.rows.count,"C").end(xlup))
end with

if application.countif(myrng, warningmsg) > 0 then
msgbox "not quite yet!
exit sub
end if




chris100 wrote:
>
> Thanks Dave,
>
> That wasn't exactly what i was thinking of but works just as well.
> What i'm thinking of now is being able to use the results from above in
> an event procedure of that if a cell has "Please enter all the values!"
> a message box pops up and it stops the macro that was currently
> running.
>
> This could then be used for stopping an archiving macro or print when
> everything is not as it should be.
>
> Thanks again,
>
> Chris
>
> --
> chris100
> ------------------------------------------------------------------------
> chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
> View this thread: http://www.excelforum.com/showthread...hreadid=489585

--

Dave Peterson

Thanks Dave, I'll give that a go and let you know.

Chris