Userform Monthly Calendar with Appointments on Textboxes

[Jape]

Hi all,

Been searching for a solution for this for a while. I have a userform with a listbox for each month, when a user clicks for ex. February i want the appointments(in this case races) to be shown on the textboxes. Since i have all days of the month and only a couple of dates for appointments, say January reaches from 1st January to 31th January and my appointments are for the dates 15-1 to 21-1 and a one day race for say 14-1, i need to reference the according textboxes trough VBA. But thats where im stuck.

The userform looks like this, with each daynumber, and adjoining racenames have names like CAL1(daynumber), CAL1_R1(race 1 for daynumber 1), CAL4_R3(race 3 for daynumber 4).

The partial code i have thus far for populating the calendar:

Private Sub CAL_LB_Month_afterupdate()


Dim ctl As Control
Dim DateStart As Range
Dim DateEndi As Range

'Sort the range on Dates
Worksheets("TempR").Range("A1", Worksheets("TempR").Range("A2").End(xlDown).Offset(0, 28)).Sort Worksheets("TempR").Range("A1"), xlAscending, header:=xlYes

Worksheets("TempR").Activate
Range("A2").Activate

'Loop trough the dates to find first and last occurance of date in specific month
Set DateStart = ActiveCell
Do Until Month(ActiveCell.Offset(1, 0).Value) = 2
    ActiveCell.Offset(1, 0).Activate
Loop
Set DateEnd = ActiveCell
 
'This is where im stuck --> it should use the date in the range.value to point to the specific textbox   
Do Until DateStart = DateEnd
myvar = "Main.CAL" & Day(DateStart)
Set ctl = main. &myvar
    If DateStart.Offset(0, 1) = "" Then
        ctl.Caption = DateStart.Offset(0, 2).Value
    End If
Set DateStart = DateStart.Offset(1, 0)
Loop
    


'The rest is just to hide the dates that dont exist for specific months (29,30,31th for february for ex.
Select Case Main.CAL_LB_Month.ListIndex

    Case Is = 0
        Main.CAL29.Visible = True
        Main.CAL30.Visible = True
        Main.CAL31.Visible = True
    
        Main.CAL29_R1.Visible = True
        Main.CAL30_R1.Visible = True
        Main.CAL31_R1.Visible = True
        Main.CAL29_R2.Visible = True
        Main.CAL30_R2.Visible = True
        Main.CAL31_R2.Visible = True
        Main.CAL29_R3.Visible = True
        Main.CAL30_R3.Visible = True
        Main.CAL31_R3.Visible = True
        Main.CAL29_R4.Visible = True
        Main.CAL30_R4.Visible = True
        Main.CAL31_R4.Visible = True
    
        Main.CAL29_PtC.Visible = True
        Main.CAL29_PtT.Visible = True
        Main.CAL30_PtC.Visible = True
        Main.CAL30_PtT.Visible = True
        Main.CAL31_PtC.Visible = True
        Main.CAL31_PtT.Visible = True
     
    Case Is = 1
        Main.CAL29.Visible = False
        Main.CAL30.Visible = False
        Main.CAL31.Visible = False
    
        Main.CAL29_R1.Visible = False
        Main.CAL30_R1.Visible = False
        Main.CAL31_R1.Visible = False
        Main.CAL29_R2.Visible = False
        Main.CAL30_R2.Visible = False
        Main.CAL31_R2.Visible = False
        Main.CAL29_R3.Visible = False
        Main.CAL30_R3.Visible = False
        Main.CAL31_R3.Visible = False
        Main.CAL29_R4.Visible = False
        Main.CAL30_R4.Visible = False
        Main.CAL31_R4.Visible = False
    
        Main.CAL29_PtC.Visible = False
        Main.CAL29_PtT.Visible = False
        Main.CAL30_PtC.Visible = False
        Main.CAL30_PtT.Visible = False
        Main.CAL31_PtC.Visible = False
        Main.CAL31_PtT.Visible = False
    
    Case Is = 2
        Main.CAL29.Visible = True
        Main.CAL30.Visible = True
        Main.CAL31.Visible = True
    
        Main.CAL29_R1.Visible = True
        Main.CAL30_R1.Visible = True
        Main.CAL31_R1.Visible = True
        Main.CAL29_R2.Visible = True
        Main.CAL30_R2.Visible = True
        Main.CAL31_R2.Visible = True
        Main.CAL29_R3.Visible = True
        Main.CAL30_R3.Visible = True
        Main.CAL31_R3.Visible = True
        Main.CAL29_R4.Visible = True
        Main.CAL30_R4.Visible = True
        Main.CAL31_R4.Visible = True
    
        Main.CAL29_PtC.Visible = True
        Main.CAL29_PtT.Visible = True
        Main.CAL30_PtC.Visible = True
        Main.CAL30_PtT.Visible = True
        Main.CAL31_PtC.Visible = True
        Main.CAL31_PtT.Visible = True

...... and so on for every month number

Am i overcomplicating things or is this the most efficient way to do it, since there needs to be quite some calculations performed on each month change (when a user picks another month from the list) speed is quite an issue.

So.. my main question, how can i reference the appropiate textbox to change to caption for the specific race on the specific date, can't i make an array of the textboxes. Cause i have no clue to how i dynamically change the appropiate control.

Regards, Jape,