VBA Code for ComboBox1 to list dates for every Monday

[Patriot2879]

Hi good afternoon, hope you can help me please i am trying to make a drop down list where dates can be selected in a ComboBox which is in a Userform for Every Monday up to 8 weeks. and once the old date has expired to disappear from the drop downlist and the new date added weekly. Really hope yuo can help me please i have tried several codes but none are working.

 

[Patriot2879]

I have tried the code below, which works now, but the first date i want it to start from the first Monday from 'Now' if possible then list the next 8 weeks.

if this bit below can be from the first monday from todays date?

sDate = "Now" & Year(Date)

Private Sub ComboBox1_Change()


End Sub


Private Sub UserForm_Initialize()

Dim sDate As String
Dim i As Integer

    sDate = "Now" & Year(Date)
    
    If Day(CDate(sDate)) <> vbMonday Then
        sDate = DateAdd("d", vbMonday - Day(CDate(sDate)) - 1, CDate(sDate))
    End If
    
    For i = 0 To 8
        ComboBox1.AddItem Format(DateAdd("ww", i, sDate), "mmm dd, yyyy")
        If Date > DateAdd("ww", i, sDate) And Date < DateAdd("ww", i + 1, sDate) Then
            ComboBox1.ListIndex = i
        End If
    Next i



End Sub

 

[dmt32]

try this update to your code & see if does what you want

Public Sub UserForm_initialize()
    Dim dDate           As Date
    Dim i               As Long
   
    'change format as required
    Const DateFormat    As String = "mmm dd, yyyy"
    'change no weeks as required
    Const NoWeeks       As Long = 8
    'get date of current week
    dDate = Date - Weekday(Date, vbMonday) + 1
   
    For i = 0 To NoWeeks
        Me.ComboBox1.AddItem Format(DateAdd("ww", i, dDate), DateFormat)
    Next i
   
End Sub

Dave

 

[Patriot2879]

try this update to your code & see if does what you want

Public Sub UserForm_initialize()
    Dim dDate           As Date
    Dim i               As Long
  
    'change format as required
    Const DateFormat    As String = "mmm dd, yyyy"
    'change no weeks as required
    Const NoWeeks       As Long = 8
    'get date of current week
    dDate = Date - Weekday(Date, vbMonday) + 1
  
    For i = 0 To NoWeeks
        Me.ComboBox1.AddItem Format(DateAdd("ww", i, dDate), DateFormat)
    Next i
  
End Sub

Dave

HI Dave this is working great thank you how to i change the format to 18/07/2022 (dd,mm,yyyy)?

 

[Patriot2879]

HI Dave this is working great thank you how to i change the format to 18/07/2022 (dd,mm,yyyy)?

i've done it thank you this is fantastic

 

[dmt32]

You are welcome - appreciate feedback

Dave