CODEWITHSUNDEEP
vbaexceldateweekday
sparkynerd
sparkynerd

Reputation: 73

Get monday of current week based on userform input

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

Upvotes: 2

Views: 1302

Answers (2)

sparkynerd
sparkynerd

Reputation: 73

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

Upvotes: 0

cxw
cxw

Reputation: 17041

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.

Upvotes: 1

Related Questions