Help with rounding date to specified weekday.

[tomleitch]

Hi,

I've got some code in a sheet that I have made that is trying to give dates based on a specified weekday.

The idea is that the starting day of the planning week is selectable via data that is put into a cell from a userform...

So I have this code for my userform:

Private Sub CommandButton1_Click()
Dim wday As Long
Application.ScreenUpdating = False
If Monday.Value = True Then wday = 0
If Monday.Value Then dname = "Monday"
 
If Tuesday = True Then wday = 1
If Tuesday = True Then dname = "Tuesday"
If Wednesday = True Then wday = 2
If Wednesday = True Then dname = "Wednesday"
If Thursday = True Then wday = 3
If Thursday = True Then dname = "Thursday"
If Friday = True Then wday = 4
If Friday = True Then dname = "Friday"
If Saturday = True Then wday = 5
If Saturday = True Then dname = "Saturday"
If Sunday = True Then wday = 6
If Sunday = True Then dname = "Sunday"
 
Sheets("5").Activate
Sheets("5").Range("A12") = wday
Sheets("5").Range("A13") = dname
Sheets("1").Activate
Application.ScreenUpdating = True
Unload Me

'MsgBox ("Planning day set to " & dname), vbOKOnly
End Sub

The idea is that this gives the set weekday value to my settings page A12 and the name of the day in A13.

On my other sheet I have the following code to give date values (for example if it is set to Monday) for the previous Monday, the Monday of that week and then the Monday of the following week.

Private Sub Worksheet_Activate()

Dim todaysDate As Integer
Dim pday As Integer

pday = Sheets("5").Range("A12")
todaysDate = Weekday(Date, pday)

Select Case todaysDate:
Case 1: 'monday
    varWeekday1 = Date - 5
    varWeekday2 = Date + 2
    varWeekday3 = Date + 9
Case 2: 'tue
    varWeekday1 = Date - 6
    varWeekday2 = Date + 1
    varWeekday3 = Date + 8
Case 3: 'wed
    varWeekday1 = Date - 7
    varWeekday2 = Date
    varWeekday3 = Date + 7
Case 4: 'thurs
    varWeekday1 = Date - 8
    varWeekday2 = Date - 1
    varWeekday3 = Date + 6
Case 5: 'fri
    varWeekday1 = Date - 9
    varWeekday2 = Date - 2
    varWeekday3 = Date + 5
Case 6: 'sat
    varWeekday1 = Date - 10
    varWeekday2 = Date - 3
    varWeekday3 = Date + 4
Case 7: 'sun
    varWeekday1 = Date - 11
    varWeekday2 = Date - 4
    varWeekday3 = Date + 3
Case Default:
    varWeekday1 = Date
    varWeekday2 = Date
    varWeekday3 = Date
End Select
    Range("B3") = "Last Week - " & varWeekday1
    Range("B4") = "This Week - " & varWeekday2
    Range("B5") = "Next Week - " & varWeekday3
End Sub

It seems to work ok for every day except when it is set to a Monday (although maybe that error will occur on different days on different days of the week??

Any help appreciated.

Thanks
Tom