SOLVED

entering the working time, automatically marked with color in the timeline.

Gold Contributor

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?

 TEST Sheet 

 

Ps. My knowledge in Excel is not the best, but my knowledge in VBA is almost non-existent :(
 

Any help is welcome

Thx in Advance

Nikolino

35 Replies

@NikolinoDE 

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?

Hi Subodh_Tiwari_sktneer,
sorry for my mistake, wrong file :(

here is the right file :)
https://www.transfernow.net/mK46HT072020

Please download the file and my project is then self-explanatory :)

Thx in Advance for any help.
Nikolino

@NikolinoDE 

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.

 

 

Hi Subodh Tiwari , thanks in advance for your help and effort.

Downloaded it, but it doesn't work. With MA1 line, the entire line turns yellow and not the specified time entry.
Maybe I'm doing something wrong, just don't know what ...

For MA1, only the time entry from 6:00 a.m. to 2:00 p.m. should be yellow, the rest of the row must not be colored. This should be with every MA, so that you can see the daily occupancy.

Supplementary question: could the problem be accomplished without an array formula?
Think that with Aray formulas the workbook will be very slow if you consider 12 months with 10 MA .... Don't know, it's just a question of a beginner like me.

This does not mean that I could not live with the proposed solution, on the contrary :-), would only work once.
Thanks again, i appreciate it knows how much time and patience it takes.

Nikolino

@NikolinoDE 

 

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

@NikolinoDE 

 

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.

 

@Subodh_Tiwari_sktneer 

 

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

@NikolinoDE 

 

Hi,

again with the old sheet ... I labeled it better.
Sry ... try to show what I want to do because my english is not the best.
Believe this sheet shows best what I want to do

@NikolinoDE 

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.

 

 

@Subodh_Tiwari_sktneer 

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.

@NikolinoDE 

I cannot open your file. Can you open the file which you uploaded in your last post?

@Subodh_Tiwari_sktneer 

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:

The colleague (MA1) has duty 1 from 10:00 a.m. to 2:00 p.m. and duty 2 from 4:00 p.m. to 6:00 p.m. All cells from 10:00 to 14:00 should be colored and all cells from 16:00 to 18:00 should be colored. In between as well as therefor and afterwards all cells should be in the same row without color.
As I said my English is not the best ... let my VBA :) ... I appreciate your help ... just have to help me get a result:-).
 
Thx
Nikolino
 

@NikolinoDE 

 

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.

 

@NikolinoDE 

I am really sorry. Still unable to open any of your files, same issue.

hope i have solved it now ... once again for the circumstances. If it doesn't work now :( i dont know any solution after that...

https://www.transfernow.net/WFZIs7072020
put it online for download in another page

@NikolinoDE 

 

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.

 

 

@Subodh_Tiwari_sktneer 

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

@NikolinoDE 

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?

 

Colleague JeWo
Sheet "January" working time entry in: I10: J10 and L10: M10 and O10: P10.
Should all automatically appear as a timeline in "IND1" from F10 to BA10.
But do not appear.
Should have a timeline from 10:00 to 15:00 in sheet IND1 (which is also) and a second timeline (color) from 17:00 to 21:00 as well as from 22:00 to 00:00 (= 24:00 ) have an additional timeline.
Only have a timeline from 10:00 a.m. to 3:00 p.m. in the workbook you sent, the other times cannot be seen in IND1.

Am I doing something wrong or thinking wrong?
As I said ... have been attached to this task for some time and just can't get any further ... if possible you could show me the way for a colleague.
Thank you again ... hope not annoy you with so much thank you :)
1 best response

Accepted Solutions
best response confirmed by NikolinoDE (Gold Contributor)
Solution

@NikolinoDE 

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!

View solution in original post