Automated Timesheet Help: Message Box & Submit Buttons

Hi, I need excel help! I have no idea about Macros and could use a wizz's assistance.
I have created a time sheet which will allow people to claim additional work they have worked as toil, flexi or overtime. I need a message box to pop up each day after they have entered a finish time and their is a value in the claimable hours field to tell them that they have worked "x" amount of hours over or under what they were supposed to and need to claim this time.
The other function I need help with is a submit button which will export the data from the timesheet into a central file with payroll. (we are a paperless office)
If we could have the submit button password protected so that only a manager can submit to payroll, and maybe a separate button for the employee to submit to their manager? Is this stuff even possible? HELP

good idea to post an example spreadsheet to help us give you some assistance please

Hi jmac,

Please find attached the spread sheet please work your magic this time sheet will be the death of me!

Hi tanya,

a couple of questions:

what is meant to happen if they work less than expected and the reason isn't one of your options? (or is that covered by "Flex"?)

the spreadsheet doesnt seem to handle the calculation of "adjustment hours" (which may be a better description than "Claimable hours") of a negative value, that is daily total less than expected, surely this is the same situation you want to catch (every instance where col i and col j are not the same?

is it OK to pop up the message as they enter a finish time and col i <> col j for each line??

I am not sure what the formula in col K is trying to do??

sorry if I am a bit thick this afternoon

jmac

Sorry,
after looking further I see code in the "worksheet"

I have been playing but my efforts are being stymied by the cell format for col K, I will look at trying to add another "helper cell" that is hidden as your existing helper cells are

jmac

Hi Tanya,

Are you still in trouble or have you got it working?

Jmac

tanya-3 timesheet.xlsm

Hi Tanya,

Ok here is an update.

The attached spreadsheet has code behind the "Timesheet" tab that will capture cell changes and where appropriate will display the suggested (or very close to them) messages from your sample spreadsheet.

A couple of things. I altered the spreadsheet to use the 1904 date system for calculations. This was needed to actually show the negative time results rather than "########" which is pretty useless.

I also altered some of the formulas in the hidden columns to correct what I think i was an error with the leave calculations using the wrong column in the calcs

This version does not force the user to enter an adjustment code as it stands. If you look at the code you will see a big block of commented code. This was me playing with a userform to enforce the need for the user to enter a code if an adjustment was needed (scope creep i admit but a learning exercise for me so worth the time investment from my point of view). I left it there for you to experiment with is you like.

The question of submission etc can certainly be handled in ways you asked about however there are a number of "workflow" type questions that you need to deal with before anybody could suggest a solution that matches your needs. I am happy to spend some time helping you if you would like some assistance in this area however I think it is probably beyond the scope of the forum so feel free to email me directly (address is in code).

I enjoyed teaching myself some new stuff, thanks for the question

Jmac

Hi Jmac sorry I haven't gotten back to you I only just got the alerts. I've attached the original copy of the spreadsheet before I changed the time formats. Hopefully this will help you jiggle around with it.
Timesheet - V1.xlsm

If you're having trouble seeing the code that was given to me please see below:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub ' exit if updating more than one cell
If Target.Offset(0, -2).Resize(1, 3) = "" Then Exit Sub

If Not Intersect(Target, Range("H:H")) Is Nothing Then ' updating column M

If Target.Value > Target.Offset(0, -2).Value Then

MsgBox "You have exceeded the scheduled hours by " & Target.Offset(0, -1).Value & " hours."

ElseIf Target.Value < Target.Offset(0, -2).Value Then

MsgBox "You are short of the scheduled hours by " & Target.Offset(0, -1).Value & " hours."

End If
End If
End Sub

Sorry i didn't see the attachment! You're a star thanks so much

Hi Jmac,
I've been playing with the timesheet you fixed up for me. Can you please tell me why it pops up with the message to say I've worked too many hours, when I have entered my correct hours and difference is 0?

you are welcome, as i said I used the question to teach myself some new stuff.

Dont forget the password stuff, happy to help offline

Jmac

if you consider the issue resolved then please mark the thread solved (see top row of original post for thread tools)
If you are happy with the suggested solution please consider clicking the "* Add Reputation" button in the lower left corner

Sorry Tanya, your last post hadnt arrived when i answered your previous one

I am a bit puzzled, did you actually use the spreadsheet I attached or cut the code into an existing one?.

Which row did you have the problem with, i just did it for row 11, start 10:00, finish 14:30, adjust hours = 0:00 and didnt get a message

It may be time to go offline to more easily include screen shots etc

John