Creating a warning window

[PCStechnical]

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

 

[Guest]

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

 

[Guest]

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.

 

[PCStechnical]

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

 

[PCStechnical]

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?

 

[Dave Peterson]

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]

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]

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-us/dnoffpro01/html/IntroductiontoUserFormsPartI.asp
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsPartII.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

 

[Guest]

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.

 

[PCStechnical]

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]

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.

 

[PCStechnical]

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.