Help With Timesheet

[Dazzawm]

I am creating a timesheet and need help with the formula to make the total worked for that day. I want to be able to enter the times as I have them if possible rather than something like 08:00:00 17:00:00.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>C</TD></TR><TR style="HEIGHT: 19.5pt" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 19.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=26>Start</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Finish</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Total</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=25>09.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>17.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>

I will be able to do all the others and do the total at the bottom, my minds gone blank for now and just need a start. Please bear in mind that nights may be worked so they may sort of overlap and it may think that the finish time is before the start time!

 

[Dazzawm]

Will this work if I save the file as a template? I need him to open up the template, enter his hours then 'save as' e.g w/e 25/2/11 and the template remains the same without any accidental deletions of formulas.

 

[VoG]

Yes that should work. But you probably need to prevent him from saving his current week as the template. In the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not SaveAsUI Then
    Cancel = True
    MsgBox "Please use Save As", vbExclamation
End If
End Sub

N.B. Don't enter this code until you have finalised the template!

 

[Dazzawm]

I can't seem to lock the cells. I will let him have 'free reign' and i will keep a copy in case he messes it up. Thanks for your help VoG.

 

[Dazzawm]

Can you do me a macro that when he clicks on any cells from G7 to G30 a box pops up and says 'Please Do Not Edit This Cell'. Thanks.

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("G7:G30")) Is Nothing Then
    MsgBox "Please do not edit this cell", vbInformation
End If
End Sub

 

[Dazzawm]

That does not do anything?!

 

[Dazzawm]

Just to recap where I am. I have the timesheet with formulas in with no other macros. I just need the macro do the above request. Thanks.

 

[VoG]

It works for me. Make sure that you put it in the correct sheet's code module.

 

[Dazzawm]

It worked then, I right clicked on sheet 1 and inserted module first time, why was it different this time thats what I normally do.

 

[VoG]

Event code goes in sheet modules or workbook modules because they are designed to react to events with sheets or workbooks. These are special Class Modules. Normally all other code goes in a regular module added from the Insert menu.