AUtomatically enter default values

tim

New member
Joined
Jul 24, 2013
Messages
18
Reaction score
0
Points
0
Excel Version(s)
2016
Hello there,

I am trying to speed up input of attendance sheet. Upon entry of both start and end hour. (which are in respectively column 3 and 6) I want the standard lunch begin and end hour to appear in column 4 and 5.

So far It doesn't work as supposed to be:
With code below I tried but no success
.. can't seem to find the solution but I think it should be simple.
I am wondering if the syntax is correct .. in any case it doesn't fire the code when columns other than 3 is changed
Any pointers are usefull thankx.

Before I did this behaviour with formula's in column 3 and 4 but the problem is that they are mostly overrided with values if non default lunch
hence the formulas dissapear

I have following code in
VBA in ThisWorkbook:
-------------------------------------------------
Code:
[COLOR=#333333]Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)[/COLOR]
[COLOR=#333333]Dim rngDV As Range[/COLOR]
[COLOR=#333333]Dim oldVal As String[/COLOR]
[COLOR=#333333]Dim newVal As String[/COLOR]
[COLOR=#333333]Dim lUsed As Long[/COLOR]


[COLOR=#333333]If ValidSheet(ActiveSheet.Name) Then   'only for certain sheets following should be applied[/COLOR]
[COLOR=#333333]If Target.Count > 1 Then GoTo exitHandler[/COLOR]
[COLOR=#333333]On Error Resume Next[/COLOR]
[COLOR=#333333]Application.EnableEvents = False[/COLOR]


[COLOR=#333333]Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)[/COLOR]
[COLOR=#333333]On Error GoTo exitHandler[/COLOR]

[COLOR=#333333]If rngDV Is Nothing Then GoTo exitHandler[/COLOR]

[COLOR=#333333]If Intersect(Target, rngDV) Is Nothing Then[/COLOR]
[COLOR=#333333]'do nothing[/COLOR]
[COLOR=#333333]Else[/COLOR]

'---> This part works
[COLOR=#333333]If Target.Column = 3 Or Target.Column = 4 Or Target.Column = 5 Or Target.Column = 6 Then[/COLOR]
[COLOR=#333333]' track changes to each input![/COLOR]
[COLOR=#333333]Cells(Target.Row, 29).Value = Now[/COLOR]
[COLOR=#333333]End If
[/COLOR]
[COLOR=#333333]If Target.Column = 3 Or Target.Column = 6 Then[/COLOR]
[COLOR=#333333]' fill in standard lunch break 30 minutes[/COLOR]
[COLOR=#333333]' track changes to each input![/COLOR]
[COLOR=#333333]  If IsDate(Cells(Target.Row, 3)) And IsDate(Cells(Target.Row, 6)) Then[/COLOR]

[COLOR=#333333]    If (IsEmpty(Cells(Target.Row, 4))) Then[/COLOR]
[COLOR=#333333]          MsgBox "Begin/end hours are filled in; standard lunch hours are taken" [/COLOR]
[COLOR=#333333]          Cells(Target.Row, 4).Value = "12:00"[/COLOR]
[COLOR=#333333]    End If[/COLOR]
[COLOR=#333333]    If (IsEmpty(Cells(Target.Row, 5))) Then[/COLOR]
[COLOR=#333333]          MsgBox "Begin/end hours are filled in; standard lunch hours are taken" [/COLOR]
[COLOR=#333333]          Cells(Target.Row, 5).Value = "12:30"[/COLOR]
[COLOR=#333333]    End If[/COLOR]
[COLOR=#333333]  End If[/COLOR]
[COLOR=#333333]End If[/COLOR]


[COLOR=#333333]End If[/COLOR]

[COLOR=#333333]Else  ' Other action for Sheet Settings[/COLOR]
[COLOR=#333333]  If sh.Name Like "Settings*" Then[/COLOR]
[COLOR=#333333]    If Not Intersect(Target, Range("B3")) Is Nothing Then [/COLOR][COLOR=#333333]Reinitialize[/COLOR]
[COLOR=#333333]   End If 
[/COLOR][COLOR=#333333]End If[/COLOR]

[COLOR=#333333]exitHandler:[/COLOR]
[COLOR=#333333]Application.EnableEvents = True[/COLOR]
[COLOR=#333333]End Sub[/COLOR]
 
Top