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

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
>
>

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
>
>

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
> >
> >

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
> >
> >

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

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

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

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
> > >
> > >
>
>

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
> > > >
> > > >
> >
> >