AUtomatically Add Lunch Pauze

[timpie_s]

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 because once beginning OR end is entered the lunch pause appears
It should only do this once both have been entered .. can't seem to find the solution but I think it should be simple

I have this in VBA in ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long


If ValidSheet(ActiveSheet.Name) Then

If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Application.EnableEvents = False


Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else


If Target.Column = 3 Or Target.Column = 4 Or Target.Column = 5 Or Target.Column = 6 Then
' track changes to each input!
Cells(Target.Row, 29).Value = Now
End If
If Target.Column = 3 Or Target.Column = 6 Then
' fill in standard lunch break 30 minutes
' track changes to each input!
If IsDate(Cells(Target.Row, 3)) And IsDate(Cells(Target.Row, 6)) Then

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


End If

Else
If sh.Name Like "Settings*" Then
If Not Intersect(Target, Range("B3")) Is Nothing Then
'MsgBox "Hello"
Reinitialize
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub

 

[btomjack]

Try replacing this:

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

With this:

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

You need to use an AND statement to make sure that both columns 3 and 6 are filled before filling in 4 and 5. Also, you needed to reverse the logic of the statement to say, "when 3 and 6 are NOT empty, do such and such..."

 

[timpie_s]

Thankx btomjack,

but it just doesn't solve my problem. It's more fundamental i think since apparently none
of the code is firing if changes are made to columns other than 3. (checked this using MsgBox Target.Column)

I tried doubling the code and check for each column separately but none of this is working. It's still only
working whenever a change is made to column 3. This is most bizarre.

Anyone.?

Thankx