How do I keep Calendar Control from closing?
 

I have used the Excel Calendar Control to put a calendar on my worksheet. I
like the factor that I can select a date and it will put the date in a
specified cell. However I want to be able to keep the calendar active (open)
on the worksheet without having to reopen it after i select the date, so I
could select other datesif desired. Is there a VBA that can be added that
does that?

How do I keep Calendar Control from closing?
 

Hello Roy,

Once you have made your selection the control generates an event called
CloseUp. This event doesn't pass or return any arguments to the Date
Time picker class which means you can't interrupt the process and keep
the calendar displayed.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=506102

How do I keep Calendar Control from closing?
 

Hi Roy,

If you insert a calendar control, it would normally remain visibile.

Do you have code associated with the calendar control? If so, post the
relevant code.


---
Regards,
Norman


"Roy Ringrose" wrote in message
...
I have used the Excel Calendar Control to put a calendar on my worksheet.
I
like the factor that I can select a date and it will put the date in a
specified cell. However I want to be able to keep the calendar active
(open)
on the worksheet without having to reopen it after i select the date, so I
could select other datesif desired. Is there a VBA that can be added that
does that?

How do I keep Calendar Control from closing?
 

I've used the normal "Excel Calendar Control" VBA thats in the Excel addins.
My particular one is "Calendar Control 10.0". When opened it displays the
calendar with the ability to "click" specific dates that then generate a date
in a specified cell. If you were to select another date then the specified
cell also changes, but if you were to go to another cell other than the date
cell, the calendar closes. I would like to keep the calendar open and not
have to open the calendar control each time I want to select a date.

Here are the directions that I was given.

Open the workbook for the calendar. It is a good idea to use your
Personal.xls for this, in which case you should first go to WindowUnhide

Go to ToolsMacroVisual Basic Editor (Alt+F11).

Go to InsertUserForm from within the VBE. This should automatically display
the Control Toolbox, if not go to ViewToolbox

Right click on the Toolbox and select Additional Controls

Scroll through the list until you see: Calendar Control 10.0 (number will
differ depending on Excel version) and check the checkbox and click OK

Now click the Calendar that is now part of the Toolbox and then click on the
UserForm we inserted in step 3.

Use the Size Handles on both the UserForm and the Calendar Control to make
them both a reasonable size. See Example below.

Now ensure the UserForm is selected (as shown above) then go to
ViewProperties Window (F4)

Select Caption from the Properties Window and replace: UserForm1 with the
word Calendar.

Now go to ViewCode (F7) and in the white Private Module in front of you,
add the code exactly as show below:

Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat="mm/dd/yy"
End Sub

Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub

Now go to InsertModule and in this Public Module place this code
Sub ShowIt()
UserForm1.Show
End Sub

Ok, nearly done. Click the top right X (or push Alt+F11) to return back to
Excel.
Go to ToolsMacroMacros (Alt+F8) and then select ShowIt click Options and
assign a shortcut key and you're done.


"Norman Jones" wrote:

Hi Roy,

If you insert a calendar control, it would normally remain visibile.

Do you have code associated with the calendar control? If so, post the
relevant code.


---
Regards,
Norman


"Roy Ringrose" wrote in message
...
I have used the Excel Calendar Control to put a calendar on my worksheet.
I
like the factor that I can select a date and it will put the date in a
specified cell. However I want to be able to keep the calendar active
(open)
on the worksheet without having to reopen it after i select the date, so I
could select other datesif desired. Is there a VBA that can be added that
does that?

How do I keep Calendar Control from closing?
 

Hi Roy,

The directions you received are excellent and the calendar should remain
available until the form's X close button is pressed.

However, if you want to keep the calendar form open and still be able to
work in the workbook, I would suggest that you change:

Sub ShowIt()
UserForm1.Show
End Sub

to:

Sub ShowIt()
UserForm1.Show vbModeless
End Sub

Again the calendar form will remain open until the X close button is pressed
but, this way, the form can remain open while you work in Excel.


---
Regards,
Norman



"Roy Ringrose" wrote in message
...
I've used the normal "Excel Calendar Control" VBA thats in the Excel
addins.
My particular one is "Calendar Control 10.0". When opened it displays the
calendar with the ability to "click" specific dates that then generate a
date
in a specified cell. If you were to select another date then the
specified
cell also changes, but if you were to go to another cell other than the
date
cell, the calendar closes. I would like to keep the calendar open and not
have to open the calendar control each time I want to select a date.

Here are the directions that I was given.

Open the workbook for the calendar. It is a good idea to use your
Personal.xls for this, in which case you should first go to WindowUnhide

Go to ToolsMacroVisual Basic Editor (Alt+F11).

Go to InsertUserForm from within the VBE. This should automatically
display
the Control Toolbox, if not go to ViewToolbox

Right click on the Toolbox and select Additional Controls

Scroll through the list until you see: Calendar Control 10.0 (number will
differ depending on Excel version) and check the checkbox and click OK

Now click the Calendar that is now part of the Toolbox and then click on
the
UserForm we inserted in step 3.

Use the Size Handles on both the UserForm and the Calendar Control to make
them both a reasonable size. See Example below.

Now ensure the UserForm is selected (as shown above) then go to
ViewProperties Window (F4)

Select Caption from the Properties Window and replace: UserForm1 with the
word Calendar.

Now go to ViewCode (F7) and in the white Private Module in front of you,
add the code exactly as show below:

Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat="mm/dd/yy"
End Sub

Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub

Now go to InsertModule and in this Public Module place this code
Sub ShowIt()
UserForm1.Show
End Sub

Ok, nearly done. Click the top right X (or push Alt+F11) to return back to
Excel.
Go to ToolsMacroMacros (Alt+F8) and then select ShowIt click Options and
assign a shortcut key and you're done.


"Norman Jones" wrote:

Hi Roy,

If you insert a calendar control, it would normally remain visibile.

Do you have code associated with the calendar control? If so, post the
relevant code.


---
Regards,
Norman


"Roy Ringrose" wrote in message
...
I have used the Excel Calendar Control to put a calendar on my
worksheet.
I
like the factor that I can select a date and it will put the date in a
specified cell. However I want to be able to keep the calendar active
(open)
on the worksheet without having to reopen it after i select the date,
so I
could select other datesif desired. Is there a VBA that can be added
that
does that?

How do I keep Calendar Control from closing?
 

I finally got to try your recommendation. Thanks, That seemed to do the job,
though I would have preferred to prevent the customer from "closing" the
calendar.

This is going into a prgram that the customer selects the date to generate
other information. Similar to what occurs in the travel or car rental
programs.

As an alternative, Is there an easy way to create an icon, that in the event
the customer accidently "closes" the calendar, that they would be able to
reopen it through the use of an Icon?

Thanks in advance, you've been a great help so far.

Roy R.


"Norman Jones" wrote:

Hi Roy,

The directions you received are excellent and the calendar should remain
available until the form's X close button is pressed.

However, if you want to keep the calendar form open and still be able to
work in the workbook, I would suggest that you change:

Sub ShowIt()
UserForm1.Show
End Sub

to:

Sub ShowIt()
UserForm1.Show vbModeless
End Sub

Again the calendar form will remain open until the X close button is pressed
but, this way, the form can remain open while you work in Excel.


---
Regards,
Norman



"Roy Ringrose" wrote in message
...
I've used the normal "Excel Calendar Control" VBA thats in the Excel
addins.
My particular one is "Calendar Control 10.0". When opened it displays the
calendar with the ability to "click" specific dates that then generate a
date
in a specified cell. If you were to select another date then the
specified
cell also changes, but if you were to go to another cell other than the
date
cell, the calendar closes. I would like to keep the calendar open and not
have to open the calendar control each time I want to select a date.

Here are the directions that I was given.

Open the workbook for the calendar. It is a good idea to use your
Personal.xls for this, in which case you should first go to WindowUnhide

Go to ToolsMacroVisual Basic Editor (Alt+F11).

Go to InsertUserForm from within the VBE. This should automatically
display
the Control Toolbox, if not go to ViewToolbox

Right click on the Toolbox and select Additional Controls

Scroll through the list until you see: Calendar Control 10.0 (number will
differ depending on Excel version) and check the checkbox and click OK

Now click the Calendar that is now part of the Toolbox and then click on
the
UserForm we inserted in step 3.

Use the Size Handles on both the UserForm and the Calendar Control to make
them both a reasonable size. See Example below.

Now ensure the UserForm is selected (as shown above) then go to
ViewProperties Window (F4)

Select Caption from the Properties Window and replace: UserForm1 with the
word Calendar.

Now go to ViewCode (F7) and in the white Private Module in front of you,
add the code exactly as show below:

Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat="mm/dd/yy"
End Sub

Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub

Now go to InsertModule and in this Public Module place this code
Sub ShowIt()
UserForm1.Show
End Sub

Ok, nearly done. Click the top right X (or push Alt+F11) to return back to
Excel.
Go to ToolsMacroMacros (Alt+F8) and then select ShowIt click Options and
assign a shortcut key and you're done.


"Norman Jones" wrote:

Hi Roy,

If you insert a calendar control, it would normally remain visibile.

Do you have code associated with the calendar control? If so, post the
relevant code.


---
Regards,
Norman


"Roy Ringrose" wrote in message
...
I have used the Excel Calendar Control to put a calendar on my
worksheet.
I
like the factor that I can select a date and it will put the date in a
specified cell. However I want to be able to keep the calendar active
(open)
on the worksheet without having to reopen it after i select the date,
so I
could select other datesif desired. Is there a VBA that can be added
that
does that?

How do I keep Calendar Control from closing?
 

Hi Roy,

As an alternative, Is there an easy way to create an icon, that in the
event
the customer accidently "closes" the calendar, that they would be able to
reopen it through the use of an Icon?

Assign your existing code:

Sub ShowIt()
UserForm1.Show vbModeless
End Sub

to a button.


---
Regards,
Norman



"Roy Ringrose" wrote in message
...
I finally got to try your recommendation. Thanks, That seemed to do the
job,
though I would have preferred to prevent the customer from "closing" the
calendar.

This is going into a prgram that the customer selects the date to generate
other information. Similar to what occurs in the travel or car rental
programs.

As an alternative, Is there an easy way to create an icon, that in the
event
the customer accidently "closes" the calendar, that they would be able to
reopen it through the use of an Icon?

Thanks in advance, you've been a great help so far.

Roy R.


"Norman Jones" wrote:

Hi Roy,

The directions you received are excellent and the calendar should remain
available until the form's X close button is pressed.

However, if you want to keep the calendar form open and still be able to
work in the workbook, I would suggest that you change:

Sub ShowIt()
UserForm1.Show
End Sub

to:

Sub ShowIt()
UserForm1.Show vbModeless
End Sub

Again the calendar form will remain open until the X close button is
pressed
but, this way, the form can remain open while you work in Excel.


---
Regards,
Norman



"Roy Ringrose" wrote in message
...
I've used the normal "Excel Calendar Control" VBA thats in the Excel
addins.
My particular one is "Calendar Control 10.0". When opened it displays
the
calendar with the ability to "click" specific dates that then generate
a
date
in a specified cell. If you were to select another date then the
specified
cell also changes, but if you were to go to another cell other than the
date
cell, the calendar closes. I would like to keep the calendar open and
not
have to open the calendar control each time I want to select a date.

Here are the directions that I was given.

Open the workbook for the calendar. It is a good idea to use your
Personal.xls for this, in which case you should first go to
WindowUnhide

Go to ToolsMacroVisual Basic Editor (Alt+F11).

Go to InsertUserForm from within the VBE. This should automatically
display
the Control Toolbox, if not go to ViewToolbox

Right click on the Toolbox and select Additional Controls

Scroll through the list until you see: Calendar Control 10.0 (number
will
differ depending on Excel version) and check the checkbox and click OK

Now click the Calendar that is now part of the Toolbox and then click
on
the
UserForm we inserted in step 3.

Use the Size Handles on both the UserForm and the Calendar Control to
make
them both a reasonable size. See Example below.

Now ensure the UserForm is selected (as shown above) then go to
ViewProperties Window (F4)

Select Caption from the Properties Window and replace: UserForm1 with
the
word Calendar.

Now go to ViewCode (F7) and in the white Private Module in front of
you,
add the code exactly as show below:

Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat="mm/dd/yy"
End Sub

Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub

Now go to InsertModule and in this Public Module place this code
Sub ShowIt()
UserForm1.Show
End Sub

Ok, nearly done. Click the top right X (or push Alt+F11) to return back
to
Excel.
Go to ToolsMacroMacros (Alt+F8) and then select ShowIt click Options
and
assign a shortcut key and you're done.


"Norman Jones" wrote:

Hi Roy,

If you insert a calendar control, it would normally remain visibile.

Do you have code associated with the calendar control? If so, post the
relevant code.


---
Regards,
Norman


"Roy Ringrose" wrote in
message
...
I have used the Excel Calendar Control to put a calendar on my
worksheet.
I
like the factor that I can select a date and it will put the date in
a
specified cell. However I want to be able to keep the calendar
active
(open)
on the worksheet without having to reopen it after i select the
date,
so I
could select other datesif desired. Is there a VBA that can be
added
that
does that?