Blank Cells

[GillianX]

Hi,

I'm struggling still with a problem I have. It seems simple enough but as
I'm fairly new to working with spreadsheets, I can't figure it out.

You know when you complete say, an online order form and if you haven't
completed a field you get asked to fill it in? Well, I have to create
something similar. My data is to be inputted by a user, basic stuff like Your
Name, Your Company that type of thing. For a quote to be calculate though,
all fields have to have either text or numbers or an option selected from a
drop down. What I would like to happen is that a "Please ensure all fields
are completed" box appears when a field or fields are left blank. I don't
want the box to appear when the worksheet is opened, only after one or a
number of fields are not completed.

I hope this makes sense and I hope someone can help me out.

Thanks

 

[John C]

Okay, now I have a clearer idea of what you are wanting, there are a variety
of ways you can do this, I'll provide 1 for you that is fairly basic. I am
referencing cells that you used in earlier questions.
Cells that require data: B3, B5, B7, B9, D9, B11, B13, B15
Say you want your statement, you have "Please ensure all fields are
completed", I am going to use something slightly different.
We have 8 cells that need to be filled, first, I want to define a variable
that actually counts how many of the cells have data.
Insert|Name|Define
Name will be DataFill (change to what suits your needs).
This will be defined as:
=(B3<>"")+(B5<>"")+(B7<>"")+(B9<>"")+(D9<>"")+(B11<>"")+(B13<>"")+(B15<>"")
Each expression evaluates to 0 or 1, 0 if the cell is blank (or appears
blank), and 1 if there is 'something' in the cell (be it typed, or picked up
from a list or 'calculated').
Then, in the cell that you want your statement to appear (B19)???? type the
following formula:

=IF(OR(DataFill=0,DataFill=8),"","You have " & 8-DataFill & " more fields to
complete for quote")
You could still do this:
=IF(OR(DataFill=0,DataFill=8),"","Please ensure all fields are completed")

Both of these formulas will display the 'response' as soon as any field is
filled, and remove the response once all the fields are filled.

Hope this helps.

 

[GillianX]

Ok, this is going to sound really dumb but when you go to Insert/Name/Define,
where do you actually type the code

=(B3<>"")+(B5<>"")+(B7<>"")+(B9<>"")+(D9<>"")+(B11<>"")+(B13<>"")+(B15<>"")

Is it in the part that says Refers to:

Sorry, there is probably a really elementary answer.

Thanks

 

[John C]

No question is dumb, and don't ever feel that way. Every one on here had to
start sometime.

There are 3 areas in the Insert|Name|Define.
Enter the name of the variable in the first line right below Names in
Workbook:
Then, in the Refers to: field, clear out anything that might be in there,
and type in the formula that I gave.
click Add on the right hand side, and then the name should appear in the
space below that first line.

 

[Gord Dibben]

Same answer as you got this morning when you posted same question.

Maybe you didn't see my post?

If they don't click in the cell I don't see a way to pop up a message
without some event code.

Either selection_change or sheet_deactivate code in the worksheet
or..............

You could place some before_save or before_close event code in Thisworkbook
module that will pop up a message and cancel the save or close until they
fill in the cell(s).

Provide some cell references that would have to be filled.

Note: the on-line forms you mention do not pop up the message until after
you hit the "Submit" button or whatever triggers the error message.

That's what the event code does.


Gord Dibben MS Excel MVP