Jul 08 2020 01:08 PM
When entering the working time in a time entry, this time should be automatically marked with color in the timeline.
Is this possible?
if yes... how?
Any help is welcome
Thx in Advance
Nikolino
Jul 08 2020 10:57 PM
Your table is blank so it is hard to visualize what exactly you are trying to achieve here.
Why not enter some time values and mock up the desired output manually and upload the file again to show what you are trying to achieve?
Jul 08 2020 11:14 PM
Jul 10 2020 12:18 AM
I have placed the formulas in rows 7 and 8 and you will need to copy the same formula to other rows but change the range reference accordingly. Remember these are Array Formulas so you will need to confirm it with Ctrl+Shift+Enter not Enter only.
Jul 10 2020 06:29 AM
Jul 10 2020 12:00 PM
Hi Subodh Tiwari ,
Send the file with the details again. Believe that a picture can say a thousand words ... it is the same with an excel sheet.
Think so you can better imagine what I want to do. It is about services, 1,2,3 that should appear in a timeline ... only the time that each MA has done every day in the month. All other fields in the timeline should remain white.
Sorry in advance that I will take your time, but as a beginner (I see myself) I need "a little" help.
Nikolino
Jul 11 2020 08:34 AM
Since I cannot open your file I am unable to see what you were trying to show in this file. Can you open the file you uploaded in your last post?
But please find the attached in which I have applied a conditional formatting. I selected the range C7:AX7 and applied a New Rule for conditional formatting to color the cells where formula returns time values, so that other cells without any time value in the timeline have no color in them.
Refer to the attached for more details.
Jul 11 2020 10:41 AM
Hi,
I apologize for this mistake on my part. Send now the file without errors ... there as I said you can also see the differences I mean. Just want it to have the timeline color from start to finish (otherwise no color). That should be with every employee (MA1, MA2, etc.) and every day in month when he is on duty.
Thanks also for your patience with me :)
Nikolino
Jul 11 2020 10:57 AM
Hi,
Jul 11 2020 01:04 PM
I have used VBA to get what you are trying to achieve.
You just need to enter the time in columns with from and to headers and timeline should be highlighted as per the time entered.
The code will only work if you enter the from time first and then enter the to time so you will have to take care of that.
You will also find a button called Clear Timeline placed in cell A3 which you can click to clear the timeline.
This approach uses no formulas, the time values will be placed by the code on the timeline.
This has consumed good amount of time so this is the max I can do for you.
Jul 11 2020 02:10 PM
WOW .... bow to a VBA guru.
Many thanks for your effort and patience.
In the end and before you lose your trust in me, I would like to please a little ... if this is possible.
I'm a total ignorant in VBA…how could I put that into my sheet?
The Working times that I enter in sheet January should appear yellow in sheet IND1 ... in the correct place and date. It should only show the working hours in color ... only the working hours.
I hope that I didn't annoy you with mine ... but as a VBA beginner ... I took over and promised my colleagues that this would be feasible ... so that everyone can see their working time in a timeline per day.
I also prepared the workbook and copied the code into it .... I would be really happy if it could work.
A big thank you for everything done so far and for what you will do (better said ... what I hope) in advance.
Jul 11 2020 10:01 PM
I cannot open your file. Can you open the file which you uploaded in your last post?
Jul 12 2020 03:06 AM
I apologize again for the circumstances I am making. But the file is a bit strange ... it works in my PC and when I load it into the forum it doesn't work afterwards .... maybe because it is an .xlsm file ... don't know.
But now I hope to have charged it correctly, with me it opens without problems ... slowly but it opens.
As I said, I want the Working times that I enter in sheet "Januar" should appear yellow in sheet "IND1" ... in the correct place and date. It should only show the working hours in color ... only the working hours.
Examble: all fields from 10:00 to 17:00 must appear with color (the same color that the MA also has). All other cells should be without color in the same row. Other examble:
Jul 12 2020 03:14 AM
I downloaded the workbook from me first and then opened it. At the beginning it showed that it had a problem with Inquire. but after switching off the Inquire it works without problems. Send it again for security, no add-in inquire.
Jul 12 2020 04:08 AM
I am really sorry. Still unable to open any of your files, same issue.
Jul 12 2020 05:53 AM
Jul 12 2020 07:48 AM - edited Jul 12 2020 07:51 AM
Please find the attached with the tweaked code.
Please note that in order to make this code work properly, you will have to input "from" and "to" column headers for all the columns for all the employees in Row#9 on January Sheet i.e. if you enter a time in any cell and if the header for that column in Row#9 is not either from or to, the code will not work properly.
Also note that when you enter the from time for any employee, enter to time for that employee first before entering from time for another employee otherwise this code will not work.
I have also changed the employee name sequence in column E on IND1 Sheet as per the sequence of the employees entered in the range H1:Q1 on IND1 Sheet.
The timeline will be highlighted as per the color of the employee in the range H1;Q1 so you may change the color for a employee if you wish.
If in any case the timeline is not highlighted for some time values you enter on January Sheet, make sure to enter the same time in Row#8 on IND1 Sheet and reenter time on January Sheet again to highlight the timeline on IND1 Sheet.
You will also find a button called "Clear the Selected Timeline Cells" on IND1 Sheet.
You may use this button to clear the timeline if required and to do so, just select the cells on the timeline you want to remove the color from and click this button to clear the color from the selected cells on the timeline.
Jul 12 2020 08:13 AM
I have the feeling I'm too stupid to explain it correctly.
From sheet Januar, the times entered in sheet IND1 (as I sent it in the workbook) would like to be automatically displayed as a timeline in color ... the working time I entered in Januar ... not in IND1. Timeline is in IND1 From F to BA. Workingtime is in January from to and Duty 1,2 & 3.
Enter Time in Januar as a examble from 10:00 to 18:00 the corresponding timeline should appear in IND1 sheet with colour from 10:00 to 18:00.
I tried the file sent to me and does not work as it should ... if you could do something with what I could explain to you, it would be a great help ... if not, don't worry ... don't want to be a burden.
I can't get any further ... and at the same time I take up your time and your nerves too much.
Thank you anyway for everything
Nikolino
Jul 12 2020 08:21 AM
It is exactly doing what you described.
Look at the file I am attaching here.
The January Sheet has time values in I10:J10 and I24:J24 and now look at the IND1 Sheet, do you see the timeline highlighted in Row#10 and 164 for the employee JeWo?
Also, the timeline is highlighted with the color of employee JeWo set in the cell I1 on IND1 Sheet.
Isn't it what you are trying to achieve?
Jul 12 2020 08:55 AM
Jul 12 2020 01:56 PM
SolutionNo 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!