Counting Employee hours booked

[Mnet]

Dear Excel users - I have a Userform were the employees book their hours work on specific jobs with job numbers and activities. It is working perfectly. Now I want to count the hours and show it to the employee each time he fills in the form e.g he might work .2 hours on job 123 and .6 hours on job 234 and 2 hours on job 345. Each time he activate the sheet to put his firstname, Lastname, jobnumber etc. in for the next job (It only captures one job at a time), it must show him how many hour he have already booked. All the data that is filled in is captures on a second worksheet called "Data". Any suggestions on how I can incorporate this on my Userform? All idees, suggestions or solutions will be highly appreciated.

[Bob Phillips]

Just do a simple COUNTIF of the job number, e.g.

txtHoursWorked.Text = Application.Countif(Worksheets("TimeData").Columns("D"), txtJobNumber.Text)

adjusted o your situation of course

[Mnet]

Dear xld there is 45 different jobs and 100 different activities that it links to .....so I need to count a persons hours booked not the job. I have tried it for both and not working. Just to clarify - each job is recorded separately if e.g. John worked on three different jobs, each one is recorded separately. Each time John book a job it must tell him you have already booked 4 hrs and the next one say 6 hours if he work one hour on the next job and so on. Hope this helps. Thank you!!

[Bob Phillips]

That is exactly what I gave you, but it does need adapting because I have no idea what your data or your form look like.

[greyangel]

Would you be able to post an example of the workbook. That would allow us to help you with writing some type of VBA that could help you.

[Mnet]

Please see attachment - Thank You!

[greyangel]

Would this work? Each time the "Hours worked" box is changed a sumif takes place in the background and matches the person's hours to the date.

[Mnet]

Dear greyangel - thank you for the great work I have changed it to show on the Data Sheet in P2 - I do not want to Add or Delete another sheet - it must run in the background. I have tested it and it is not working it does not sum it only shows the amount of hours that was put in for hour worked . If e.g. Peter Pan need to fill in 4 timesheets on the same day for 4 different jobs it must sum his total. E.g. If job 123 was 2 hrs and he fills in the next time sheet it was 1 hrs it must show in the counter 3 hrs so that Peter knows he has 5 hrs left for an 8 hour day......does it make sense? Also getting error when sending to Database.....

Private Sub Reg7_change()
'Sheets.Add
Range("P2").Formula = "=SUMIFS(Data!I:I,Data!C:C," & Reg1.Value & ",Data!E:E," & Reg3.Value & ",Data!F:F," & Reg4.Value & ")+ " & Reg7.Value & ""
Label29 = Range("P2").Value
'ActiveSheet.Delete
End Sub

[greyangel]

Try this one. I can't really run the macro because my excel does not recognize the Format() feature and your spreadsheets are locked. The sumifs function should work as long as the date, Last name, and firstnme box matches whats on the spreadsheet exactly.

[Mnet]

Dear Greyangel it is working perfectly - Thank you so much your time ,effort and help.... is greatly appreciated!!!