Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a warning window
I'm working on a timecard project. I want to create a popup window
(userform) when the values someone inputs on the worksheet for their regular hours + other hours does not equal the time interval they inputed. Example: I input that I worked from 8:00am to 4:00pm (8hr day). I can break down my hours between regular work hours and other hours (overtime). If the employee puts in 5 regular hours and 10 other hours for that day, obviously that is more than the 8 hour day they claimed they worked. If this scenario was true, I want a window to pop up telling them that their hours don't equal. CAN I DO THAT? PCStechnical |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a warning window
The easiest way is using data validation. From the excel drop-down menus:
Data / Validation... Choose "custom" in the "allow" combo, then you can specify your formula. Your formula can contain cell references and boolean logic, just like a cell formula. Bill "PCStechnical" wrote: I'm working on a timecard project. I want to create a popup window (userform) when the values someone inputs on the worksheet for their regular hours + other hours does not equal the time interval they inputed. Example: I input that I worked from 8:00am to 4:00pm (8hr day). I can break down my hours between regular work hours and other hours (overtime). If the employee puts in 5 regular hours and 10 other hours for that day, obviously that is more than the 8 hour day they claimed they worked. If this scenario was true, I want a window to pop up telling them that their hours don't equal. CAN I DO THAT? PCStechnical |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a warning window
You really don't need a macro for this. Look at Data=Validation and use the
custom formula option. On the other two tabs in the dialog you can specify messages that pop up and so forth. Debra Dalgleish http://www.contextures.com/tiptech.html look under D and then Data Validation. -- Regards, Tom Ogilvy "PCStechnical" wrote: I'm working on a timecard project. I want to create a popup window (userform) when the values someone inputs on the worksheet for their regular hours + other hours does not equal the time interval they inputed. Example: I input that I worked from 8:00am to 4:00pm (8hr day). I can break down my hours between regular work hours and other hours (overtime). If the employee puts in 5 regular hours and 10 other hours for that day, obviously that is more than the 8 hour day they claimed they worked. If this scenario was true, I want a window to pop up telling them that their hours don't equal. CAN I DO THAT? PCStechnical |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a warning window
connected the data validation and it doesn't seem to be working.
If I put a data validation condition in a cell that has a formula in it, will the validation not work? What I have: cell 1 has the formula that adds regular hours +other hours. Cell 2 gives me the total hours worked that day (also a formula). In cell 1, I put a data validation of these settings Allow: custom Formula: =n7* *n7 is where the number of total hours is located any sugguestions on why the warning is not popping up? PCStech Tom Ogilvy wrote: You really don't need a macro for this. Look at Data=Validation and use the custom formula option. On the other two tabs in the dialog you can specify messages that pop up and so forth. Debra Dalgleish http://www.contextures.com/tiptech.html look under D and then Data Validation. -- Regards, Tom Ogilvy "PCStechnical" wrote: I'm working on a timecard project. I want to create a popup window (userform) when the values someone inputs on the worksheet for their regular hours + other hours does not equal the time interval they inputed. Example: I input that I worked from 8:00am to 4:00pm (8hr day). I can break down my hours between regular work hours and other hours (overtime). If the employee puts in 5 regular hours and 10 other hours for that day, obviously that is more than the 8 hour day they claimed they worked. If this scenario was true, I want a window to pop up telling them that their hours don't equal. CAN I DO THAT? PCStechnical |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a warning window
I did a test one in another worksheet that looks like this
regular hours other total 6 2 8 6 8 8 8 the demensions are columns a:c and rows 1:4 questions: which cell do I validate? when I tried validating in cell d3 the formula I put was (=a3+b3=c3) and nothing popped up. Solutions? Tom Ogilvy wrote: You really don't need a macro for this. Look at Data=Validation and use the custom formula option. On the other two tabs in the dialog you can specify messages that pop up and so forth. Debra Dalgleish http://www.contextures.com/tiptech.html look under D and then Data Validation. -- Regards, Tom Ogilvy "PCStechnical" wrote: I'm working on a timecard project. I want to create a popup window (userform) when the values someone inputs on the worksheet for their regular hours + other hours does not equal the time interval they inputed. Example: I input that I worked from 8:00am to 4:00pm (8hr day). I can break down my hours between regular work hours and other hours (overtime). If the employee puts in 5 regular hours and 10 other hours for that day, obviously that is more than the 8 hour day they claimed they worked. If this scenario was true, I want a window to pop up telling them that their hours don't equal. CAN I DO THAT? PCStechnical |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a warning window
How about just using an adjacent cell with a formula in it:
=if(someformulathatchecksyourinputisok,"","Please check your input!") Format the cell in nice big, bold, red letters. PCStechnical wrote: I'm working on a timecard project. I want to create a popup window (userform) when the values someone inputs on the worksheet for their regular hours + other hours does not equal the time interval they inputed. Example: I input that I worked from 8:00am to 4:00pm (8hr day). I can break down my hours between regular work hours and other hours (overtime). If the employee puts in 5 regular hours and 10 other hours for that day, obviously that is more than the 8 hour day they claimed they worked. If this scenario was true, I want a window to pop up telling them that their hours don't equal. CAN I DO THAT? PCStechnical -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a warning window
that might work--the only problem is will that show all the time. I
would like to have you be able to input your time you punched in, time you punched out, and record what type of hours they were. After that, if they don't add up, put in a warning. Is there a way that I can create a userform that gives me a screen they can input all that stuff (time in, time out, regular hours, other hours) and then it places it on the worksheet int he next available slot? PCStechnical Dave Peterson wrote: How about just using an adjacent cell with a formula in it: =if(someformulathatchecksyourinputisok,"","Please check your input!") Format the cell in nice big, bold, red letters. PCStechnical wrote: I'm working on a timecard project. I want to create a popup window (userform) when the values someone inputs on the worksheet for their regular hours + other hours does not equal the time interval they inputed. Example: I input that I worked from 8:00am to 4:00pm (8hr day). I can break down my hours between regular work hours and other hours (overtime). If the employee puts in 5 regular hours and 10 other hours for that day, obviously that is more than the 8 hour day they claimed they worked. If this scenario was true, I want a window to pop up telling them that their hours don't equal. CAN I DO THAT? PCStechnical -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a warning window
You could make your formula check to see if all the entries are complete before
it shows anything. But yep. You could create a userform that does pretty much what you want. It's just a matter of getting things to work ok. From Debra Dalgleish's site: http://contextures.com/xlUserForm01.html Tom Ogilvy posted these links: Peter Aiken Articles: Part I http://msdn.microsoft.com/library/en...FormsPartI.asp Part II http://msdn.microsoft.com/library/en...ormsPartII.asp If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm PCStechnical wrote: that might work--the only problem is will that show all the time. I would like to have you be able to input your time you punched in, time you punched out, and record what type of hours they were. After that, if they don't add up, put in a warning. Is there a way that I can create a userform that gives me a screen they can input all that stuff (time in, time out, regular hours, other hours) and then it places it on the worksheet int he next available slot? PCStechnical Dave Peterson wrote: How about just using an adjacent cell with a formula in it: =if(someformulathatchecksyourinputisok,"","Please check your input!") Format the cell in nice big, bold, red letters. PCStechnical wrote: I'm working on a timecard project. I want to create a popup window (userform) when the values someone inputs on the worksheet for their regular hours + other hours does not equal the time interval they inputed. Example: I input that I worked from 8:00am to 4:00pm (8hr day). I can break down my hours between regular work hours and other hours (overtime). If the employee puts in 5 regular hours and 10 other hours for that day, obviously that is more than the 8 hour day they claimed they worked. If this scenario was true, I want a window to pop up telling them that their hours don't equal. CAN I DO THAT? PCStechnical -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a warning window
You should validate the cells where the user is making entries. You say the
user makes 3 entries Total, Regular, Overtime. To be absolutely sure, you would need validation in each - but the formula would have to restrict entry only if there were entries in the other two as an example - although it would be more complex. -- Regards, Tom Ogilvy "PCStechnical" wrote: I did a test one in another worksheet that looks like this regular hours other total 6 2 8 6 8 8 8 the demensions are columns a:c and rows 1:4 questions: which cell do I validate? when I tried validating in cell d3 the formula I put was (=a3+b3=c3) and nothing popped up. Solutions? Tom Ogilvy wrote: You really don't need a macro for this. Look at Data=Validation and use the custom formula option. On the other two tabs in the dialog you can specify messages that pop up and so forth. Debra Dalgleish http://www.contextures.com/tiptech.html look under D and then Data Validation. -- Regards, Tom Ogilvy "PCStechnical" wrote: I'm working on a timecard project. I want to create a popup window (userform) when the values someone inputs on the worksheet for their regular hours + other hours does not equal the time interval they inputed. Example: I input that I worked from 8:00am to 4:00pm (8hr day). I can break down my hours between regular work hours and other hours (overtime). If the employee puts in 5 regular hours and 10 other hours for that day, obviously that is more than the 8 hour day they claimed they worked. If this scenario was true, I want a window to pop up telling them that their hours don't equal. CAN I DO THAT? PCStechnical |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a warning window
it seems though the validation option only works when the user actually
inputs a number instead of a formula computing a number in the cell you want to validate. Is that true? If it is, is there anything else that I can do. Tom Ogilvy wrote: You should validate the cells where the user is making entries. You say the user makes 3 entries Total, Regular, Overtime. To be absolutely sure, you would need validation in each - but the formula would have to restrict entry only if there were entries in the other two as an example - although it would be more complex. -- Regards, Tom Ogilvy "PCStechnical" wrote: I did a test one in another worksheet that looks like this regular hours other total 6 2 8 6 8 8 8 the demensions are columns a:c and rows 1:4 questions: which cell do I validate? when I tried validating in cell d3 the formula I put was (=a3+b3=c3) and nothing popped up. Solutions? Tom Ogilvy wrote: You really don't need a macro for this. Look at Data=Validation and use the custom formula option. On the other two tabs in the dialog you can specify messages that pop up and so forth. Debra Dalgleish http://www.contextures.com/tiptech.html look under D and then Data Validation. -- Regards, Tom Ogilvy "PCStechnical" wrote: I'm working on a timecard project. I want to create a popup window (userform) when the values someone inputs on the worksheet for their regular hours + other hours does not equal the time interval they inputed. Example: I input that I worked from 8:00am to 4:00pm (8hr day). I can break down my hours between regular work hours and other hours (overtime). If the employee puts in 5 regular hours and 10 other hours for that day, obviously that is more than the 8 hour day they claimed they worked. If this scenario was true, I want a window to pop up telling them that their hours don't equal. CAN I DO THAT? PCStechnical |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a warning window
I don't see any use for a formula in the cell in the scenario you describe.
If there is a formula, then it is unclear why you would need any type of validation. The user enters two values and the third is calculated. - no disconnect. -- Regards, Tom Ogilvy "PCStechnical" wrote in message ups.com... it seems though the validation option only works when the user actually inputs a number instead of a formula computing a number in the cell you want to validate. Is that true? If it is, is there anything else that I can do. Tom Ogilvy wrote: You should validate the cells where the user is making entries. You say the user makes 3 entries Total, Regular, Overtime. To be absolutely sure, you would need validation in each - but the formula would have to restrict entry only if there were entries in the other two as an example - although it would be more complex. -- Regards, Tom Ogilvy "PCStechnical" wrote: I did a test one in another worksheet that looks like this regular hours other total 6 2 8 6 8 8 8 the demensions are columns a:c and rows 1:4 questions: which cell do I validate? when I tried validating in cell d3 the formula I put was (=a3+b3=c3) and nothing popped up. Solutions? Tom Ogilvy wrote: You really don't need a macro for this. Look at Data=Validation and use the custom formula option. On the other two tabs in the dialog you can specify messages that pop up and so forth. Debra Dalgleish http://www.contextures.com/tiptech.html look under D and then Data Validation. -- Regards, Tom Ogilvy "PCStechnical" wrote: I'm working on a timecard project. I want to create a popup window (userform) when the values someone inputs on the worksheet for their regular hours + other hours does not equal the time interval they inputed. Example: I input that I worked from 8:00am to 4:00pm (8hr day). I can break down my hours between regular work hours and other hours (overtime). If the employee puts in 5 regular hours and 10 other hours for that day, obviously that is more than the 8 hour day they claimed they worked. If this scenario was true, I want a window to pop up telling them that their hours don't equal. CAN I DO THAT? PCStechnical |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a warning window
The formula is for the total hours. They don't actualy punch in the
total hours they worked, they punch in the actual time frame they worked (time in and time out). Then I calculate that in a formula to get a numberic value for the number of hours that spans. Tom Ogilvy wrote: I don't see any use for a formula in the cell in the scenario you describe. If there is a formula, then it is unclear why you would need any type of validation. The user enters two values and the third is calculated. - no disconnect. -- Regards, Tom Ogilvy "PCStechnical" wrote in message ups.com... it seems though the validation option only works when the user actually inputs a number instead of a formula computing a number in the cell you want to validate. Is that true? If it is, is there anything else that I can do. Tom Ogilvy wrote: You should validate the cells where the user is making entries. You say the user makes 3 entries Total, Regular, Overtime. To be absolutely sure, you would need validation in each - but the formula would have to restrict entry only if there were entries in the other two as an example - although it would be more complex. -- Regards, Tom Ogilvy "PCStechnical" wrote: I did a test one in another worksheet that looks like this regular hours other total 6 2 8 6 8 8 8 the demensions are columns a:c and rows 1:4 questions: which cell do I validate? when I tried validating in cell d3 the formula I put was (=a3+b3=c3) and nothing popped up. Solutions? Tom Ogilvy wrote: You really don't need a macro for this. Look at Data=Validation and use the custom formula option. On the other two tabs in the dialog you can specify messages that pop up and so forth. Debra Dalgleish http://www.contextures.com/tiptech.html look under D and then Data Validation. -- Regards, Tom Ogilvy "PCStechnical" wrote: I'm working on a timecard project. I want to create a popup window (userform) when the values someone inputs on the worksheet for their regular hours + other hours does not equal the time interval they inputed. Example: I input that I worked from 8:00am to 4:00pm (8hr day). I can break down my hours between regular work hours and other hours (overtime). If the employee puts in 5 regular hours and 10 other hours for that day, obviously that is more than the 8 hour day they claimed they worked. If this scenario was true, I want a window to pop up telling them that their hours don't equal. CAN I DO THAT? PCStechnical |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro warning window | Excel Discussion (Misc queries) | |||
Creating a warning window | Excel Discussion (Misc queries) | |||
Creating a Warning window | Excel Worksheet Functions | |||
Macro warning window | Excel Discussion (Misc queries) | |||
Getting rid of macro warning window | Excel Programming |