Can a Checkbox Caption in Excel be a date? 1

[UnsolvedCoding]

I am working on a userform and it needs to have dates populate on the checkbox captions. I cannot hard code the dates because the dates need to update each time the userform opens. In short, when the form is opened the checkboxes need to have captions for the next two weeks.

The checkboxes are already made I simply can't find anything anywhere that says how to or gives examples of how to do this. When I tried to use code it didn't work with the exisiting userform and I am not eager to re-create a list of 50 some odd options all over again.

Does anyone know how to have the checkbox captions show dates?

 

[Andrzejek]

On the UserForm, 3 CheckBoxes: CheckBox1, CheckBox2, CheckBox3

Option Explicit

Private Sub UserForm_Initialize()
Dim i As Integer

For i = 1 To 3
     UserForm1.Controls("CheckBox" & i).Caption = Date + (i - 1)
Next i

End Sub

You will end up with captions:
5/30/2012
5/31/2012
6/1/2012

Is that what you are after?

Have fun.

---- Andy

 

[UnsolvedCoding]

I modified it a bit to adjust the date and to take into account weekends. The checkbox and the date work like a charm.

THANKS!!


I = 1

J = 1

While J <= 14

' If its a Saturday go forward two days
If Weekday(Date + I) = 7 Then I = I + 2

' If its a Sunday go forward one day
If Weekday(Date + I) = 1 Then I = I + 1

' If its a weekday go forward one day
If Weekday(Date + I) <= 6 And Weekday(Date + I) >= 2 Then
UserForm1.Controls("CheckBox" & J).Caption = Date + I
I = I + 1
End If

J = J + 1
Wend

UserForm1.Show