how do I limit the dates that a calendar allows the user
to select? the calender is placed on a form

have tried using the 'beforeUpdate' event and can't get it
to work

using the MS Calendar Control 10.0

any ideas appreciated

Hi Brent

'Test calendar date falls within 2004

Private Sub CommandButton1_Click()
Dim calendardate As Date
Dim earliestdate As Date
Dim latestdate As Date

earliestdate = DateSerial(2004, 1, 1)
latestdate = DateSerial(2004, 12, 31)
calendardate = Calendar1.Value

If calendardate < earliestdate Or calendardate latestdate Then
MsgBox "The date selected of " & Format(calendardate, "dd mmm yyyy") & _
" does not fall between " & Format(earliestdate, "dd mmm yyyy") & _
" and " & Format(latestdate, "dd mmm yyyy") & ". Please try again."
Exit Sub
End If

MsgBox "Do your stuff here"
End Sub


--
XL2002
Regards

William



"brent" wrote in message
...
| how do I limit the dates that a calendar allows the user
| to select? the calender is placed on a form
|
| have tried using the 'beforeUpdate' event and can't get it
| to work
|
| using the MS Calendar Control 10.0
|
| any ideas appreciated

thanks Will

i would like to prevent user from being able to go beyond
say Jul 31 04; for example when the user selects Aug 08 04,
the calendar should reset to its' previous date and thus
the new date is not highlighted

have tried to make it work as follows -

Sub calendar1_beforeupdate(ByVal Cancel As
MSForms.ReturnBoolean)

If calender1.Value #7/31/2004# Then
Cancel = True
End If

End Sub




-----Original Message-----
Hi Brent

'Test calendar date falls within 2004

Private Sub CommandButton1_Click()
Dim calendardate As Date
Dim earliestdate As Date
Dim latestdate As Date

earliestdate = DateSerial(2004, 1, 1)
latestdate = DateSerial(2004, 12, 31)
calendardate = Calendar1.Value

If calendardate < earliestdate Or calendardate
latestdate Then
MsgBox "The date selected of " & Format(calendardate, "dd
mmm yyyy") & _
" does not fall between " & Format(earliestdate, "dd mmm
yyyy") & _
" and " & Format(latestdate, "dd mmm yyyy") & ". Please
try again."
Exit Sub
End If

MsgBox "Do your stuff here"
End Sub


--
XL2002
Regards

William



"brent" wrote in
message
...
| how do I limit the dates that a calendar allows the user
| to select? the calender is placed on a form
|
| have tried using the 'beforeUpdate' event and can't get
it
| to work
|
| using the MS Calendar Control 10.0
|
| any ideas appreciated


.

Does this help?

Private Sub Calendar1_Click()
If Month(Calendar1.Value) < 7 Or Year(Calendar1.Value) < 2004 Then
MsgBox "The date selected has to fall within July 2004. Please try again."
Calendar1.Value = DateSerial(2004, 7, 1)
End If
End Sub

Private Sub UserForm_Initialize()
Calendar1.Value = DateSerial(2004, 7, 1)
End Sub


--
XL2002
Regards

William



wrote in message
...
| thanks Will
|
| i would like to prevent user from being able to go beyond
| say Jul 31 04; for example when the user selects Aug 08 04,
| the calendar should reset to its' previous date and thus
| the new date is not highlighted
|
| have tried to make it work as follows -
|
| Sub calendar1_beforeupdate(ByVal Cancel As
| MSForms.ReturnBoolean)
|
| If calender1.Value #7/31/2004# Then
| Cancel = True
| End If
|
| End Sub
|
|
|
|
| -----Original Message-----
| Hi Brent
|
| 'Test calendar date falls within 2004
|
| Private Sub CommandButton1_Click()
| Dim calendardate As Date
| Dim earliestdate As Date
| Dim latestdate As Date
|
| earliestdate = DateSerial(2004, 1, 1)
| latestdate = DateSerial(2004, 12, 31)
| calendardate = Calendar1.Value
|
| If calendardate < earliestdate Or calendardate
| latestdate Then
| MsgBox "The date selected of " & Format(calendardate, "dd
| mmm yyyy") & _
| " does not fall between " & Format(earliestdate, "dd mmm
| yyyy") & _
| " and " & Format(latestdate, "dd mmm yyyy") & ". Please
| try again."
| Exit Sub
| End If
|
| MsgBox "Do your stuff here"
| End Sub
|
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "brent" wrote in
| message
| ...
| | how do I limit the dates that a calendar allows the user
| | to select? the calender is placed on a form
| |
| | have tried using the 'beforeUpdate' event and can't get
| it
| | to work
| |
| | using the MS Calendar Control 10.0
| |
| | any ideas appreciated
|
|
| .
|

William,

that is very helpful!

I would like to go a step further though and obtain the
date that was selected prior to the update

i.e. - if the date with focus is July 7 04 then upon
trying an "Aug' date, the focus is returned to Jul 07
again.

have tried to capture the value with beforeUpdate but
at runtime the procedure does not seem to get cycled using
the example below

thanks for your help again



-----Original Message-----
Does this help?

Private Sub Calendar1_Click()
If Month(Calendar1.Value) < 7 Or Year(Calendar1.Value)
< 2004 Then
MsgBox "The date selected has to fall within July 2004.
Please try again."
Calendar1.Value = DateSerial(2004, 7, 1)
End If
End Sub

Private Sub UserForm_Initialize()
Calendar1.Value = DateSerial(2004, 7, 1)
End Sub


--
XL2002
Regards

William



wrote in message
...
| thanks Will
|
| i would like to prevent user from being able to go
beyond
| say Jul 31 04; for example when the user selects Aug 08
04,
| the calendar should reset to its' previous date and thus
| the new date is not highlighted
|
| have tried to make it work as follows -
|
| Sub calendar1_beforeupdate(ByVal Cancel As
| MSForms.ReturnBoolean)
|
| If calender1.Value #7/31/2004# Then
| Cancel = True
| End If
|
| End Sub
|
|
|
|
| -----Original Message-----
| Hi Brent
|
| 'Test calendar date falls within 2004
|
| Private Sub CommandButton1_Click()
| Dim calendardate As Date
| Dim earliestdate As Date
| Dim latestdate As Date
|
| earliestdate = DateSerial(2004, 1, 1)
| latestdate = DateSerial(2004, 12, 31)
| calendardate = Calendar1.Value
|
| If calendardate < earliestdate Or calendardate
| latestdate Then
| MsgBox "The date selected of " & Format
(calendardate, "dd
| mmm yyyy") & _
| " does not fall between " & Format(earliestdate, "dd
mmm
| yyyy") & _
| " and " & Format(latestdate, "dd mmm yyyy") & ". Please
| try again."
| Exit Sub
| End If
|
| MsgBox "Do your stuff here"
| End Sub
|
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "brent" wrote in
| message
| ...
| | how do I limit the dates that a calendar allows the
user
| | to select? the calender is placed on a form
| |
| | have tried using the 'beforeUpdate' event and can't
get
| it
| | to work
| |
| | using the MS Calendar Control 10.0
| |
| | any ideas appreciated
|
|
| .
|


.

Hi

This should stop any change to the month or year

Private Sub Calendar1_NewMonth()
Calendar1.Value = Calendar1.Value
End Sub

Private Sub Calendar1_NewYear()
Calendar1.Value = Calendar1.Value
End Sub

Private Sub UserForm_Initialize()
Calendar1.Value = DateSerial(2004, 7, 1)
End Sub


--
XL2002
Regards

William



wrote in message
...
| William,
|
| that is very helpful!
|
| I would like to go a step further though and obtain the
| date that was selected prior to the update
|
| i.e. - if the date with focus is July 7 04 then upon
| trying an "Aug' date, the focus is returned to Jul 07
| again.
|
| have tried to capture the value with beforeUpdate but
| at runtime the procedure does not seem to get cycled using
| the example below
|
| thanks for your help again
|
|
|
| -----Original Message-----
| Does this help?
|
| Private Sub Calendar1_Click()
| If Month(Calendar1.Value) < 7 Or Year(Calendar1.Value)
| < 2004 Then
| MsgBox "The date selected has to fall within July 2004.
| Please try again."
| Calendar1.Value = DateSerial(2004, 7, 1)
| End If
| End Sub
|
| Private Sub UserForm_Initialize()
| Calendar1.Value = DateSerial(2004, 7, 1)
| End Sub
|
|
| --
| XL2002
| Regards
|
| William
|
|
|
| wrote in message
| ...
| | thanks Will
| |
| | i would like to prevent user from being able to go
| beyond
| | say Jul 31 04; for example when the user selects Aug 08
| 04,
| | the calendar should reset to its' previous date and thus
| | the new date is not highlighted
| |
| | have tried to make it work as follows -
| |
| | Sub calendar1_beforeupdate(ByVal Cancel As
| | MSForms.ReturnBoolean)
| |
| | If calender1.Value #7/31/2004# Then
| | Cancel = True
| | End If
| |
| | End Sub
| |
| |
| |
| |
| | -----Original Message-----
| | Hi Brent
| |
| | 'Test calendar date falls within 2004
| |
| | Private Sub CommandButton1_Click()
| | Dim calendardate As Date
| | Dim earliestdate As Date
| | Dim latestdate As Date
| |
| | earliestdate = DateSerial(2004, 1, 1)
| | latestdate = DateSerial(2004, 12, 31)
| | calendardate = Calendar1.Value
| |
| | If calendardate < earliestdate Or calendardate
| | latestdate Then
| | MsgBox "The date selected of " & Format
| (calendardate, "dd
| | mmm yyyy") & _
| | " does not fall between " & Format(earliestdate, "dd
| mmm
| | yyyy") & _
| | " and " & Format(latestdate, "dd mmm yyyy") & ". Please
| | try again."
| | Exit Sub
| | End If
| |
| | MsgBox "Do your stuff here"
| | End Sub
| |
| |
| | --
| | XL2002
| | Regards
| |
| | William
| |
| |
| |
| | "brent" wrote in
| | message
| | ...
| | | how do I limit the dates that a calendar allows the
| user
| | | to select? the calender is placed on a form
| | |
| | | have tried using the 'beforeUpdate' event and can't
| get
| | it
| | | to work
| | |
| | | using the MS Calendar Control 10.0
| | |
| | | any ideas appreciated
| |
| |
| | .
| |
|
|
| .
|