Printable View
Good evening, Here I am hat in hand again.
Picture a group of 4 cells ( let us say A1 A2 B1 B2) could be any group of 4 cells in
range C4:D21....E4:F21....G4:H21....I4:J21....K4:L21 (Not C4:L21)
A1 is the IN time the employee comes to work
A2 is the OUT time
B2 is 0:30 min (Lunch)
I think I need two conditions:
1st condition : If I clear the IN time cell then both OUT and Lunch cells clear also.
2nd condition : If OUT less IN is 4 Hours or less then clear the Lunch cell.
Thank you
Do you need to solve through VBA, or can you use excel formulas?
Hello Codemakr ... any solution is welcome
Correction to above ... no formula otherwise I would have to introduce the formula in the clered cells all over again.
Nick, this is relatively easy..
Code would be added to the Worksheet_Change Module of the sheet to address this. i will use the latest version I posted in the other thread and work something out a bit later.
I am just short on time at the moment..but will get something back for you before the end of the night.
Thanks Pete thought you were taking time off this evening.
BTW if you are going to use the last revision pls check SUA for a day only in regards to the clearing of the 0:30 min Lunch ... Somehow for the whole week it clears it but you must have forgotten to revise the code for the single day.
This is for the viewers that might download the file.
IMPORTANT to the MODERATOR:
This is not a one on one conversation ... Pete put two and two together and guessed that I might use the solution in regards to another post he helped me with.
One could say the man is very helpful and clairvoyant to boot.:grinhalo:
Noted. Will do.Quote:
Originally Posted by ndendrinos
I am one of the moderators. And thank you:thumbQuote:
IMPORTANT to the MODERATOR:
This is not a one on one conversation ... Pete put two and two together and guessed that I might use the solution in regards to another post he helped me with.
One could say the man is very helpful and clairvoyant to boot.:grinhalo:
TIme off? maybe from Mr. Excel and some of the newer posts. But I feel a personal connection to this file now...happy to help tidy it up more for you.
Besides, I always learn something new when I go the extra steps.
The note to the moderator was addressed to the "volatile" one just in case he's on this evening.:nervous:
Did I miss something?Quote:
Originally Posted by ndendrinos
If I understand you then yes, long ago and better not revisited.
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Cells.Count > 1 Then GoTo Skip
Set myRange = Range("C4:C21,E4:E21,G4:G21,I4:I21,K4:K21")
'check only the "in" cells for cleared, check the "out" cells for Out-In < 4
If Not Intersect(Target, myRange) Is Nothing Then
If IsEmpty(Target) And WorksheetFunction.IsEven(Target.Row) Then 'if Target was cleared
Target.Offset(1).Resize(1, 2).ClearContents 'clears 1 row down, 1 row right
End If
If Not IsEmpty(myRange) And WorksheetFunction.IsOdd(Target.Row) Then 'check the time difference
If (Target.Value <= 4 / 24 - Target.Offset(-1).Value) Then
Target.Offset(, 1).ClearContents
Else: Target.Offset(, 1).Value = 0.5 / 24 'if the time > 4 hours, put the lunch 1/2 hr Back
End If
End If
End If
Application.Calculate
Skip:
Application.EnableEvents = True
End Sub[/vba]
Nick, try that in your worksheet change module. I added also that if the time Out-In > 4 that it puts the lunch in to the appropriate cell. Tested a bit, seems to do what you asked for.
Might be a better way! Worksheet events are not my forte.
Cleaned up the code for the missed day buttons/reasons, it clears the 3 necessary cellls when SUA is clicked.