Populate Checkbox label with date between two date values

[JasonEnsor]

Hi Guys,

I have a userform with 12 checkboxes on it, one for each week of a semester, my aim is to populate the label of each checkbox with the relevant date. I am trying to build a function where i can pass in a start date, end date and day name and from that populate the checkbox label. The reasoning behind this is that currently the user manually copies data for modules that they study on to a sheet for each week of the semester that they attend. However most modules do not run a session every week, they have study weeks etc...I am wanting the user to be able to enter the day the module falls on, then from that see a list of all the dates in the semester where they can tick all of the dates where the module is running.

Saving the data will utilise bog standard code that i already have written to save a new row for every selected checkbox, but it is just working out how to populate the label for the 12 weeks that i am unsure of.

Example Data: Semester start 23 Sept 2013, Semester end 17 Dec 2013. Users Module falls on a Monday

Checkbox No Label Value
1 23 Sept 2013
2 30 Sept 2013
3 7 Oct 2013
4 14 Oct 2013
5 21 Oct 2013

etc...

Any Help would be appreciated. I am sure it will be something silly as usual

Regards

Jason

 

[strongm]

>Example Data: Semester start 23 Sept 2013, Semester end 17 Dec 2013. Users Module falls on a Monday

That'll give you thirteen possible Mondays, won't it?

Anyway, here's some quickly hacked together code that'll generate candidate dates for a specific day between two dates:

[blue]Public Function CalcAvailableDates(startday As VbDayOfWeek, startdate As Date, enddate As Date) As Date()
    Dim AvailableDate As Date
    Dim Available() As Date

    Dim max As Long
    If startday < Weekday(startdate) Then startdate = startdate + 7
    For AvailableDate = startdate - (Weekday(startdate) - startday) To enddate Step 7
        ReDim Preserve Available(max) As Date
        Available(max) = AvailableDate
        max = max + 1
    Next
    CalcAvailableDates = Available
End Function[/blue]

and an example of calling it:

[blue]    Dim SessionDate As Variant
    For Each SessionDate In CalcAvailableDates(vbMonday, startsemester, endsemester)
        Debug.Print SessionDate
    Next[/blue]

 

[SkipVought]

Hi,

[StartweekDate] + (ckbxnbr - 1) * 7

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[strongm]

That won't work as it stands will it, Skip? You need to modify by the specified day.

 

[SkipVought]

I got my exersize this morning jumping to conclusions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}