General help with an excel service schedule

[Craiggy]

Hey All,

I'm creating a service schedule. The schedule would have a list of service engineers and show what customer's they are at throughout the year. The whole year with days of the week and dates would be on one sheet. On the far left most column from top to bottom, I have the year which is defined by the name "CalendarYear". The next row down I have a drop-down list which contains all the months of the year. The next row below that I have a drop-down list of service engineers. I used data validation to create both of the drop-down lists.

To the right of the year in the same row, I have a list of days (Mon-Sun). I defined these days by the function =TEXT(WEEKDAY(DATE(CalendarYear,1,1),1),"aaa"). I would love it if I could eliminate Saturday and Sunday since the guys don't typically work those days. To the right of the month drop-down list in the same row, I have a list of the dates (1/1/2018-12/31/2018). I defined these dates with the function =DATE(CalendarYear,1,1) but I don't know how to move that function out for the whole year. I would like to be able to change the year manually and have it change the days of the week and dates automatically. I would like it to account for Feb 29th. In years with Feb 29 like 2020, I'd like it to be visible. In years without like 2018 I'd like it to be hidden. Because the schedule will span the entire year, I could see people getting frustrated if they have to scroll for a while to get to the correct month. Is there a way you could select a month from the drop-down list of months and have it jump to that month in the Excel sheet? Also, is there a way as you scroll thru the schedule that the month drop-down would change? For example, if my cursor is on a cell in the month of January the month drop-down list would show Jan. If I were to then scroll into February it would show Feb. Some of this I think will be easy while other things will require VBA. I would appreciate any direction you could give. A link to my starter Excel sheet is listed below. I'm working with Office 365.

https://sft.mazakcorp.com/message/9jGijZkVrbtuF62rBJtGIo/VkaXtKaJBO7oSX7AbN7jVi/SITPJYrT5tQ1b3JEzqfxeg/Service%20Schedule.xlsx

Thank You!

~Craig

 

[pbornemeier]

This should help with some parts of your workbook

Put 1/1/2018 in A1
Put this formula in A3
=WORKDAY($A$1,ROW()-3)
Drag-copy the formula down to row 266
It will show the weekdays for the year.
Format that column with "ddd mm/dd/yyyy"

Add an ActiveX combo box at the top of the worksheet with Jan...Dec as choices and this code.
Freeze the top 2 rows so the combo box does not disappear when the worksheet scrolls.

Option Explicit

Private Sub ComboBox1_Change()

    Dim sInput As String
    Dim sLookup As String
    
    sInput = UCase(Me.ComboBox1.Text)
    sLookup = Switch( _
        sInput = "JAN", "01/*/", sInput = "FEB", "02/*/", sInput = "MAR", "03/*/", _
        sInput = "APR", "04/*/", sInput = "MAY", "05/*/", sInput = "JUN", "06/*/", _
        sInput = "JUL", "07/*/", sInput = "AUG", "08/*/", sInput = "SEP", "09/*/", _
        sInput = "OCT", "10/*/", sInput = "NOV", "11/*/", sInput = "DEC", "12/*/")
    
    'Find and select first date in month
    Columns("A:A").Find(What:=sLookup, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
    'Scroll to put first of month at top of worksheet
    ActiveWindow.ScrollRow = ActiveCell.Row
    
End Sub

 

[Craiggy]

Hey Phil,

Thanks for the information. I was able to get this going the way you described. My problem is that I need the dates to span column instead of down rows. I want to be able to select the year and have the dates change based on that. Also, I still want to jump like your sub describes but using columns. Let me know if you can help. Below is a link to the basic layout. I created a date drop-down list using data validation, but I also noticed that if I freeze that frame and move over to the right a bunch of columns, I no longer have the option of changing the year. I can still see the year, but there is no longer a drop-down list I can select a year from. I attached my basic excel document for you to look at. I'm not sure yet if I want Saturday & Sunday included or not.

https://sft.mazakcorp.com/message/DBiOJPfCJhAHc91OFdQ3im[FONT=&quot] [/FONT]

Thanks,

~Craig

 

[pbornemeier]

ComboBox2 contains the 4-digit year
ComboBox1 contains Jan, Feb, Mar,...,Dec

The code below will allow a multi-year display with the dates in Row1 and formatted as "ddd mm/dd/yyyy"

Private Sub ComboBox1_Change()
    Dim sInput As String
    Dim sLookup As String
    Dim sYear As String
    
    sYear = Sheet1.ComboBox2.Text  
    
    sInput = UCase(Me.ComboBox1.Text)
    sLookup = Switch( _
        sInput = "JAN", "01/*/" & sYear, sInput = "FEB", "02/*/" & sYear, sInput = "MAR", "03/*/" & sYear, _
        sInput = "APR", "04/*/" & sYear, sInput = "MAY", "05/*/" & sYear, sInput = "JUN", "06/*/" & sYear, _
        sInput = "JUL", "07/*/" & sYear, sInput = "AUG", "08/*/" & sYear, sInput = "SEP", "09/*/" & sYear, _
        sInput = "OCT", "10/*/" & sYear, sInput = "NOV", "11/*/" & sYear, sInput = "DEC", "12/*/" & sYear)
    
    'Find and select first date in month
    Rows(1).Find(What:=sLookup, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
    'Scroll to put first of month at top of worksheet
    ActiveWindow.ScrollColumn = ActiveCell.Column

End Sub

I'll work on some code to generate those headers.