Userform Monthly Calendar with Appointments on Textboxes

Jape

New Member
Joined
Sep 30, 2004
Messages
47
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:

Code:
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,
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,199
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top