Sheet display in function of date and time on open and save

Question

Here's a simple one for u guys,

The code is called upon workbook_open and BeforeSave, it displays the right sheet in function of date and time (Day vs night shift vs date). The code run trough the sheet and activate it if the date on sheet is the same as the actual date. Since there's 2 sheets with the same date (day and night shift) i had to add a time condition. The nights shifts are before the day one (look at image) so i had to split my loop in 2 to make it work (could maybe be one). Also, if we're not in the week of that report, so that no sheets has a date that fits, i wanted it to select Friday cause it has a report on it. Since its called often i wanted to make sure that it is optimal.

Sheet order

Code :

Sub selectiondequartauto()
' Code that display sheet in fucntion of date and time
 Dim count As Long ' Counter 
 Dim xSh As Worksheet
 Dim activation as Boolean 
 Dim sheetdate As Date
 Dim limitpm As Variant
 Dim limitam As Variant
 limitpm = TimeValue("16:15:00") ' Night shift Start
 limitam = TimeValue("3:00:00") ' Night shift end
 For Each xSh In Worksheets ' Loop on every visible sheet
 If xSh.Visible Then
 sheetdate = xSh.Cells(4, 2).Value ' Gives sheetdate the date of the sheet in present loop
 If TimeValue(Now) < limitpm And TimeValue(Now) > limitam Then
 If count < 1 Then ' If count is smaller than 1 (no sheet w same date as now yet)
 If sheetdate = Date Then ' Si Sheetdate = actual date 
 count = count + 1 ' Count = 1 and next sheet, because 2 sheet have sheetdate = actual date
 End If 
 ElseIf count = 1 Then ' Si count = 1
 Activation = True
 xSh.Activate ' Activate curent loop sheet and quit
 Exit For
 End If
 Else ' If we are in night shift time frame
 If sheetdate = Date Then
 Activation = True
 xSh.Activate ' Activate curent loop sheet and quit
 Exit For
 End If
 End If
 End If
 
 Next
 If Activation = False Then 
 Sheets("Vendredi jour").Activate
 End If
End Sub

Answer 1

selectiondequartauto() defines night shift to determine whether to select either the night shift or day shift sheet. If none of the worksheets meet the criteria of Night Shift or Day Shift then the Friday worksheet is selected. This all works fine but consider the advantages of my refactored code which separates these tasks into their own methods.

• isNightShift: Using a function that determines what is Night Shift and what is Day Shift means that you can update the hours of Night Shift in one spot and not have to look over every procedure that is dependent on on the Night Shift.

• NightShiftSheet() and DayShiftSheet(): Having these functions will prevent you from having to repeat the logic to find the sheets in the future. They also make selectiondequartauto() easier to read

Public Sub selectiondequartauto()
 Dim ws As Worksheet
 
 If isNightShift(Now) Then
 Set ws = NightShiftSheet
 Else
 Set ws = DayShiftSheet
 End If
 
 If ws Is Nothing Then
 Sheets("Vendredi jour").Activate
 Else
 ws.Activate
 End If
End Sub
Public Function DayShiftSheet() As Worksheet
 Dim ws As Worksheet
 For Each ws In ThisWorkbook.Worksheets
 With ws.Range("B4")
 If IsDate(.Value) Then
 If Not isNightShift(.Value) Then
 Set DayShiftSheet = ws
 Exit Function
 End If
 End If
 End With
 Next
 
End Function
Public Function NightShiftSheet() As Worksheet
 Dim ws As Worksheet
 For Each ws In ThisWorkbook.Worksheets
 With ws.Range("B4")
 If IsDate(.Value) Then
 If isNightShift(.Value) Then
 Set NightShiftSheet = ws
 Exit Function
 End If
 End If
 End With
 Next
End Function
Public Function isNightShift(DateTime As Date) As Boolean
 isNightShift = TimeValue(DateTime) > TimeValue("3:00:00") And TimeValue(DateTime) < TimeValue("16:15:00")
End Function

• Patates Pilées: Excellent refactor! It always seems to me that my code are clear but it seems like there's always something to perfect that i cant see! Before i accept i need to correct some little detail: do you want me to adress them in comment ot update my question?
• Patates Pilées: Update : I solved the issues, it now work following your logic even tho it went back to complex condition and hard to read; it woudve work perfecty with your method if date on sheets were actual date + time but rn its actually fixed pre determined manually entered date only (no time) so the call back to isNightShift was never returning True (since DateTime had no time in it).
• Patates Pilées: Il repost because of the amount of midification ive made
• Patates Pilées: No problem that was usefull!