Show number of weeks in combo box

[Av8tordude]

Can someone help me with a code that will fill a combo box with the number of weeks in combo for the month I select. September is a combo box named cbMonth to select different months

 

[NoSparks]

How many weeks would you want for May 2020 ?

 

[Av8tordude]

6 weeks

 

[NoSparks]

testing on a user form I used this

Private Sub UserForm_Initialize()
    Me.cbMonth.List = Application.GetCustomListContents(4)
End Sub

Private Sub cbMonth_Change()
    Dim dte As Date, i As Long

If cbMonth = "" Then
    cbWeek.Clear
    Exit Sub
End If

dte = Me.cbMonth & " 15, " & Year(Date)
Me.cbWeek.Clear
For i = 1 To WeeksInMonth(dte)
    Me.cbWeek.AddItem "Week" & i
Next i
End Sub

Private Function WeeksInMonth(tDate As Date)
    Dim sDate As Date  ' First of the month
    Dim eDate As Date  ' Last of the month

    sDate = DateSerial(Year(tDate), Month(tDate), 1)
    eDate = DateSerial(Year(tDate), Month(tDate) + 1, 0)
    WeeksInMonth = DateDiff("ww", sDate, eDate) + 1
End Function

 

[Av8tordude]

Thank you NoSparks for the code. I've been busy at work so I haven't been able to test it, but it works . I greatly appreciate you helping.

 

[NoSparks]

You're welcome and thanks for the feedback