Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jul 2014
    Posts
    33
    Thanks
    7
    Thanked 3 Times in 3 Posts

    Populating multiple Time Pickers with a combo box

    Hi all,

    I've been attempting to create a userform for maintaining and updating a rota.

    I've created a userform where I've put shift patterns in, and there's a combo box that has a number of possible shift patterns.

    What I need that to do is populate 10 Time Pickers ( one start and end time for each day in a week ) with the information that relates to that shift pattern, so that the shift can be edited if needs be.

    Is this possible? Sample sheet will come a bit later on as I will need to take out all the names etc

    Regards

    R

  2. #2
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts
    Hi

    Yes, it is possible.
    Let us have your sample.

    zeddy

  3. #3
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,352
    Thanks
    49
    Thanked 275 Times in 253 Posts
    It would be helpful to also know what version Excel you are using

  4. #4
    Lounger
    Join Date
    Jul 2014
    Posts
    33
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Hi both,

    Thanks... Okay here we go

    I'm using excel 2010 at work and 2013 at home.

    Attached you can find the rota. I took out the names and replaced them with letters and numbers...

    In the column Agent_Shifts where there are letters, these are people who have fixed and peculiar shift patterns every week. So they need their own shift pattern. Where the time is shown in the format "0800-1600" this relates to mon-fri 8am-4pm.
    People without their own shifts are on a rolling rota and thus need to be edited, this is why I decided to put in the possible shift patterns which you can see the half finished table of ( i realised that my knowledge had come to an end and thus threw a hissy fit and tried here )

    There is some of the script that is missing in the userform, I had hardcoded it so that when the teams were selected it autopopulated the names into first drop down boxes and auto populated the shift patterns for the set shifts in the second drop down boxes.

    The Time Pickers to the right relate to monday to sunday and is what I need to populate.

    In the end I'd also like to make it submit to the raw_rota tab in a format that I can use to run against a logon report to provide a division wide adherance report.

    Any help here would be grand, I was really proud of my offset to populate the dropdown lists but then I realised i was way way out of my depth with it all!

    Thanks a lot

    R

    EDIT I'd also like to add that it's Rota Test 3 because Test 1 + 2 went so badly wrong that I had to scrap the sheet and start again...
    Attached Files Attached Files

  5. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,352
    Thanks
    49
    Thanked 275 Times in 253 Posts
    Rathril,

    I started working on this prior to you posting your sample but I think the same principle applies. By selecting the shift at the top, the time start/stop pickers adjacent to the right populate with the shift start and stop times. Each will allow for fine adjustment. Duplicate for each employee. Looking at your sample, this could be easily applied. I would highly suggest renaming the D/T pickers to the same name followed by a unique number so that you can cycle through them as shown below

    HTH,
    Maud

    timepicker.png

    Code:
    Private Sub ComboBox1_Change()
    '-------------------------------
    'COMBOBOX WITH SHIFTS
    Dim I As Integer
    '-------------------------------
    'SPLIT START AND STOP TIMES AND ASSIGN TO TIME PICKERS
    s = Split(ComboBox1.Value, " - ")
    For I = 0 To 9 Step 2
            Me.Controls("dpt" & I).Value = s(0)
            Me.Controls("dpt" & I + 1).Value = s(1)
    Next I
    End Sub
    
    Private Sub CommandButton2_Click()
    '-------------------------------
    'CLOSE FORM
    DPT.Hide
    End Sub
    
    Private Sub UserForm_Initialize()
    '-------------------------------
    'POPULATE COMBOBOX WITH SHIFTS
    With ComboBox1
        .AddItem "7AM - 3PM"
        .AddItem "7AM - 7PM"
        .AddItem "3PM - 11PM"
        .AddItem "3PM - 3AM"
        .AddItem "11PM - 7AM"
        .AddItem "11PM - 11AM"
    End With
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-11-26 at 16:18. Reason: add file

  6. #6
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,352
    Thanks
    49
    Thanked 275 Times in 253 Posts
    Rathril,

    On your form, I would swap out the cboAgent comboboxes for textboxes. The following code will populate the textboxes according to which contact you choose. Your code can be greatly condensed by looping through the textboxes. Could you please explain how you want the shift combo boxes to populate? As you have it setup now, each will be assigned a shift value. Instead, do you want them each to populate with all the selections you had indicated for that specific contract that you can assign to each agent?

    HTH,
    Maud

    Rathril3.png

    Code:
    Private Sub cboContract_Change()
    Dim ws As Worksheet
    Set ws = Worksheets("Agents_List")
    Dim rngAgent As Range
    For I = 1 To 15
        Me.Controls("TextBox" & I).Value = ""
    Next I
    For I = 1 To 15
        Me.Controls("Shift" & I).Value = ""
    Next I
    Select Case cboContract.Value
        Case ""
            Exit Sub
        Case "LBG"
            col = 3
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
        Case "CSC"
            col = 4
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
            Shift1.Value = "0800-1600"
            Shift2.Value = "0930-1730"
            Shift3.Value = "1000-1800"
            Shift4.Value = "1000-1800"
            Shift5.Value = "0800-1600"
            Shift6.Value = "0900-1700"
            Shift7.Value = "1000-1800"
        Case "Ladbrokes"
            col = 5
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
            Shift1.Value = "0730-1530"
            Shift2.Value = "0800-1600"
            Shift3.Value = "0830-1630"
            Shift4.Value = "0900-1700"
            Shift5.Value = "1000-1800"
            Shift6.Value = "0900-1700"
            Shift7.Value = "0800-1600"
        Case "E+"
            col = 6
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
            Shift1.Value = "0800-1600"
            Shift2.Value = "0800-1600"
            Shift3.Value = "0900-1700"
            Shift4.Value = "0900-1700"
            Shift5.Value = "1000-1800"
        Case "TSB/CCM"
            col = 7
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
            Shift1.Value = "0800-1600"
            Shift2.Value = "0900-1700"
            Shift3.Value = "1000-1800"
            Shift4.Value = "0830-1630"
            Shift5.Value = "1000-1800"
        Case "Sky"
            col = 8
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
            Shift1.Value = "0600-1400"
            Shift2.Value = "0730-1530"
            Shift3.Value = "0800-1600"
            Shift4.Value = "0830-1630"
            Shift5.Value = "0930-1730"
            Shift6.Value = "1000-1800"
        Case "COOP"
            col = 9
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
            Shift1.Value = "0800-1600"
            Shift2.Value = "0900-1700"
            Shift3.Value = "0900-1700"
            Shift4.Value = "0930-1730"
            Shift5.Value = "1000-1800"
        Case "CIT"
            col = 10
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
            Shift1.Value = "0800-1600"
            Shift2.Value = "0800-1600"
        Case "MMC"
            col = 11
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
            Shift1.Value = "0900-1700"
        Case "Voda/NWR"
            col = 12
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
            Shift1.Value = "0730-1530"
            Shift2.Value = "0800-1600"
            Shift3.Value = "0830-1630"
            Shift4.Value = "1000-1800"
            Shift5.Value = "0800-1600"
            Shift6.Value = "1000-1800"
            Shift7.Value = "0800-1600"
            Shift8.Value = "0900-1700"
        Case "40/40"
            col = 13
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
        Case "OOH"
            col = 14
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
        Case "AB/Cap"
            col = 15
            For I = 2 To ws.Cells(Rows.Count, col).End(xlUp).Row
                Me.Controls("TextBox" & I - 1).Value = ws.Cells(I, col)
            Next I
            Shift1.Value = "0800-1600"
            Shift2.Value = "0900-1700"
            Shift3.Value = "1000-1800"
    End Select
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-11-26 at 16:03. Reason: added file

  7. #7
    Lounger
    Join Date
    Jul 2014
    Posts
    33
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Hi Maudibe,

    Populating them with the shifts for each contract would probably be a much better solution...
    As for adding the Agents as labels, with the dynamic range set that would be easy to update just by adding/ taking away from the range I assume which means there wouldn't be any reason for the user to change them so I think that would be much better. I presume that the same procedure I'd set up to populate the combo boxes would work for Labels.

    I'm heading to the office early tomorrow so I'll have a play with all you've done so far and let you know how good it is!

    Thanks a lot, I really appreciate it. The part where you've defined the combo boxes as a variable list was very enlightening, I thought it would be possible but stuff like that I guess you need to see to be able to use, I would never have figured out this line on my own!
    Code:
    For I = 1 To 15
        Me.Controls("Shift" & I).Value = ""
    R

  8. #8
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,352
    Thanks
    49
    Thanked 275 Times in 253 Posts
    I'm heading to the office early tomorrow so....
    Rathril,

    I was puzzled why you would be working tomorrow then I realized that Thankgiving Day is strictly an American holiday and not celebrated by the Brits...duh!

    I continued to do some more work to connect the time pickers. Because there are 7 days with 15 agents, the amount of coding would have been enormous. To reduce the load, I renamed all the time pickers to allow looping through them. Another issue was that the formatting for the shift had to be changed to a time format for the time pickers to work. Therefore, on the Agent_List worksheet and throughout the code the shift format had to be modified:

    ex. 0700-1900 had to be changed to 07:00-19:00

    Overview: When the shift is split to a start time (07:00) and a stop time (19:00), the times are properly recognized by the time pickers. So, on the form, if you were to select a contract, the agents from the contract column on the agents_List sheet will populate the agent textboxes. The adjacent shift comboboxes will start with a shift that you had indicated in your code (although I don't have a handle of how you are assigning them) or you could assign them a shift from the dropdown. For each day of the week the start/stop times will change to match the shift. Each start and stop time can be edited from that point.

    Consideration: Shifts that you designated by a letter (a - ak) will cause an error because they cannot be converted to a start/stop time in a time format. I would suggest to assign them actual shift time ranges or set them to some default shift.

    If you need any changes, just let me know

    Good Luck,
    Maud
    Attached Files Attached Files

  9. #9
    Lounger
    Join Date
    Jul 2014
    Posts
    33
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Hi,
    Yes I'm english, though believe me I would love to live in America!

    This actually got sidelined by a more important project but I will be getting back to it.

    I think that we would have to define each shift pattern for everyone that has their own set shifts then, I think that would be the simplest solution anyway!

    Looking at the spreadsheet it looks really good so far, I'm going to put this in with the names now and work on the submit function

    Thanks a lot Maudibe!

    R

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •