Forum Discussion
entering the working time, automatically marked with color in the timeline.
- Jul 12, 2020
No problem. Here is the code you need to place on Next month's Sheet module.
Public sTimeFound As Boolean Public sColumn As Long Public eColumn As Long Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim timeType As String Dim dt As Date Dim empRow As Long Dim startCell As Range Dim timeRng As Range Dim dtCel As Range Dim tCel As Range Dim dtRng As Range Dim emp As String Dim n As Variant Dim dws As Worksheet Dim empRng As Range Dim inputRng As Range Dim clr As Long Set dws = Worksheets("IND1") Set empRng = dws.Range("H1:Q1") Set dtRng = dws.Range("C8:C348") Set timeRng = dws.Range("F8:BA8") Set inputRng = Range("I10:EZ40") Application.EnableEvents = False If Not Intersect(Target, inputRng) Is Nothing Then timeType = VBA.Trim(Cells(9, Target.Column).Value) emp = Cells(5, Target.Column).MergeArea.Cells(1).Value n = Application.Match(emp, empRng, 0) clr = empRng.Cells(n).Interior.Color If IsError(n) Then MsgBox "The employee " & emp & " doesn't exist on " & dws.Name & " Sheet.", vbExclamation GoTo Skip End If If (LCase(timeType) = "from" Or LCase(timeType) = "to") And Target <> "" Then dt = Cells(Target.Row, 2).Value For Each dtCel In dtRng If dtCel.Value = dt Then empRow = dtCel.Row empRow = empRow + n Exit For End If Next dtCel For Each tCel In timeRng If CDate(tCel.Value) = CDate(Target.Value) And LCase(timeType) = "from" Then sColumn = tCel.Column With dws.Cells(empRow, sColumn) .Value = Target.Value .NumberFormat = "hh:mm" End With sTimeFound = True Exit For ElseIf CDate(tCel.Value) = CDate(Target.Value) And LCase(timeType) = "to" Then eColumn = tCel.Column With dws.Cells(empRow, eColumn) .Value = Target.Value .NumberFormat = "hh:mm" End With End If Next tCel End If If sColumn <> 0 And eColumn <> 0 And sTimeFound = True Then If Application.Count(dws.Range("F" & empRow, dws.Cells(empRow, sColumn - 1))) = 0 Then dws.Range("F" & empRow, dws.Cells(empRow, sColumn - 1)).Interior.ColorIndex = xlNone ElseIf Application.Count(dws.Range(dws.Cells(empRow, eColumn + 1), "BA" & empRow)) = 0 Then dws.Range(dws.Cells(empRow, eColumn + 1), "BA" & empRow).Interior.ColorIndex = xlNone End If dws.Range(dws.Cells(empRow, sColumn), dws.Cells(empRow, eColumn)).Interior.Color = clr sColumn = 0 eColumn = 0 sTimeFound = False End If End If Skip: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Unprotect "1234" With Range("A10:EZ40").Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .PatternTintAndShade = 1 End With If Not Intersect(Target, Range("A10:EZ40")) Is Nothing Then With Range(Cells(Target.Row, 1), Cells(Target.Row, 156)).Interior .Pattern = xlGray25 .PatternThemeColor = xlThemeColorAccent2 .PatternTintAndShade = 0.399945066682943 End With Application.EnableEvents = False Target.Activate Application.EnableEvents = True End If Protect "1234" End Sub
Also, don't forget to mark the post with the proposed solution as a Best Response. 🙂
Thanks for all your wishes.
Have a good time ahead!
I know that I ask a lot, but as a beginner and self-learner, I have no other way of getting close to a solution.
Wish life gives you much more back 🙂
Nikolino
No problem. Here is the code you need to place on Next month's Sheet module.
Public sTimeFound As Boolean
Public sColumn As Long
Public eColumn As Long
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim timeType As String
Dim dt As Date
Dim empRow As Long
Dim startCell As Range
Dim timeRng As Range
Dim dtCel As Range
Dim tCel As Range
Dim dtRng As Range
Dim emp As String
Dim n As Variant
Dim dws As Worksheet
Dim empRng As Range
Dim inputRng As Range
Dim clr As Long
Set dws = Worksheets("IND1")
Set empRng = dws.Range("H1:Q1")
Set dtRng = dws.Range("C8:C348")
Set timeRng = dws.Range("F8:BA8")
Set inputRng = Range("I10:EZ40")
Application.EnableEvents = False
If Not Intersect(Target, inputRng) Is Nothing Then
timeType = VBA.Trim(Cells(9, Target.Column).Value)
emp = Cells(5, Target.Column).MergeArea.Cells(1).Value
n = Application.Match(emp, empRng, 0)
clr = empRng.Cells(n).Interior.Color
If IsError(n) Then
MsgBox "The employee " & emp & " doesn't exist on " & dws.Name & " Sheet.", vbExclamation
GoTo Skip
End If
If (LCase(timeType) = "from" Or LCase(timeType) = "to") And Target <> "" Then
dt = Cells(Target.Row, 2).Value
For Each dtCel In dtRng
If dtCel.Value = dt Then
empRow = dtCel.Row
empRow = empRow + n
Exit For
End If
Next dtCel
For Each tCel In timeRng
If CDate(tCel.Value) = CDate(Target.Value) And LCase(timeType) = "from" Then
sColumn = tCel.Column
With dws.Cells(empRow, sColumn)
.Value = Target.Value
.NumberFormat = "hh:mm"
End With
sTimeFound = True
Exit For
ElseIf CDate(tCel.Value) = CDate(Target.Value) And LCase(timeType) = "to" Then
eColumn = tCel.Column
With dws.Cells(empRow, eColumn)
.Value = Target.Value
.NumberFormat = "hh:mm"
End With
End If
Next tCel
End If
If sColumn <> 0 And eColumn <> 0 And sTimeFound = True Then
If Application.Count(dws.Range("F" & empRow, dws.Cells(empRow, sColumn - 1))) = 0 Then
dws.Range("F" & empRow, dws.Cells(empRow, sColumn - 1)).Interior.ColorIndex = xlNone
ElseIf Application.Count(dws.Range(dws.Cells(empRow, eColumn + 1), "BA" & empRow)) = 0 Then
dws.Range(dws.Cells(empRow, eColumn + 1), "BA" & empRow).Interior.ColorIndex = xlNone
End If
dws.Range(dws.Cells(empRow, sColumn), dws.Cells(empRow, eColumn)).Interior.Color = clr
sColumn = 0
eColumn = 0
sTimeFound = False
End If
End If
Skip:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Unprotect "1234"
With Range("A10:EZ40").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.PatternTintAndShade = 1
End With
If Not Intersect(Target, Range("A10:EZ40")) Is Nothing Then
With Range(Cells(Target.Row, 1), Cells(Target.Row, 156)).Interior
.Pattern = xlGray25
.PatternThemeColor = xlThemeColorAccent2
.PatternTintAndShade = 0.399945066682943
End With
Application.EnableEvents = False
Target.Activate
Application.EnableEvents = True
End If
Protect "1234"
End Sub
Also, don't forget to mark the post with the proposed solution as a Best Response. 🙂
Thanks for all your wishes.
Have a good time ahead!
- NikolinoDEJul 14, 2020Gold ContributorThanks for the hint.
I would like to apologize if I should have said something sloppy ... that was not my intention. Apologize for my persistence on this topic ... based on my very little VBA knowledge and at the same time, to be honest, I took a bit over and promised my colleagues to do it for our team.
It is not my job to construct excels ... I don't earn my living with it,
I only do it because I enjoy it and I like to learn something ... at the same time a little show with my colleagues :-).
Anyway thanks for the effort and patience you had with me ... wish you all the luck in the world, with love, joy and health.
Nikolino - Subodh_Tiwari_sktneerJul 14, 2020Silver Contributor
This is not fair as you don't want to pay attention to any of my post.
I have clearly mentioned several times that under which condition this code would not work but probably you didn't read my instruction and notes carefully.
It is you who has to manage and control this file in future not me so it is important for you to know what should be the correct layout of the month sheet and IND1 sheets to make this code work perfectly.
Do one thing, go through all my previous posts and read all my points and instructions and make the correction accordingly in your file as you need to learn yourself to debug the issues in future.
I would give you a hint though and it is the sequence of the employee in column E on IND1 Sheet which doesn't match with the sequence of employees in the range G1:Q1.
Correct this sequence and then test the code again.
- NikolinoDEJul 13, 2020Gold ContributorHi Hi Subodh Tiwari sktneer,
It doesn't work like before.
The working hours contributions of MA 1 in January should be shown in sheet IND1 in cell row F13: BA13, but can be seen in F9: BA9. And only the first two services can be seen ("Dienst 1" and "Dienst 2", "Bereitschaft" is missing in IND1).
Funny in the previous version of you, the colors in the fields in worksheet IND1 appeared automatically.
In worksheet IND1, the MA (colleagues) of G1: P1, in Q is only the sum of the team.
If I enter times in the January worksheet at MA1 (in Dienst 1, 2 and Bereitschaft), for example on January 1, worksheet IND1 should appear in color 13 in F13: BA13.
Where i make the mistake?
Maybe I'm not doing something right there...maybe or sure..if I didn't cry I would laugh 🙂
Thx
Nikolino - Subodh_Tiwari_sktneerJul 13, 2020Silver Contributor
Please find the attached with the tweaked codes.
Points to remember.
The employees names in Row#5&6 on January Sheet must exactly match with the employees names in the range G1:Q1 on IND1 Sheets.
I have tweaked the formulas on January Sheet in Row#5&6 for you.
- If you enter a time on January Sheet in any cell and if you find that the time you entered is not populated by the code on IND1 Sheet, manually overwrite that time on IND1 Sheet in Row#8. e.g. if you enter a time say 2:30 in any cell on January sheet and if this time is not populated by the code on IND1 Sheet for the corresponding date and employee, locate that time in Row#8 on IND1 Sheet, in this case it would be 2:30 so find the cell with the time 2:30 in Row#8 on IND1 Sheet and select that cell and type 2:30 to overwrite the preexisting time in that cell. Now come back to the January Sheet, select the cell where you previously entered the time 2:30, press F2 key to go into the edit mode and hit Enter and this will trigger the code again and see if this time is now populated by the code on IND1 Sheet. Please remember, the time you enter on January Sheet must match with time on IND1 Sheet in Row#8.
- NikolinoDEJul 13, 2020Gold Contributor
I still need one last help. I translated everything in German on the sheet and also changed the VBA code into the words.
Have it installed so as mandatory, but I come to no result.
Sure there is a mistake somewhere ... as sure is that I can't find it ... even if I could have made it accidentally.Sending you file and ask for one last time for help and patience with me 🙂
ThxNikolino
- NikolinoDEJul 12, 2020Gold ContributorBow me !!!
Great ... awesome
Thanks alot
I know it was a difficult birth with me ... but as far as I can see ... IT WORKS ... I will continue tomorrow .... thanks again and wish you love, joy and health in your life.
Regards,
Nikolino