help with Xl time sheet

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

 

[Norman Harker]

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.

 

[mudraker]

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

 

[Bill L.]

Thanks all for the help...