help with Xl time sheet

  • Thread starter Thread starter Bill L.
  • Start date Start date
B

Bill L.

I am creating a time sheet. I am wondering if there is a short cut to enter
the staffs time. Right now I copy and paste the time in but was hopeing
there is a better way.
here what I am working with
part time who work 4 days in a row
full time who work 5 days in a row
I have early afternoon and late shifts
My work week is 6 days.
Colume "B" is mon "C" is tues... "G" is Sat.
can I make 6 short cut keys to fit all my needs?

I was thinking something like type f1 in colum "C" it would enter in 5 days
of early shift(tuesday colume "C" to sat colume "G"), f2 could be 5 days
afternoon f3 5 days evening f4 4 days early f5 4 days afternoon and f6 4
days
any chance of this or is copy paste my only option?
 
Hi Bill!

You could do it by subroutines and assign them to shortcut keys:

Use Alt +F11 to get to the Visual Basic Editor
Select the workbook in the top left project explorer window
Insert > Module
Double clicking the inserted Module in the explorer window actives the code
widow
Now copy and paste the following into the code window.

Back in Excel
Tools > Macro > Macros
Select each macro in turn and use the Options button to set up your shortcut
keys.


Sub Earlies()
' Shortcut Ctrl+e
ActiveCell.FormulaR1C1 = "6:00"
ActiveCell.Offset(0, 1).Value = "6:00"
ActiveCell.Offset(0, 2).Value = "6:00"
ActiveCell.Offset(0, 3).Value = "6:00"
ActiveCell.Offset(0, 4).Value = "6:00"
End Sub
Sub Arvo()
' Shortcut Ctrl+a
ActiveCell.FormulaR1C1 = "15:00"
ActiveCell.Offset(0, 1).Value = "15:00"
ActiveCell.Offset(0, 2).Value = "15:00"
ActiveCell.Offset(0, 3).Value = "15:00"
ActiveCell.Offset(0, 4).Value = "15:00"
End Sub
Sub Eves()
' Shortcut Ctrl+l
ActiveCell.FormulaR1C1 = "21:00"
ActiveCell.Offset(0, 1).Value = "21:00"
ActiveCell.Offset(0, 2).Value = "21:00"
ActiveCell.Offset(0, 3).Value = "21:00"
ActiveCell.Offset(0, 4).Value = "21:00"
End Sub
Sub Earlies4()
' Shortcut Ctrl+r
ActiveCell.FormulaR1C1 = "6:00"
ActiveCell.Offset(0, 1).Value = "6:00"
ActiveCell.Offset(0, 2).Value = "6:00"
ActiveCell.Offset(0, 3).Value = "6:00"
End Sub
Sub Arvo4()
' Shortcut Ctrl+s
ActiveCell.FormulaR1C1 = "15:00"
ActiveCell.Offset(0, 1).Value = "15:00"
ActiveCell.Offset(0, 2).Value = "15:00"
ActiveCell.Offset(0, 3).Value = "15:00"
End Sub
Sub Eves4()
' Shortcut Ctrl+k
ActiveCell.FormulaR1C1 = "21:00"
ActiveCell.Offset(0, 1).Value = "21:00"
ActiveCell.Offset(0, 2).Value = "21:00"
ActiveCell.Offset(0, 3).Value = "21:00"
ActiveCell.Offset(0, 4).Value = "21:00"
End Sub

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Bill an example of how it can be done

Place this code on the time sheet module

enter f1, f2, s1, s2 into any row on column B and it will populate
columns C to G in the same row.

Add/Delete case statements as required


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Rng As Range
Dim c As Range
Dim t As Date ' start time

If Target.Count > 1 Then
Exit Sub
ElseIf Target.Column <> 2 Then
Exit Sub
ElseIf Len(Target.Value) <> 2 Then
Exit Sub
End If
Range("c" & Target.Row & ":g" & Target.Row).ClearContents
Select Case Target.Value
Case "f1"
Set Rng = Range("c" & Target.Row & ":g" & Target.Row)
t = "07:00"
Case "f2"
Set Rng = Range("c" & Target.Row & ":g" & Target.Row)
t = "13:00"
Case "s1"
Set Rng = Range("c" & Target.Row & ":f" & Target.Row)
t = "07:00"
Case "s2"
Set Rng = Range("c" & Target.Row & ":f" & Target.Row)
t = "13:00"
Case Else
Exit Sub
End Select

For Each c In Rng
c = t
Next
End Sub
 
Back
Top