I have generated a form for absence requests which requires 3 different dates
to be added. I can use the Calendar Contol 11.0 Object to provide the
calendars, but this means displaying three calendars on the form. It would be
far more elegant if clicking on the date fields brought up a pop-up calendar
from which dates could be selected and which closed once the dates have been
selected. There seem to be pop-up calendars you can purchase but I was
wondering if anybody knew of a free one.

http://www.rondebruin.nl/calendar.htm

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
I have generated a form for absence requests which requires 3 different
dates
to be added. I can use the Calendar Contol 11.0 Object to provide the
calendars, but this means displaying three calendars on the form. It would
be
far more elegant if clicking on the date fields brought up a pop-up
calendar
from which dates could be selected and which closed once the dates have
been
selected. There seem to be pop-up calendars you can purchase but I was
wondering if anybody knew of a free one.

Thanks Bob,
An spot-on response and so quickly too!
Much appreciated,
PeteBE

"Bob Phillips" wrote:

http://www.rondebruin.nl/calendar.htm

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
I have generated a form for absence requests which requires 3 different
dates
to be added. I can use the Calendar Contol 11.0 Object to provide the
calendars, but this means displaying three calendars on the form. It would
be
far more elegant if clicking on the date fields brought up a pop-up
calendar
from which dates could be selected and which closed once the dates have
been
selected. There seem to be pop-up calendars you can purchase but I was
wondering if anybody knew of a free one.

Hi Bob,
Have now implemented your suggestion and it works fine except for one minor
thing. I am using the pop-up calendar to populate a 'From' date and a 'To'
date field. I was using the Data Validation menu option to check that the
'To' date is greater than the 'From' date. This does not seem to work when
the fields are being populated by the calendar. Is there a way to overcome
this?
Regards,
PeteBE

"Bob Phillips" wrote:

http://www.rondebruin.nl/calendar.htm

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
I have generated a form for absence requests which requires 3 different
dates
to be added. I can use the Calendar Contol 11.0 Object to provide the
calendars, but this means displaying three calendars on the form. It would
be
far more elegant if clicking on the date fields brought up a pop-up
calendar
from which dates could be selected and which closed once the dates have
been
selected. There seem to be pop-up calendars you can purchase but I was
wondering if anybody knew of a free one.

Pete,

Maybe ditch the data validation and use event code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_DATE_TO As String = "H1" '<== date to, change to suit
Const WS_DATE_FROM As String = "J1" '<== date from, change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Intersect(Target, Me.Range(WS_DATE_FROM)) Is Nothing And _
Intersect(Target, Me.Range(WS_DATE_TO)) Is Nothing Then
Exit Sub
Else
If Me.Range(WS_DATE_FROM) < "" And Me.Range(WS_DATE_TO) < "" Then
If Me.Range(WS_DATE_FROM) = Me.Range(WS_DATE_TO) Then
MsgBox "Dates invalid"
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
Hi Bob,
Have now implemented your suggestion and it works fine except for one
minor
thing. I am using the pop-up calendar to populate a 'From' date and a 'To'
date field. I was using the Data Validation menu option to check that
the
'To' date is greater than the 'From' date. This does not seem to work when
the fields are being populated by the calendar. Is there a way to overcome
this?
Regards,
PeteBE

"Bob Phillips" wrote:

http://www.rondebruin.nl/calendar.htm

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
I have generated a form for absence requests which requires 3 different
dates
to be added. I can use the Calendar Contol 11.0 Object to provide the
calendars, but this means displaying three calendars on the form. It
would
be
far more elegant if clicking on the date fields brought up a pop-up
calendar
from which dates could be selected and which closed once the dates have
been
selected. There seem to be pop-up calendars you can purchase but I was
wondering if anybody knew of a free one.

Hi Bob,
Sorry to be a pain. Your last e-mail seems to stop abruptly. I don't have
the details about where to insert the code. I have tried inserting it where I
think is right but it seems to have stopped the existing code working!
Regards,
PeteBE
"Bob Phillips" wrote:

Pete,

Maybe ditch the data validation and use event code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_DATE_TO As String = "H1" '<== date to, change to suit
Const WS_DATE_FROM As String = "J1" '<== date from, change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Intersect(Target, Me.Range(WS_DATE_FROM)) Is Nothing And _
Intersect(Target, Me.Range(WS_DATE_TO)) Is Nothing Then
Exit Sub
Else
If Me.Range(WS_DATE_FROM) < "" And Me.Range(WS_DATE_TO) < "" Then
If Me.Range(WS_DATE_FROM) = Me.Range(WS_DATE_TO) Then
MsgBox "Dates invalid"
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
Hi Bob,
Have now implemented your suggestion and it works fine except for one
minor
thing. I am using the pop-up calendar to populate a 'From' date and a 'To'
date field. I was using the Data Validation menu option to check that
the
'To' date is greater than the 'From' date. This does not seem to work when
the fields are being populated by the calendar. Is there a way to overcome
this?
Regards,
PeteBE

"Bob Phillips" wrote:

http://www.rondebruin.nl/calendar.htm

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
I have generated a form for absence requests which requires 3 different
dates
to be added. I can use the Calendar Contol 11.0 Object to provide the
calendars, but this means displaying three calendars on the form. It
would
be
far more elegant if clicking on the date fields brought up a pop-up
calendar
from which dates could be selected and which closed once the dates have
been
selected. There seem to be pop-up calendars you can purchase but I was
wondering if anybody knew of a free one.

The installation details are after the code Pete.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
Hi Bob,
Sorry to be a pain. Your last e-mail seems to stop abruptly. I don't have
the details about where to insert the code. I have tried inserting it
where I
think is right but it seems to have stopped the existing code working!
Regards,
PeteBE
"Bob Phillips" wrote:

Pete,

Maybe ditch the data validation and use event code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_DATE_TO As String = "H1" '<== date to, change to suit
Const WS_DATE_FROM As String = "J1" '<== date from, change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Intersect(Target, Me.Range(WS_DATE_FROM)) Is Nothing And _
Intersect(Target, Me.Range(WS_DATE_TO)) Is Nothing Then
Exit Sub
Else
If Me.Range(WS_DATE_FROM) < "" And Me.Range(WS_DATE_TO) < ""
Then
If Me.Range(WS_DATE_FROM) = Me.Range(WS_DATE_TO) Then
MsgBox "Dates invalid"
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
Hi Bob,
Have now implemented your suggestion and it works fine except for one
minor
thing. I am using the pop-up calendar to populate a 'From' date and a
'To'
date field. I was using the Data Validation menu option to check that
the
'To' date is greater than the 'From' date. This does not seem to work
when
the fields are being populated by the calendar. Is there a way to
overcome
this?
Regards,
PeteBE

"Bob Phillips" wrote:

http://www.rondebruin.nl/calendar.htm

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
I have generated a form for absence requests which requires 3
different
dates
to be added. I can use the Calendar Contol 11.0 Object to provide
the
calendars, but this means displaying three calendars on the form. It
would
be
far more elegant if clicking on the date fields brought up a pop-up
calendar
from which dates could be selected and which closed once the dates
have
been
selected. There seem to be pop-up calendars you can purchase but I
was
wondering if anybody knew of a free one.

Bob,
It is possible I am being thick, but the only details I have on the e-mail I
can see a
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click
Right-click on what? I am tempted to ask.
Regards,
PeteBE
"Bob Phillips" wrote:

The installation details are after the code Pete.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
Hi Bob,
Sorry to be a pain. Your last e-mail seems to stop abruptly. I don't have
the details about where to insert the code. I have tried inserting it
where I
think is right but it seems to have stopped the existing code working!
Regards,
PeteBE
"Bob Phillips" wrote:

Pete,

Maybe ditch the data validation and use event code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_DATE_TO As String = "H1" '<== date to, change to suit
Const WS_DATE_FROM As String = "J1" '<== date from, change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Intersect(Target, Me.Range(WS_DATE_FROM)) Is Nothing And _
Intersect(Target, Me.Range(WS_DATE_TO)) Is Nothing Then
Exit Sub
Else
If Me.Range(WS_DATE_FROM) < "" And Me.Range(WS_DATE_TO) < ""
Then
If Me.Range(WS_DATE_FROM) = Me.Range(WS_DATE_TO) Then
MsgBox "Dates invalid"
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
Hi Bob,
Have now implemented your suggestion and it works fine except for one
minor
thing. I am using the pop-up calendar to populate a 'From' date and a
'To'
date field. I was using the Data Validation menu option to check that
the
'To' date is greater than the 'From' date. This does not seem to work
when
the fields are being populated by the calendar. Is there a way to
overcome
this?
Regards,
PeteBE

"Bob Phillips" wrote:

http://www.rondebruin.nl/calendar.htm

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
I have generated a form for absence requests which requires 3
different
dates
to be added. I can use the Calendar Contol 11.0 Object to provide
the
calendars, but this means displaying three calendars on the form. It
would
be
far more elegant if clicking on the date fields brought up a pop-up
calendar
from which dates could be selected and which closed once the dates
have
been
selected. There seem to be pop-up calendars you can purchase but I
was
wondering if anybody knew of a free one.

Sorry, didn't spot it got truncated. See if this is any better

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
Bob,
It is possible I am being thick, but the only details I have on the e-mail
I
can see a
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click
Right-click on what? I am tempted to ask.
Regards,
PeteBE
"Bob Phillips" wrote:

The installation details are after the code Pete.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
Hi Bob,
Sorry to be a pain. Your last e-mail seems to stop abruptly. I don't
have
the details about where to insert the code. I have tried inserting it
where I
think is right but it seems to have stopped the existing code working!
Regards,
PeteBE
"Bob Phillips" wrote:

Pete,

Maybe ditch the data validation and use event code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_DATE_TO As String = "H1" '<== date to, change to suit
Const WS_DATE_FROM As String = "J1" '<== date from, change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Intersect(Target, Me.Range(WS_DATE_FROM)) Is Nothing And _
Intersect(Target, Me.Range(WS_DATE_TO)) Is Nothing Then
Exit Sub
Else
If Me.Range(WS_DATE_FROM) < "" And Me.Range(WS_DATE_TO) < ""
Then
If Me.Range(WS_DATE_FROM) = Me.Range(WS_DATE_TO) Then
MsgBox "Dates invalid"
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
Hi Bob,
Have now implemented your suggestion and it works fine except for
one
minor
thing. I am using the pop-up calendar to populate a 'From' date and
a
'To'
date field. I was using the Data Validation menu option to check
that
the
'To' date is greater than the 'From' date. This does not seem to
work
when
the fields are being populated by the calendar. Is there a way to
overcome
this?
Regards,
PeteBE

"Bob Phillips" wrote:

http://www.rondebruin.nl/calendar.htm

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"PeteBE" wrote in message
...
I have generated a form for absence requests which requires 3
different
dates
to be added. I can use the Calendar Contol 11.0 Object to provide
the
calendars, but this means displaying three calendars on the form.
It
would
be
far more elegant if clicking on the date fields brought up a
pop-up
calendar
from which dates could be selected and which closed once the
dates
have
been
selected. There seem to be pop-up calendars you can purchase but
I
was
wondering if anybody knew of a free one.