Calendar Control - Years

[Guest]

Hi I've used the tutorial at www.fontstuff.com/vba/vbatut07.htm to set up a
calendar for date selection in a cell.

I've used an earlier posting (12/5/2006 6:28 AM Calendar Control) by Tom
Ogilvy to pick up the Friday of the week for any date selected.

ActiveCell.Value = Format(Calendar1.Value - Weekday(Calendar1.Value,
vbSaturday), "dd-mmm-yy")

I'd like to know if it is possible limit the start and end years which can
be selected from the Calendar, say 2003 - 2012.

Can anyone help please?

Regards

 

[Ron de Bruin]

hi Philip

You can check the value(date) when you click on the control (click event) and when it is before 2003 jump to
the first date you allow and if it is after the last date jump to your last date.

If you need help post back

 

[Guest]

Hi Ron.

Can you give me the syntax for the first of these so that I can replicate it
for the Second please?

Regards

Phil

 

[Ron de Bruin]

In the click event you can use this two events

If Calendar1.Value < 37622 Then Calendar1.Value = 37622
If Calendar1.Value > 41274 Then Calendar1.Value = 41274

 

[stevebriz]

Youl could do something like this

Private Sub Calendar1_Click()
If Year(Calendar1.Value) < 2003 Or Year(Calendar1.Value) > 2007 Then
MsgBox "Please select Date between the Years 2003 and 2007"
End Sub

 

[David G]

Hi I've used the tutorial atwww.fontstuff.com/vba/vbatut07.htmto set up a
calendar for date selection in a cell.

I've used an earlier posting (12/5/2006 6:28 AM Calendar Control) by Tom
Ogilvy to pick up the Friday of the week for any date selected.

ActiveCell.Value = Format(Calendar1.Value - Weekday(Calendar1.Value,
vbSaturday), "dd-mmm-yy")

I'd like to know if it is possible limit the start and end years which can
be selected from the Calendar, say 2003 - 2012.

Can anyone help please?

Regards

This way is not particularly simple, but:
1. Remove the regular calendar month/year combo-boxes by setting "Show
Date Selectors" to "False" for the Calendar.
2. Create two new combo-boxes called MonthBox and YearBox.
3. Add the following code to the UserForm (assumed the calendar object
is called Cal):

Private Sub YearBox_Change()
Dim new_date As Date, eom_date As Date
new_date = DateSerial(YearBox, DateTime.month(Cal),
DateTime.Day(Cal))
eom_date = EoMonth(DateSerial(YearBox, DateTime.month(Cal), 1), 0)

If new_date < eom_date Then
Cal = new_date
Else
Cal = eom_date
End If
End Sub

Private Sub MonthBox_Change()
Dim new_date As Date, eom_date As Date
new_date = DateSerial(DateTime.year(Cal), MonthBox.ListIndex + 1,
DateTime.Day(Cal))
eom_date = EoMonth(DateSerial(DateTime.year(Cal),
MonthBox.ListIndex + 1, 1), 0)

If new_date < eom_date Then
Cal = new_date
Else
Cal = eom_date
End If
End Sub

Private Sub UserForm_Initialize()
' Set calendar date to now, if you want
Cal = Now()

Dim year As Integer, month As Integer
With YearBox
For year = 2003 To 2012
.AddItem year
Next year
YearBox.Text = DateTime.year(Cal)
End With

With MonthBox
For month = 1 To 12
MonthBox.AddItem MonthName(month)
Next month
MonthBox = MonthName(DateTime.month(Cal))
End With
End Sub


Make sure you enable the Analysis ToolPak - VBA addin. And I wouldn't
recommend typing into the new combo-boxes, but I don't remember if you
can lock them but still allow users to select from them.

Cheers,
David

 

[Guest]

This worked for me.

Private Sub Calendar1_NewYear()
Me.Calendar1.ValueIsNull = False
If Year(Me.Calendar1) >= 2012 Then
MsgBox "NO!"
Me.Calendar1.PreviousYear
End If
If Year(Me.Calendar1) <= 2003Then
MsgBox "NO!"
Me.Calendar1.NextYear
End If
End Sub