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).

calendar.jpg


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,
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,141,226
Messages
5,705,130
Members
421,379
Latest member
SoundlessResonance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top