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

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Philip J Smith" <(E-Mail Removed)> wrote in message
news:3F1BDBF0-99AD-47BB-88EE-(E-Mail Removed)...
> 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

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" wrote:

> 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
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Philip J Smith" <(E-Mail Removed)> wrote in message
> news:3F1BDBF0-99AD-47BB-88EE-(E-Mail Removed)...
> > 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
>
>

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Philip J Smith" <(E-Mail Removed)> wrote in message
news:989EAA71-3551-4CC0-B677-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Philip J Smith" <(E-Mail Removed)> wrote in message
>> news:3F1BDBF0-99AD-47BB-88EE-(E-Mail Removed)...
>> > 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
>>
>>

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

On Apr 24, 9:22 am, Philip J Smith
<PhilipJSm...@discussions.microsoft.com> wrote:
> 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

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

"David G" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Apr 24, 9:22 am, Philip J Smith
> <PhilipJSm...@discussions.microsoft.com> wrote:
> > 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
>