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:
-------------------------------------------------
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]