Get monday of current week based on userform input

Question

I am very new to VBA, so please bear with me. I have a userform that is going to eventually create a 2 or 6 week schedule look ahead. The userform has a textbox which automatically populates the Monday of the current week as the lookahead start date. If the user inputs a date, it will use that date as the lookahead start date instead.

The part I can't seem to figure out, is the formula so that when the user inputs a date, it calculates the Monday of that week.

The code:

Private Sub UserForm_Initialize()
'Inserts date of Monday this week into "Start Date" field in UserForm
    LookAheadDate1.Value = Date - Weekday(Date) + 2
End Sub

Private Sub Generate_Click()

Dim StartDate As Date
Dim EndDate As Date
Dim ws As Worksheet
    Dim addme As Long
    Set ws = Worksheets("Projects")
    addme = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


' Clears Look Ahead sheet - Row 5 and below
    With Sheets("Look Ahead")
     .Rows(5 & ":" & .Rows.Count).Delete
    End With

'Force user to select either option button
    If ((Me.OptionButton1.Value = 0) * (Me.OptionButton2.Value = 0)) Then
        MsgBox "Please select 2 or 6 Week Look Ahead"
    End If

'Force user to select either option button
    If ((Me.OptionButton1.Value)) Then
     ThisWorkbook.Worksheets("Look Ahead").Range("E6").Value = "2 Week Look Ahead"
    End If

    If ((Me.OptionButton2.Value)) Then
        ThisWorkbook.Worksheets("Look Ahead").Range("E6").Value = "6 Week Look Ahead"
    End If

' Set StartDate Variable - If Start Date field value is blank, use this weeks start date, otherwise use start date field value
    If IsNull(Me.LookAheadDate1.Value) Or Me.LookAheadDate1.Value = "" Then
        StartDate = Date
            Else
        StartDate = LookAheadDate1.Value
    End If

' Option buttons / Code to create end date for 2 or 6 week look ahead
    Dim res As Date
        If OptionButton1.Value Then
                EndDate = StartDate - Weekday(Date) + 16
            ElseIf OptionButton2.Value Then
                EndDate = StartDate - Weekday(Date) + 44
        End If

'Write Look Ahead date range in cell "E7"

    ThisWorkbook.Worksheets("Look Ahead").Range("E7").Value = StartDate - Weekday(Date) + 2 & "  to  " & EndDate

'Clear all fields in UserForm
    Dim oneControl As Object
        For Each oneControl In ProjectData.Controls
            Select Case TypeName(oneControl)
                Case "TextBox"
                    oneControl.Text = vbNullString
                Case "CheckBox"
                    oneControl.Value = False
            End Select
    Next oneControl

'Close UserForm.
    Unload Me

End Sub

Private Sub ToggleButton1_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub LookAheadDate1_Change()

End Sub

Private Sub Cancel_Click()
'Close UserForm if "Cancel" Button is pressed
    Unload Me
End Sub

'Checks for entry of valid date

Private Sub LookAheadDate1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If LookAheadDate1 = vbNullString Then Exit Sub

        If IsDate(LookAheadDate1) Then
          LookAheadDate1 = Format(LookAheadDate1, "Short Date")
        Else
          MsgBox "Please Enter Valid Date"
          Cancel = True
        End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'If the user clicks the "X" to close the UserForm, then cancel
If CloseMode = 0 Then Cancel_Click
End Sub

A *long*-overdue welcome to the site! As a reminder, check out the [tour](https://stackoverflow.com/tour) and the [how-to-ask page](https://stackoverflow.com/help/how-to-ask) for more about asking questions that will attract quality answers. I am going to take the liberty of removing your note about closing before the Generate button is pressed, since I think that sounds like its own question. Before you post that one, would you see if you can strip out as much code as possible while still having the early-close problem? Good luck! — cxw, Jul 20 '18 at 12:42

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

If I understand your question about the date correctly, you want to edit this block of your code:

If IsNull(Me.LookAheadDate1.Value) Or Me.LookAheadDate1.Value = "" Then
    StartDate = Date    ' <--- this is where you want Monday, right?
Else
    StartDate = LookAheadDate1.Value
End If

If so, replace the marked line with

StartDate = Date - Application.WorksheetFunction.Weekday(Date,3)

to get the date of Monday of this week as the StartDate.

Date returns the current date (similar to Now, but without the time part)
The use of Weekday (=WEEKDAY()) to get Monday is from this answer by Paul

Edit

In the Else branch:

Dim enteredDate as Date
enteredDate = CDate(LookAheadDate1.Value)
StartDate = enteredDate - Application.WorksheetFunction.Weekday(enteredDate,3)

CDate converts from text to date according to the current locale. You may need to parse the date text manually if you need a more sophisticated conversion.

I believe LookAheadDate1 is a text box because I see you are giving it a value using Format(..., "Short Date"). If it is a date/time picker, you may not need the CDate call.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 20 '18 at 12:36

cxw

16,685
2
45
81

Thanks for the reply! I would also like to calculate the Monday date if the user manually enters a date. Basically, the userform will default to the current week as a starting point, but the user can also manually enter any date. This section: {StartDate = LookAheadDate1.Value} – sparkynerd Jul 20 '18 at 13:06
I put in a message box temporarily, so that when "Generate" is pressed, it shows me the start and end dates. Maybe I am misunderstanding, but it seems like this is returning 12:00AM as the dates. Should I change this text box? It will only ever have a date entered into it. – sparkynerd Jul 20 '18 at 13:49
@sparkynerd `StartDate` is also a text box, not a variable? Use `Format(..., "Short Date")` just like you did for `LookAheadDate1` and see if that does it. – cxw Jul 20 '18 at 14:06

score 0 · Answer 2 · answered Jul 20 '18 at 15:19

0

I got it working. I added a calendar date picker to input the date into the textbox. Thanks for the help!

answered Jul 20 '18 at 15:19

sparkynerd

81
3
15

Get monday of current week based on userform input

2 Answers2