Validating cells left blank

[chris100]

Hi all,

Any suggestions on how you can make it so when a line is 'exited'
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 canno
leave the line until C2 is filled in. I say line because he might no
have clicked on the cell and so therefore it cannot be validate
against if he left the cell.

I hope i made myself clear.

Thanks in advance for all the great help.

Chri

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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]

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

Chris