Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
Hi Dave
A validate button sounds fine to me. The input cells a 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
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. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Error message boxes....
BTW Dermot- please post or send me your AIM handle.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Boxes in Forms | Excel Discussion (Misc queries) | |||
XL97: Position text boxes (or data labels) w/out VBA | Charts and Charting in Excel | |||
check boxes - copy | Excel Discussion (Misc queries) | |||
Check boxes - when one box is checked, I want a 2nd box to auto ch | Excel Discussion (Misc queries) | |||
Excel 2002 Will Not Print Text Boxes | Excel Discussion (Misc queries) |