none
Dynamic List Depending on Available Dates

    Question

  • I have a worksheet that lists employees in a single column. (D8:D61). There are dates in the remaining columns (E1:LM1) through the beginning of next year. For each employee in Column D, I have the cell filled with a background color under the dated header column to show they're on shift  to work (no fill to show they're off).

    I would like to be able to type in a start date (sheet2!A2) and end date(sheet2!B2) in sheet2 that would populate a list of which employees are available (sheet2!C2) during those dates.

    It could be a macro with start and end dates populating a list of who is available too, but If i could get the code to work on sheet2, then I could probably make the macro myself.

    The color code for the background fill when available is: bfbfbf (medium grey)

    There is no fill when they are not available

    The fill is either green or yellow on the day they arrive or depart ( that can change if it complicates the problem at hand)

    I was able to use a UDF of countcolorif to count the employees who are available, and now need to populate a list by a date range. The shading(availability) could change at anytime if an employee stays longer or has to leave early.


    Tuesday, March 06, 2018 6:07 PM

Answers

  • Hi dross85,

    I have only done the list so far and have uploaded it to OneDrive at the link below for you to test.

    Not sure why you had split panes on Crew sheet and Page Layout on List Sheet. I turned split panes off on the Crew sheet and then used Freeze Panes at cell E8. Also I changed the View on Sheet List to Normal. You can change them back to your way if you want to.

    The majority of the code is in the List Worksheet Module. Not sure of your expertise here but just in case you need it, if you Right click the View worksheet tab name and select View code it will open the worksheet's module. Alternatively you can double click the worksheet name in the VBA Project Explorer (left column) to view the List worksheet module.

     I also inserted a UDF in Module1 for finding the last used row on Crew.

    When the List sheet is Activated, event code (WoksheetActivate) runs to create a list Data Validation in cell A2 (Start Date) which is the dates from Row 1 of Crew sheet.(No problem using a horizontal list for the Data Validation).
    When a Start Date is then selected in cell A2, event code (Worksheet_Change) runs to create Data Validation for End Date in cell B2. However, End Date list starts at the same date that was selected in Start Date so that a valid selection will always be equal to or greater than Start Date.

    After entering the Start and End dates click Find Available and the list of Available will be created in column C.

    If new dates are entered then any previous output is cleared and the code runs to re-populate the Available column.

    The list is only created for the grey cells. At this point the Orange and Green cells are omitted. Is this correct? I already have a line of code commented out that will allow the orange and green cells to be counted it they are meant to be counted.

    I have been referencing the color here as orange although I see in your text that you call it yellow. I think it is nearer an orange color but it doesn't really matter.

    What are the rules to determine whether the first and last day of available should be Orange or Green? I need to know for the next part of the project so that I can set up the Userform to use the correct color.

    I would also appreciate your thoughts on how you want to present the Userform for editing the Start and End for each employee.

    There are a number of Starts and Ends for each employee so how do you anticipate that the user will be able to determine which ones are be edited and/or deleted.

    I see it as being somewhat complex to select and edit on a Userform.

    I am wondering if we need to select an employee and then list all of Start and End dates for the shifts for the entire period represented on Crew worksheet. Then the user to then select a specific shift and edit the start and end.

    When start and/or end dates are edited, is it likely that the new dates will overlap end of one existing shift and start of another existing shift effectively making one very long shift.

    Anyway test the list part of the project and get back to me with your thoughts about the design of the next part of the project.

    Link to Example workbook.

    https://1drv.ms/u/s!ArAXPS2RpafCl0pdA9gfA0bF5XSu


    Regards, OssieMac

    • Marked as answer by dross85 Friday, March 09, 2018 6:18 AM
    Thursday, March 08, 2018 11:58 AM
  • Progress report.

    I am looking at a Userform like the screen shot below. The actual layout is irrelevant because you can move the controls around to where ever you want them on the Userform. (I have squeezed them up to tall and narrow so the screen shot will fit in the editor of this forum).

    User selects Employee.

    ListBox is populated with all shifts.

    User enters new Start and End dates. (Combo boxes to select dates so they will be valid similar to the Validation on the List worksheet).

    Then the user makes the decisions on color. There are 3 sets of Option buttons that cover all the colors you have used on the worksheet. The button groups are horizontal. (If you don't want all the colors or you want more colors then let me know. )

    The options in the screen shot are Start and End dates Yellow with Grey in between.

    If User wants all grey then select grey options for all 3.

    If User wants all red then select red for all 3.


    Regards, OssieMac

    • Marked as answer by dross85 Friday, March 09, 2018 6:18 AM
    Friday, March 09, 2018 4:59 AM
  • I have not been able to figure out how to only have those 5 option buttons do all the re-formatting. Still working on that challenge.

    I have had a bit of free time this morning so I had a look at this problem.

    The code is written with Uerform Control Names as per the Screen Shot. You will need to either edit the code so it all matches the control names you have used or edit your control names on your Userform to match the names I have used. (I think the latter will be easier but it's your choice).

    I have inserted the control names as captions on the controls because it is the easiest method to explain and also easy when writing code if you print it and have it beside you. (You don't need to leave the captions in your project). I used white font on the red because black on red is hard to read.

    Take note that in the Select Case portion of the UDF code to identify matching option buttons is case sensitive so you need to use the exact same case to match the option buttons as used when you name them on the Userform.

    For interest, note how to return an array from a UDF. Can't dimension the UDF name as an array but can create another separate array and assign it to the UDF name which then becomes an array. Dimension the actual UDF as Variant.

    In the Userform Module "Sub UpdateRecord(bolDelete As Boolean, bolAddShift As Boolean)"

    Add the following Dim Statement

    Dim arrSampColors As Variant

    Delete the following 3 lines of code (towards the top under the Dim statements and the line assigning the worksheet to a variable)

        lngGrpArrivColor = OptBtnColor(Me, "grpArrivColor")     'Get the color of the selected OptionButton for Arrive Date
        lngGrpDeptColor = OptBtnColor(Me, "grpDeptColor")       'Get the color of the selected OptionButton for Depart Date
        lngGrpBetween = OptBtnColor(Me, "grpBetween")           'Get the color of the selected OptionButton for Between Start Date and End Date.

    Insert the following code in lieu of the deleted code.

        arrSampColors = setColor(Me)    'Assign the 3 colors for Arrive, Between and Depart to an array
       
        If Not (IsEmpty(arrSampColors)) Then        'Will be empty if User did not select a color combination
            'Assign the inidividual elements of the array to the variables used in the code.
            lngGrpArrivColor = arrSampColors(1)
            lngGrpBetween = arrSampColors(2)
            lngGrpDeptColor = arrSampColors(3)
        End If

    In Module1, Delete the UDF  "Function OptBtnColor(frm As UserForm, strGrp As String) As Long"

    Insert the following UDF in lieu of the deleted UDF

    Function setColor(frm As UserForm) As Variant
       
        Dim ctrl As Control
        Dim arrColor(1 To 3)  'Dimension one based, 3 element array
       
        For Each ctrl In frm.Controls
            If TypeName(ctrl) = "OptionButton" Then
                If ctrl = True Then
                    Select Case ctrl.Name
                        Case "opt_1"        'Note the text comparison is Case sensitive
                            arrColor(1) = frm.lblArr_1.BackColor
                            arrColor(2) = frm.lblBtwn_1.BackColor
                            arrColor(3) = frm.lblDept_1.BackColor
                        Case "opt_2"
                            arrColor(1) = frm.lblArr_2.BackColor
                            arrColor(2) = frm.lblBtwn_2.BackColor
                            arrColor(3) = frm.lblDept_2.BackColor
                        Case "opt_3"
                            arrColor(1) = frm.lblArr_3.BackColor
                            arrColor(2) = frm.lblBtwn_3.BackColor
                            arrColor(3) = frm.lblDept_3.BackColor
                        Case "opt_4"
                            arrColor(1) = frm.lblArr_4.BackColor
                            arrColor(2) = frm.lblBtwn_4.BackColor
                            arrColor(3) = frm.lblDept_4.BackColor
                        Case "opt_5"
                            arrColor(1) = frm.lblArr_5.BackColor
                            arrColor(2) = frm.lblBtwn_5.BackColor
                            arrColor(3) = frm.lblDept_5.BackColor
                    End Select
                    'After a True is found, colors are all set
                    'so assign the array to the UDF Name and Exit the Loop
                    setColor = arrColor
                    Exit For
                End If
            End If
        Next ctrl
    End Function


    Regards, OssieMac

    • Marked as answer by dross85 Thursday, March 29, 2018 8:49 PM
    Wednesday, March 14, 2018 12:12 AM
  • I have uploaded a new version of the Example file with the new code to the following link.

    Removing the old option buttons also required deleting some code attached to them.

    https://1drv.ms/u/s!ArAXPS2RpafCl02EDS5rQ5xZWSpJ


    Regards, OssieMac

    • Marked as answer by dross85 Thursday, March 29, 2018 8:49 PM
    Wednesday, March 14, 2018 2:38 AM
  • I have deleted my previous post because I believe I have now emulated the problem and hopefully fixed the problem so download the new workbook at the link below and test.

    The problem was in Sub UpdateRecord(bolDelete As Boolean, bolAddShift As Boolean). I won't go into individual lines of code. Just replace all of the sub with the new sub.

    https://1drv.ms/u/s!ArAXPS2RpafCl1edW795om7EmBz0


    Regards, OssieMac

    • Marked as answer by dross85 Friday, March 30, 2018 7:25 PM
    Friday, March 30, 2018 8:01 AM

All replies

  • I would like to be able to help you but unfortunately there is not enough information. Can you upload a copy of your workbook to OneDrive. If you have sensitive data in the workbook then replace with dummy data. eg. Employee names could be characters of the alphabet combined with numerics.

    It appears that you have attempted to upload screen shots pictures and they have failed. To do this you need to use the "Insert image" icon in the toolbar at the top of the editor box. Hover the cursor over the icons in the toolbar to determine what they are.

    Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Select "Get a Link" from the popup menu.
    10. Click in the field displaying the link and it should highlight and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)

    Regards, OssieMac

    Wednesday, March 07, 2018 5:03 AM
  • Thank you OssieMac-

    You have helped before, I'm sure you can lead me in the right direction again. It's people like you that restore my faith in humanity!. Sorry for the broken images, guess this website just wont support them to some viewers. Here is a link to my zipped file that I made with dummy data. Keep in mind I will have many more employees than are in this file.

    Again here are some of my key needs:

    Be able to input a date range on the second sheet where a list of available workers will populate.

    On the first sheet, those shaded areas are subject to change at any time. They may be colored red, rendering them unavailable completely, or as you can see with the green and yellow...those are the days where they arrive or depart. The green is a crew change where that person will not be available until the next day, the yellow is a lead change where that person is available to work. The medium grey shows when the people are available , no fill means they're not.

    In addition to being able to populate this list, I would also like to be able to use a macro to easily change availability for each employee. eg. If they were available from 3/7-4/6 but now are only available until 3/29, or maybe instead of coming in on 3/9 they need to be changed to 3/12. i would like a user friendly form to change that, adding or removing the appropriate shading into the spreadsheet.

    Hope we can figure this out!

    https://1drv.ms/u/s!ArTfUcJS-eVihlrrErkosIfy-NgV


    • Edited by dross85 Wednesday, March 07, 2018 4:19 PM
    Wednesday, March 07, 2018 4:15 PM
  • I'll have a look at this during today. We are obviously in different time zones. My part of the world it is currently 6:30am 8 March 2018

    Regards, OssieMac

    Wednesday, March 07, 2018 8:30 PM
  • Hi dross85,

    I have only done the list so far and have uploaded it to OneDrive at the link below for you to test.

    Not sure why you had split panes on Crew sheet and Page Layout on List Sheet. I turned split panes off on the Crew sheet and then used Freeze Panes at cell E8. Also I changed the View on Sheet List to Normal. You can change them back to your way if you want to.

    The majority of the code is in the List Worksheet Module. Not sure of your expertise here but just in case you need it, if you Right click the View worksheet tab name and select View code it will open the worksheet's module. Alternatively you can double click the worksheet name in the VBA Project Explorer (left column) to view the List worksheet module.

     I also inserted a UDF in Module1 for finding the last used row on Crew.

    When the List sheet is Activated, event code (WoksheetActivate) runs to create a list Data Validation in cell A2 (Start Date) which is the dates from Row 1 of Crew sheet.(No problem using a horizontal list for the Data Validation).
    When a Start Date is then selected in cell A2, event code (Worksheet_Change) runs to create Data Validation for End Date in cell B2. However, End Date list starts at the same date that was selected in Start Date so that a valid selection will always be equal to or greater than Start Date.

    After entering the Start and End dates click Find Available and the list of Available will be created in column C.

    If new dates are entered then any previous output is cleared and the code runs to re-populate the Available column.

    The list is only created for the grey cells. At this point the Orange and Green cells are omitted. Is this correct? I already have a line of code commented out that will allow the orange and green cells to be counted it they are meant to be counted.

    I have been referencing the color here as orange although I see in your text that you call it yellow. I think it is nearer an orange color but it doesn't really matter.

    What are the rules to determine whether the first and last day of available should be Orange or Green? I need to know for the next part of the project so that I can set up the Userform to use the correct color.

    I would also appreciate your thoughts on how you want to present the Userform for editing the Start and End for each employee.

    There are a number of Starts and Ends for each employee so how do you anticipate that the user will be able to determine which ones are be edited and/or deleted.

    I see it as being somewhat complex to select and edit on a Userform.

    I am wondering if we need to select an employee and then list all of Start and End dates for the shifts for the entire period represented on Crew worksheet. Then the user to then select a specific shift and edit the start and end.

    When start and/or end dates are edited, is it likely that the new dates will overlap end of one existing shift and start of another existing shift effectively making one very long shift.

    Anyway test the list part of the project and get back to me with your thoughts about the design of the next part of the project.

    Link to Example workbook.

    https://1drv.ms/u/s!ArAXPS2RpafCl0pdA9gfA0bF5XSu


    Regards, OssieMac

    • Marked as answer by dross85 Friday, March 09, 2018 6:18 AM
    Thursday, March 08, 2018 11:58 AM
  • OssieMac-

    This is great! So far so good! I do like the code you have written for me. I have been playing with it, and looks like I need to adjust the coloring in the Crew Tab to my needs. I think I will need to count (sometimes) those who are colored on the beginning or the end. The purpose for the coloring is because that person is either starting or ending their availability, the color just depends on if they arrive/depart early or late. If they arrive early, they are available, if late they are not. I  should be able to work that out with what you gave me.

    As far as the Userform goes, I have been trying to write one as you described. One that when you choose a specific employee, it will show the current (closest to today's calendar day) start and end date. The user could then change the populated dates and the fill would either lengthen or shorten to the new range. I imagine it's possible but complex to have a "next" button that would look for the 2nd start and finish. Going beyond the 2nd isn't necessary to have on the form when it could be manually changed on the sheet if it is something very far in the future. Most changes either happen within the current date range, or the following date range.

    Again, I am elated you got me this far. If the Form is something you do not want to take on I completely understand. I am learning so much from the codes you write it's extremely helpful and I'm very greatful that you've done this much for me this far.

    Please let me know if I can clarify anything else.


    Thursday, March 08, 2018 11:38 PM
  • I have actually made a start on the Userform to change the shifts. Amazing the ideas one gets if one thinks about it enough.

    I have a CombBox to select the Employee.

    When an employee is selected, I am then populating a 2 column ListBox with start and end of each shift from the row of the selected employee. Start and End at this point is if the interior is colored irrespective of the actual color because on your example of the userform you have arrive and depart which I assumed is the yellow or green etc and should be included.

    The user can then select the shift to edit. The new start and end can then be inserted in the appropriate text boxes.

    Updating will involve delete the original shift in total and inserting the new shift.

    Are there likely to be instances where the edited new shift will over lap the start or end of another existing shift? If so, I need to decide how to handle this.

    Screen shot of the Userform so far


    Regards, OssieMac


    • Edited by OssieMac Friday, March 09, 2018 12:30 AM
    Friday, March 09, 2018 12:29 AM
  • Yes! I like what you're thinking, a lot of what was in my head...I just could not express it. Amazing how our minds work.

    Indeed you're correct. There are instances where the work will overlap as well as be removed completely.

    eg. Rod may cover for a coworker for 2 weeks, therefore he will be arriving 3/16 and departing 4/27. Its also a possibility that because he worked to cover, he may work 3/16 to 4/20 instead of his 'normal' 4/27.

    eg. Brad may be scheduled to work 3/30 to 4/13, but he called and won't be available. It would be nice to show that it was his normal schedule, but possibly have an option to color in red showing there was an issue. (rendering him unavailable in the list & count)

    Again, Thank you SO much for all this help. This is going to do wonders.

    Friday, March 09, 2018 4:15 AM
  • Progress report.

    I am looking at a Userform like the screen shot below. The actual layout is irrelevant because you can move the controls around to where ever you want them on the Userform. (I have squeezed them up to tall and narrow so the screen shot will fit in the editor of this forum).

    User selects Employee.

    ListBox is populated with all shifts.

    User enters new Start and End dates. (Combo boxes to select dates so they will be valid similar to the Validation on the List worksheet).

    Then the user makes the decisions on color. There are 3 sets of Option buttons that cover all the colors you have used on the worksheet. The button groups are horizontal. (If you don't want all the colors or you want more colors then let me know. )

    The options in the screen shot are Start and End dates Yellow with Grey in between.

    If User wants all grey then select grey options for all 3.

    If User wants all red then select red for all 3.


    Regards, OssieMac

    • Marked as answer by dross85 Friday, March 09, 2018 6:18 AM
    Friday, March 09, 2018 4:59 AM
  • Excellent! That looks excellent! You my friend are a gentleman and a scholar. I've got a lot to learn to someday reach your level!

    When you select the shift, does it populate the edit combobox?

    I do not think I will need the last color(light blue) at all.

    I will not need the orange or green as a between option because that will strictly be for arrive and depart.

    Keep me posted when you have a link to the code for this form, I'm excited to test it out and take a look at your work!

    • Edited by dross85 Friday, March 09, 2018 6:31 AM
    Friday, March 09, 2018 6:18 AM
  • When you select the shift, does it populate the edit combobox?

    I do not think I will need the last color(light blue) at all.

    I will not need the orange or green as a between option because that will strictly be for arrive and depart.

    The 2 combo boxes will initially populate with the selected Shift Arrive and Departure dates and then the user can edit as required by selecting new date/s.

    I will delete the 3 Light Blue buttons plus the Orange and Green buttons from the between line. I'll leave the red and mid blue buttons as they are in the matrix.

    Do you have a deadline for this? I am hoping not. I will work on it a little over the weekend but I also have some other commitments and then mid next week I am going to be away for a week or so visiting my Grand Children and they take priority.


    Regards, OssieMac

    Friday, March 09, 2018 7:04 AM
  • Sounds like you know just what is needed. I cant even express the help you have been. I do not have a hard deadline, but would like to give it a look, try to code some of it and attempt to adapt it into my worksheets. If you need more time, you have it. If you have a version you would allow me to work from, just let me know.

    I will work on trying to get my outlook app to notify me when there is a reply here as I have not been receiving emails or notifications when this thread is added to.

    I am very grateful you've been this helpful so far. I can only hope that I'll be able to help someone to this extent someday as a pay it forward for what I owe you.

    Enjoy your own time and especially your family!... I will keep refreshing and checking for your updates.

    Friday, March 09, 2018 3:45 PM
  • I managed to get a fair bit done over the weekend. I have uploaded to the following link what I have done so far.

    https://1drv.ms/u/s!ArAXPS2RpafCl0uot9roKYkRhB_t

    Here are a few notes.

    • Cannot add new employees via the Userform as yet.
    • Cannot insert New/Additional Shifts via the Userform as yet.
    • Can edit the Arrive and Depart dates of the existing shifts.
    • The code removes all of the interior colors and diagonal borders from both the old range and the new range. That way we have a clear row from start to end to insert the modified shift. It then colors all of the new range to the between color.
    • Then it finds the first cell of the new range and tests the cell offset one cell to the left. If the cell to the left contains any color then the assumption is that it joins the previous shift so the previous cell is also colored to the between color and remove diagonal border (if any).
    • A similar method is used for the end cell but tests the cell to the right of the last cell of the new Shift range.
    • If cell to the right or left of the new range is no color then the end cells are set to the Arrive/Depart colors and the diagonal border inserted.
    • On the Userform, I added a sample of the color scheme selected from the option buttons. When you show the Userform, try selecting different combinations of the colors so that you will know that you are getting the required combination of colors.
    • I made some alterations to your UDF to count the colors in rows 2:6. There is a strange problem with it because it keeps returning errors in the formulas and I am assuming that is why you have added the code to copy and paste the formulas to force them to update. I believe that I have previously seen questions in the forums relating to similar problems whare that are no actual values in the cells.
    • I don't know why you were using the Optional False parameter in the UDF and then in the formula on the worksheet you were setting the optional parameter to False. It does not do anything. I have removed it from the UDF and also from the formulas because I was hoping it might rectify the problem but it didn't.
    • See the comments in the code to copy and paste the formulas and force them to update. You will see that I am also calling the Copy/Paste code from strategic points in the other code.
    • I have set it up with Freeze panes at cell E8 so that rows 1:6 are always visible and columns B:D are always visible. You will need to remove the Freeze panes if you want to alter it so column A is also always visible and then reset the Freeze panes.
    • When you edit a Shift, when it is finished it scrolls the screen so the first cell of the Shift is at the top left of the Bottom right window of the screen. Makes it easy to find and check if it is correct. If you don't want it then find the line of code and comment it out. It is the last 2 lines of "Sub UpdateRecord(bolDelete As Boolean)" in the Userform module.
    • If you are doing anything with changing the color scheme and you need to reset the colors of the options button BackColor property, then you need to be able to identify the Hex value of the color on the worksheet and format it as required for the BackColor property. It is not a straight Hex conversion. It needs to be in the format that you will see with the leading and trailing ampersands and also the H at the beginning of the code.
    • To do this, I have inserted a UDF called "Function CtrlHexColor(rng As Range)". Enter the function in any blank cell on the worksheet and use any colored cell on the worksheet for the parameter like the following where Q38 is the cell containing the fill color.
    • =CtrlHexColor(Q38)
    • Copy the cell and paste special values and then you can copy the result and paste it into the property value on the Design mode dialog.

          Anyway have a look at what I have done so far and see if you think I am headed in the right direction.


    Regards, OssieMac

    Sunday, March 11, 2018 11:29 AM
  • Following is the next version that includes functionality to add a Shift.

    https://1drv.ms/u/s!ArAXPS2RpafCl0xKhVfTlmMgAHpx

    I have not done anything with adding an Employee. I wonder if it is worth it because it only involves inserting the name at the bottom of the table or inserting a line if required up between specific other employees.

    You need to be aware that because it is a table, it tends to inherit some formatting so you might need to select the remainder of the line after the name and set the Fill to "No Fill".

    I have done some testing but not to the nth degree. Test and see how it goes and let me know if any problems.

    I am planning on going away and not being available until towards the end of next week. I have some other commitments for the next couple of days and then plan on being away for a week or so. However, this could plan could change. We have a forecasted low pressure system off the coast that could become a cyclone and if that occurs then the trip away will be cancelled/postponed.


    Regards, OssieMac

    Monday, March 12, 2018 7:02 AM
  • OssieMac-

    I do not think its worth it to add an employee, this is not to my concern and can easily be done in the spreadsheet if or when that will happen. I have actually removed the table from the crew sheet on my actual workbook where the sheet is named Employee Schedule. That was from a previous attempt and I forgot to return it to a range before I shared the file.

    I have tested on BlankSched_3, and will check out #4 today when I have some time. It is awesome so far, but I haven't tested it all the way either. Just to note- I was thinking that instead of having the option to make all these color changes, to simplify user friendliness if we were to only have 5 choices with a single option button for each example. This could rule out several buttons and your sample display even though they are a great idea if there were to be a lot of choices.

    . See this snipit from my actual userform I've built to use your code.

    Im also working on adding another 2 text boxes to populate on a sheet when an update is made so the changes can be noted and recorded. This form is amazing, and I really am happy with how you have made it. Please take caution to the weather, hopefully it doesn't cancel your plans or time with the family!

    dross85

    Monday, March 12, 2018 3:22 PM
  • I like the idea of being able to preview the complete color combinations instead of selecting first and only see the complete color combination after selecting.

    Something else I intended to mention is the Sum formula in row 6. First one is =SUM(E3). If it is supposed to be a Total I would have thought it should be =SUM(E3:E5).

    I thought that maybe I did something to change it but I went back to your original worksheet (I never change the original download) and it contains just the one cell.


    Regards, OssieMac

    Monday, March 12, 2018 8:38 PM
  • Thanks, I have accomplished my goal of adding your code to my master sheet to get it running well. I have added in my extra text boxes to note and record updates to the schedule, that is working.

    You read that row 6 code correct. I have that row in there for being connected to another sheet elsewhere that is used. It may be confusing, but it works its purpose. So it will stay.

    Everything is working very well, ive been testing the forms and transferring the codes into my master sheet to further the testing. Very pleased

    I would eventually like the ability to jump to today's date when the worksheet gets activated, that way the dates do not have to be deleted, and all records of the year can be kept.

    I have not been able to figure out how to only have those 5 option buttons do all the re-formatting. Still working on that challenge.

    Thank you very much for your continued support!

    Tuesday, March 13, 2018 6:15 PM
  • I have not been able to figure out how to only have those 5 option buttons do all the re-formatting. Still working on that challenge.

    I have had a bit of free time this morning so I had a look at this problem.

    The code is written with Uerform Control Names as per the Screen Shot. You will need to either edit the code so it all matches the control names you have used or edit your control names on your Userform to match the names I have used. (I think the latter will be easier but it's your choice).

    I have inserted the control names as captions on the controls because it is the easiest method to explain and also easy when writing code if you print it and have it beside you. (You don't need to leave the captions in your project). I used white font on the red because black on red is hard to read.

    Take note that in the Select Case portion of the UDF code to identify matching option buttons is case sensitive so you need to use the exact same case to match the option buttons as used when you name them on the Userform.

    For interest, note how to return an array from a UDF. Can't dimension the UDF name as an array but can create another separate array and assign it to the UDF name which then becomes an array. Dimension the actual UDF as Variant.

    In the Userform Module "Sub UpdateRecord(bolDelete As Boolean, bolAddShift As Boolean)"

    Add the following Dim Statement

    Dim arrSampColors As Variant

    Delete the following 3 lines of code (towards the top under the Dim statements and the line assigning the worksheet to a variable)

        lngGrpArrivColor = OptBtnColor(Me, "grpArrivColor")     'Get the color of the selected OptionButton for Arrive Date
        lngGrpDeptColor = OptBtnColor(Me, "grpDeptColor")       'Get the color of the selected OptionButton for Depart Date
        lngGrpBetween = OptBtnColor(Me, "grpBetween")           'Get the color of the selected OptionButton for Between Start Date and End Date.

    Insert the following code in lieu of the deleted code.

        arrSampColors = setColor(Me)    'Assign the 3 colors for Arrive, Between and Depart to an array
       
        If Not (IsEmpty(arrSampColors)) Then        'Will be empty if User did not select a color combination
            'Assign the inidividual elements of the array to the variables used in the code.
            lngGrpArrivColor = arrSampColors(1)
            lngGrpBetween = arrSampColors(2)
            lngGrpDeptColor = arrSampColors(3)
        End If

    In Module1, Delete the UDF  "Function OptBtnColor(frm As UserForm, strGrp As String) As Long"

    Insert the following UDF in lieu of the deleted UDF

    Function setColor(frm As UserForm) As Variant
       
        Dim ctrl As Control
        Dim arrColor(1 To 3)  'Dimension one based, 3 element array
       
        For Each ctrl In frm.Controls
            If TypeName(ctrl) = "OptionButton" Then
                If ctrl = True Then
                    Select Case ctrl.Name
                        Case "opt_1"        'Note the text comparison is Case sensitive
                            arrColor(1) = frm.lblArr_1.BackColor
                            arrColor(2) = frm.lblBtwn_1.BackColor
                            arrColor(3) = frm.lblDept_1.BackColor
                        Case "opt_2"
                            arrColor(1) = frm.lblArr_2.BackColor
                            arrColor(2) = frm.lblBtwn_2.BackColor
                            arrColor(3) = frm.lblDept_2.BackColor
                        Case "opt_3"
                            arrColor(1) = frm.lblArr_3.BackColor
                            arrColor(2) = frm.lblBtwn_3.BackColor
                            arrColor(3) = frm.lblDept_3.BackColor
                        Case "opt_4"
                            arrColor(1) = frm.lblArr_4.BackColor
                            arrColor(2) = frm.lblBtwn_4.BackColor
                            arrColor(3) = frm.lblDept_4.BackColor
                        Case "opt_5"
                            arrColor(1) = frm.lblArr_5.BackColor
                            arrColor(2) = frm.lblBtwn_5.BackColor
                            arrColor(3) = frm.lblDept_5.BackColor
                    End Select
                    'After a True is found, colors are all set
                    'so assign the array to the UDF Name and Exit the Loop
                    setColor = arrColor
                    Exit For
                End If
            End If
        Next ctrl
    End Function


    Regards, OssieMac

    • Marked as answer by dross85 Thursday, March 29, 2018 8:49 PM
    Wednesday, March 14, 2018 12:12 AM
  • I have uploaded a new version of the Example file with the new code to the following link.

    Removing the old option buttons also required deleting some code attached to them.

    https://1drv.ms/u/s!ArAXPS2RpafCl02EDS5rQ5xZWSpJ


    Regards, OssieMac

    • Marked as answer by dross85 Thursday, March 29, 2018 8:49 PM
    Wednesday, March 14, 2018 2:38 AM
  • OssieMac-

    I have put the code into my master sheet and followed your directions, possibly a change elsewhere was missed and I should just remove all the old code and replace it again.

    Unfortunately the code is not executing properly as I have been testing and trying to find what the problem is.

    When I update a shift for the employee, it is deleting dates from the list box instead of updating the listbox

    The updated shift is not populating correct with coloring and formats. If I just change the arrive date to one day earlier, it adds a day to the end . I have to update and change the dates a second time for it to correct the dates and formatting.

    The add shift is not working to overwrite the recent update record nor is it adding the new dates to the list. I am getting an error for column reference when I do not have a selection in the list box when trying to add back in the deleted shift.

    Something went very wrong with adding this last code into my sheet.

    I'll keep working on trying to see what I missed. I'll do some more testing to try and get it right. OneDrive is giving me errors to your link, I cannot access the onedrive to get the file on my personal PC. I'll try to download again later.
    • Edited by dross85 Thursday, March 15, 2018 3:51 AM
    Thursday, March 15, 2018 3:39 AM
  • Another update-

    I figured out a few bugs when using the 5 option buttons. The update shift no longer deletes the dates from the list box.

    The add shift /update record still is in need of some minor tweaks. Sometimes, when updating or adding a shift, it will add on a day before or a day after the selected days. The extra day is reflected into the listbox, but not the individual drop downs when the code runs. I have to re-select the shift from the listbox and change the arrive and depart dates then update a second time to make it correct. It only seems to happen when the userform first runs, on the first change. After it runs the code once, it hasnt seemed to do it again until the program is closed and re-opened.

    All in all, this is turning out great! Thanks again for your continued support!

    Thursday, March 29, 2018 8:48 PM
  • OneDrive is giving me errors to your link, I cannot access the onedrive to get the file on my personal PC. I'll try to download again later.

    Something strange must have occurred in this forum. I got several email notifications today for posts you made some time ago, including the one where you could not access the uploaded file. Unfortunately I was unaware of the problems you were having until this morning. I tested the link to the file and it did not work for me either so I have uploaded it again to the following link.

    Ensure that you down load the file and not attempt to edit it on line.

    https://1drv.ms/u/s!ArAXPS2RpafCl1a5CMsPBNglqyXZ

    Hope you can sort it out but if not then feel free to upload a copy of your work and I will have a look at it.

    Regards, OssieMac

    Thursday, March 29, 2018 9:31 PM
  • Thanks Ossie. The link file was fine, it happened in your blank_5 as it did in my master file. At this point, I have the code put in a larger workbook making it more difficult to split to share on onedrive. There is just too much sensitive information. Were you able to replicate the issue within your file? Im working with Excel2010, could be a problem between working with newer versions. I would like this file to be completely compatible with 2013 as this is what is on some work machines.

    If you have found this issue in your file, let me know so i can try to diagnose it on my end. Hope all is well!

    Thursday, March 29, 2018 11:51 PM
  • I have deleted my previous post because I believe I have now emulated the problem and hopefully fixed the problem so download the new workbook at the link below and test.

    The problem was in Sub UpdateRecord(bolDelete As Boolean, bolAddShift As Boolean). I won't go into individual lines of code. Just replace all of the sub with the new sub.

    https://1drv.ms/u/s!ArAXPS2RpafCl1edW795om7EmBz0


    Regards, OssieMac

    • Marked as answer by dross85 Friday, March 30, 2018 7:25 PM
    Friday, March 30, 2018 8:01 AM
  • OssieMac-

    That worked, problem solved! Now just to be able to add a new employee into a row of the sheet using the form instead of the worksheet. The quest continues!

    Sunday, April 01, 2018 1:18 AM
  • What are the rules for adding the new employee? The worksheet is divided up into what is obviously different positions.

    Rows 8 to 19 appear to be supervisory

    Row 20 has a header of Crew in column D and then rows 21 down with Lead and GMT.

    Currently my example is not alphabetical. Do you want them alphabetical under the separate position id's in column C?


    Regards, OssieMac

    Sunday, April 01, 2018 2:27 AM
  • I would like to be able to add a new employee into any desired row in the worksheet using the form. They are divided by position, but alphabetical is not an option because each person has another who is on opposite schedule. Row 20 that is a header row has been cleared, its not a table anymore, it does not include dates. In my actual form it is just shaded dark to distinguish between supervisory and crew

    The vision I have been trying is to have a button on the userform to raise an input box or small form with a Name combobox (that includes the same row source but allows new input) and a Position textbox and also has a spin button for the row number. Once filled out, it would insert that new name and position into the selected row number and shift all other rows down. This would also allow full delete the selected name/row number in the case the name is no longer employed.

    Sunday, April 01, 2018 4:43 PM
  • Rather than the user identifying a row for the new employee, I suggest selecting an existing employee and then a field to indicate to insert the new employee above or below the selected employee rather than a spin button to find the appropriate row number.

    Your description of employees in pairs for opposite schedules now makes sense to me with the thicker borders in each pair. Is my assumption correct in that this is what the thicker borders indicate? However, if you insert or delete employees then the pairs need to be handled so the thicker borders are applied to each pair and do not get out of sync.

    Do you have any thoughts on how to handle the top and bottom borders for the pairs? If they will always be pairs counting from the top (either from top of supervisory section or top of crew section) then it is easy enough to remove all top and bottom borders and then loop through the rows and re-apply the borders as required.


    Regards, OssieMac

    Monday, April 02, 2018 1:45 AM
  • I do like the idea of selecting an existing employee for input order rather than a spin button.

    Yes, the pairs who are opposite are grouped together with the thicker borders. The catch is that although it is best case to have each person have someone opposite, it's not always. In the master sheet, I do have one or two blank rows where those who do not have opposites are marked with the name field as "TBD". The way that the form is coded with the change method, I am not able to change TBD to a name, so that makes me have to enter it manually to the worksheet.

    I have another form within my master sheet to add new employees that will enter that new name to the bottom of the crew schedule worksheet so it populates in the combo box of the schedule form. Being able to re-order them from the form would be convenient for the user.

    Ultimately, I would like to have the forms control everything in my master sheet and have the worksheets themselves protected from general user input. With all the help with your code, my master sheet has become very dynamic and very useful. I believe it is nearing the point where it can be released to the user level. In protecting personal information from the whole world, I cannot add it to a link here in the forums but If it would help, we can exchange the information through personal channels.

    Monday, April 02, 2018 4:11 AM