Calendar

[wzel]

Is it possible to format a cell so that when it is clicked on, a drop down calendar displays and you can choose the date from the calendar to enter into the cell? If so, could someone describe the procedure. Thank You.

 

[Rocky E]

This is for dwhj... or anyone else

A Double click anywhere on the workseet will activate this control, but the result is always returned to A1. The line that does this is:

ActiveSheet.Range("A1") = Calendar1.Value


How can I change this to return the value to the selected cell, the one that was double clicked in the first place.

Rocky...

 

[dwhj]

On 2002-05-07 06:48, Rocky E wrote:
This is for dwhj... or anyone else

A Double click anywhere on the workseet will activate this control, but the result is always returned to A1. The line that does this is:

ActiveSheet.Range("A1") = Calendar1.Value


How can I change this to return the value to the selected cell, the one that was double clicked in the first place.

Rocky...

There was an error in my first posting that I corrected in the second post.
Change the BeforeDoubleClick procedure to :-

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$1" Then
UserForm1.Show
Application.EnableEvents = False
Target.Offset(1, 0).Select
Application.EnableEvents = True
End If
End Sub

With the revised code from the first posting, the calendar will appear when A1 is selected or double clicked, and the date selected will be put in A1.

The code in the second post will display the calendar when a cell in column B is double-clicked, and the date selected will be put in the active cell.

If you need the calendar activated under some other conditions, the code needs to be adjusted accordingly. Post again if you have difficulty in changing the code.

 

[Rocky E]

Well no I got that, but the button routine that actualy returns the value to "A1" every time.

Private Sub CommandButton1_Click()
ActiveSheet.Range("A1") = Calendar1.Value
Me.Hide
End Sub
I need to pass the range from the other routine here (I think).

Thanks for hanging with me!!!

Rocky...

 

[NateO]

Try:

activesheet.selection = Calendar1.Value

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-07 19:33

 

[dwhj]

On 2002-05-07 18:50, Rocky E wrote:
Well no I got that, but the button routine that actualy returns the value to "A1" every time.

Private Sub CommandButton1_Click()
ActiveSheet.Range("A1") = Calendar1.Value
Me.Hide
End Sub
I need to pass the range from the other routine here (I think).

Thanks for hanging with me!!!

Rocky...

Not if you use the code in my second post, as I advised you to do. Which is :-

Private Sub CommandButton1_Click()
ActiveCell = Calendar1.Value
Me.Hide
End Sub

 

[Rocky E]

OK, now I got it!
Thank You!(Again)

Rocky...
(thanks to you too Nate)

 

[orivera]

Is this the same for excel 97?

Thanks

 

[dwhj]

On 2002-05-08 06:23, orivera wrote:
Is this the same for excel 97?

Thanks

Yes, except that the Control is Calendar Control 8.0

 

[orivera]

Hi:

I have it working but it always gives me the same date no matter how I change it. May 2002 is what I get.

Thanks

Orlando

 

[orivera]

I got it.

Thank you very much.

Orlando