Error message boxes....

[Dermot]

Explained in simple terms.
I have created a calculator.
It consists of 6 input cell in which I enter values manually.
And many calculated cell with formulae in them which use the values entered
in the input cells.
How can I generate and error message if someone overlooks entering values in
one of the cells.....a text message that would indicate the missing cell or
cells that data has yet to be entered into?
Not sure the best way to approach this, any advise would be appreciated.

[Dave O]

Possibly the easiest way to do this is to apply conditional formatting
that colors the cell red if its value is blank. You could write code
that would check those cells for values, but it is not as
straightforward and would require some type of event (leaving a
particular cell, updating a cell, etc.

[Dermot]

Thanks Dave for the reply I appreciate you suggestion.

It's not really colour conditional formatting that I want to understand.
I know how to do this from a previous posting.

I have tried to simplify what I am trying to do as decribed in the first
posting.
It would be better if I could incorporate a "Message Box" some how to
indicate which data input data is missing.
Perhaps my seeing an empy cell as a "Null" But Out of the six required cells
in my example....I am not sure the best way to identify the missing one? or
even ones (plural)!

I would appreciate if anyone else has any suggestion, non code or coded
solution or even both or even a downloadable example....that is if what I am
trying to do is possible?


"Dave O" wrote:

> Possibly the easiest way to do this is to apply conditional formatting
> that colors the cell red if its value is blank. You could write code
> that would check those cells for values, but it is not as
> straightforward and would require some type of event (leaving a
> particular cell, updating a cell, etc.
>
>

[Dave O]

It is definitely do-able, but fitting it into the structure of your
spreadsheet is the question mark for me. You can attach code to a
sprdsht in such a way that a particular event (such as landing on a
cell or within a range of cells, entering data to a cell, or changing
the value of a cell) triggers code to run. What I mean by "fitting it
into the structure of your sheet" is this: since you have 6 cells that
must be filled, we could say "if the user enters data into a required
cell, show a message box that tells the user the other 5 cells must
also be entered". If we did that, the user would click OK to close the
message box, but then they would see that same message 5 more times
(for the remaining 5 boxes) and would get severely torqued at having to
spend their working day closing all those boxes.

So to set this up properly, we'd need to examine the flow of the
spreadsheet or the entry of data to the spreadsheet and determine at
what point we can perform data validation on those 6 cells. For
instance: is the user required to save the sprdsht? Print? Click on a
button, or run another macro? Flip to a different sheet in the
workbook? Each of those constitutes a workbook event, and we can write
code to perform validation before any of those events.

[Dermot]

Hi Dave
The 6 data input cells are all entered in one column with calculated fields
in between each. They are entered in a top to bottom order. They are either
all required for a full calculation or only three under certain
circumstances. They are entered in worksheet 1 and are referenced along with
results to a further 4
It did enter my mind exactly what you pointed out about several annoying
message boxes....I too am not sure about the best choice of event to trigger
the possible code. Cells ABC are common to both calculations and cells DEF
are only involved under more involved circumstances.....
Calculation 1 would be based on input ABC and Calculation 2 ABCDEF if you
know what I mean? (Assuming calculated cells inbetween are ignored to
simplify explanation)

I am not sure what event would trigger the calculation....it entered my mind
that maybe a "Calculate" command button could be added...(but I don't know
how to incorporate this)......when clicked if any of the 6 cells is a
Null....a text prompt would appear with a message box to enter the relevant
missing input(S) by name.

I realise this all sounds a little elaborate.....but the 6 cells could be
any larger number in reality and I have only used this figure for
simplification.

If I had a working sample ....even of something similar....that I could
examine...then I may be able to work out how to achieve my objective.

I wondered if the code could incorporate some form of logic.....
cells (A AND B AND C) OR(A And B......And F)......
But then how would it be possble to identify any combination of missing
cells by name in the error message?
Best Wishes
Dermot



"Dave O" wrote:

> It is definitely do-able, but fitting it into the structure of your
> spreadsheet is the question mark for me. You can attach code to a
> sprdsht in such a way that a particular event (such as landing on a
> cell or within a range of cells, entering data to a cell, or changing
> the value of a cell) triggers code to run. What I mean by "fitting it
> into the structure of your sheet" is this: since you have 6 cells that
> must be filled, we could say "if the user enters data into a required
> cell, show a message box that tells the user the other 5 cells must
> also be entered". If we did that, the user would click OK to close the
> message box, but then they would see that same message 5 more times
> (for the remaining 5 boxes) and would get severely torqued at having to
> spend their working day closing all those boxes.
>
> So to set this up properly, we'd need to examine the flow of the
> spreadsheet or the entry of data to the spreadsheet and determine at
> what point we can perform data validation on those 6 cells. For
> instance: is the user required to save the sprdsht? Print? Click on a
> button, or run another macro? Flip to a different sheet in the
> workbook? Each of those constitutes a workbook event, and we can write
> code to perform validation before any of those events.
>
>

[Dave O]

Good morning, Dermot-
I didn't mention the *easiest* event: orchestrate one. Advise your
users they must press the "Validate" button at a certain point in data
entry. Adding a button to a sprdsht is easy, and just as easy to
assign code to that button.

If that's amenable to your process, it's just a matter of stepping
through the required logic. Your post says
>>entered in a top to bottom order. They are either all required for a full calculation or only three under certain circumstances
.... and
>>if the code could incorporate some form of logic..cells (A AND B AND C) OR(A And B......And F)

This is absolutely do-able: if you can determine how many circumstances
are possible and what cells are required in each given circumstance,
then we can write the code to accommodate it.

How do you feel about the "Validate" button?

And just for grins, what is your continent/time zone? I am in North
America, Eastern Daylight Time.

Dave O

[Dermot]

Hi Dave
A validate button sounds fine to me.
The input cells are: C2, C4, C8, C9, C10 , C11 and C21.
I have broken down the calculations into 5 possible senarios.....using cells
as below.
Calculation 1
C2= Must have
C4= Must have
C21= Must have

Calculation 2
C2= Must have
C4= Must Have
C8= Must Have
C11= Must Have
C21= Must Have

Calculation 3
C2, C4, C8, C9, C11, C21 all must have

Calculation 4
C2, C4, C8,C10,C11, C21.

Calculation 5
C2, C4, C8, C9, C10, C11, C21,
Cells C9 and C10 are optional...but must be filled if there is data present.
It would be nice to find out a way to confirm if the data were
entered.......by some form of text message.

Note
Cell C24 shows a "#Value!" error if cell C24 data is not entered.

Dave I am in the UK, London GMT time.
How do I create the validate button within excel?

Hope you can help





"Dave O" wrote:

> Good morning, Dermot-
> I didn't mention the *easiest* event: orchestrate one. Advise your
> users they must press the "Validate" button at a certain point in data
> entry. Adding a button to a sprdsht is easy, and just as easy to
> assign code to that button.
>
> If that's amenable to your process, it's just a matter of stepping
> through the required logic. Your post says
> >>entered in a top to bottom order. They are either all required for a full calculation or only three under certain circumstances
> .... and
> >>if the code could incorporate some form of logic..cells (A AND B AND C) OR(A And B......And F)
>
> This is absolutely do-able: if you can determine how many circumstances
> are possible and what cells are required in each given circumstance,
> then we can write the code to accommodate it.
>
> How do you feel about the "Validate" button?
>
> And just for grins, what is your continent/time zone? I am in North
> America, Eastern Daylight Time.
>
> Dave O
>
>

[Dave O]

Good morning, Dermot-
I'm 4 hours behind GMT, which is why our messages are a day apart: by
the time I clear my desk and do fun stuff such as posting to the Excel
newsgroup, you're probably gone for the day!

To create a command button on your spreadsheet, please practice once or
twice on a blank spreadsheet so we don't delete any data or have some
kind of catastrophic loss. From the menu click >View >Toolbars >Forms.
A box will appear, either "floating" on the spreadsheet or on the
menu. Within that box, click the icon for the command button (if you
float your cursor over the choices, the word "Button" will appear),
then click and drag your cursor on the spreadsheet to create a button
of the appropriate size and shape. We can assign a macro to this
button later (once it's finally written!).

The five scenarios you present are great, easily coded: but how can a
user, and subsequently our program, tell which scenario is which? Is
there any kind of label or other discriminator in use to tell the
scenarios apart? We need something like this to write the code: "if
cell A1 says "Scenario 1", validate the entries in cells etc etc etc".

I have a question about this statement from your post:
>>Cell C24 shows a "#Value!" error if cell C24 data is not entered.
How can c24 show an error message if data in c24 is not present? If
c24 shows #Value it must be a formula, so it can't be a data entry cell
that depends on itself- possibly a typo in your post?

Dave O

[Dermot]

Hi Dave

Hope this clarifies a litte
.....sorry...had a little drink after work tonight!

"#Value!" is a formula cell.......so is dependant on the other cells...I
assume that nothing can be done to reflect......that other data needs be
entereed?...
It would be good if a message suggested "Data input Incomplete"....or
something like that.
Terms for the different calculation could be......

Calc1 could be called "Basic"

Calc 2 ....."Secured"

Calc 3 ...." Mort"

Calc 4 ...."arrears"

Calc 5......." All"

Hope this helps .....if not please claify.

Regards

Dermot

















"Dave O" wrote:

> Good morning, Dermot-
> I'm 4 hours behind GMT, which is why our messages are a day apart: by
> the time I clear my desk and do fun stuff such as posting to the Excel
> newsgroup, you're probably gone for the day!
>
> To create a command button on your spreadsheet, please practice once or
> twice on a blank spreadsheet so we don't delete any data or have some
> kind of catastrophic loss. From the menu click >View >Toolbars >Forms.
> A box will appear, either "floating" on the spreadsheet or on the
> menu. Within that box, click the icon for the command button (if you
> float your cursor over the choices, the word "Button" will appear),
> then click and drag your cursor on the spreadsheet to create a button
> of the appropriate size and shape. We can assign a macro to this
> button later (once it's finally written!).
>
> The five scenarios you present are great, easily coded: but how can a
> user, and subsequently our program, tell which scenario is which? Is
> there any kind of label or other discriminator in use to tell the
> scenarios apart? We need something like this to write the code: "if
> cell A1 says "Scenario 1", validate the entries in cells etc etc etc".
>
> I have a question about this statement from your post:
> >>Cell C24 shows a "#Value!" error if cell C24 data is not entered.
> How can c24 show an error message if data in c24 is not present? If
> c24 shows #Value it must be a formula, so it can't be a data entry cell
> that depends on itself- possibly a typo in your post?
>
> Dave O
>
>

[Dave O]

It's possible to re-write the C24 formula to generate a message-
something along the lines of "if the formula results in an error,
display a message in the cell, else display the answer to the formula".

Do your labels, "Basic", "Secured", "Mort", "arrears", "All" already
appear on your spreadsheet? The code will require some way to figure
this out: either the user specifically identifies a basic transaction,
for instance, or we write the code in such a way as to figure out the
transaction type based on the same information you (a human operator)
would use.

How would you feel about exchanging instant messaging IDs, or email
addresses? This one or at best two messages per day business is a bit
frustrating. My email is CYCLEZENatsignYAHOOperiodCOM; my IM address
is cyclezen on AIM.

[Dermot]

Hi Dave
Hope I am not too late to take you up on this offer.
AIM sounds fine with me....
Sorry I have been away.
Dermot

"Dave O" wrote:

> It's possible to re-write the C24 formula to generate a message-
> something along the lines of "if the formula results in an error,
> display a message in the cell, else display the answer to the formula".
>
> Do your labels, "Basic", "Secured", "Mort", "arrears", "All" already
> appear on your spreadsheet? The code will require some way to figure
> this out: either the user specifically identifies a basic transaction,
> for instance, or we write the code in such a way as to figure out the
> transaction type based on the same information you (a human operator)
> would use.
>
> How would you feel about exchanging instant messaging IDs, or email
> addresses? This one or at best two messages per day business is a bit
> frustrating. My email is CYCLEZENatsignYAHOOperiodCOM; my IM address
> is cyclezen on AIM.
>
>

[Dave O]

Hopefully you were on holiday! No worries about the time lag, happy to
help.

Apologies: I inserted a typo in that post. My AIM designation in
cyclozen.

[Dermot]

Hi Dave
I have tried AIM a few times....what's the best time to catch you?
Cheers
Dermot

"Dave O" wrote:

> Hopefully you were on holiday! No worries about the time lag, happy to
> help.
>
> Apologies: I inserted a typo in that post. My AIM designation in
> cyclozen.
>
>

[Dave O]

Generally, starting about 9 am. My GMT offset is -4. I inadvertently
posted the wrong AIM designation: it is cyclozen. If that fails for
too much longer please email me at cyclezen ATSIGN yahoo DOT com.

[Dave O]

BTW Dermot- please post or send me your AIM handle.