excel 2007 and calendar control

[Sheldon]

I have spreadsheet developed in Excel 2003 inserted a calendar control in a
spreadsheet so I could easily click on the date and evaluate data from that
date. After I click on the calendar it will poplulate a series of cells and
allow excel to pull data from other places correctly. However, after I
upgraded to excel 2007, the format of this date is not read in my graphs
correctly. On the graphs it is showing up as a 1900 date. I can reformat
the cells (paste special and reformat) and get it to display correctly, but
everytime I click on my calendar control, it reverts back to the other
"incorrect" format. It used to work fine in Office 2003.

I believe it is two quirks. The format of the date shows fine in the cells,
but the graphs will not show it correctly. The other problem is that the
calendar control overwrites any format I have for a cell.

Please help. I can't find any solutions to this quirk. I can send you a
copy of the file if that would help. Thanks.

 

[ShaneDevenshire]

Hi Sheldon,

I take it you have code behind the calendar control that puts the results
into the spreadsheet, could you show us the code?

I am using the calender control in one of my files and it works find in
2007. The file was created in an earlier version but I didn't need to modify
it.

 

[ShaneDevenshire]

Hi Sheldon,

It appears that the calendar control enters the date as text in 2007 while
in 2003 it entered it as a date. To solve this problem use the following
code:

Private Sub Calendar1_Click()
Range("A1") = CDbl(Me.Calendar1.Value)
End Sub

You can add this to the calendar control by putting choosing Developer tab,
Design Mode. Then right click the Calentdar and choose View Code. You may
need to change the name of your calendar and the cell where it is putting the
date.

 

[Sheldon]

Shane,
Thank you. That worked perfectly.

Hi Sheldon,

It appears that the calendar control enters the date as text in 2007 while
in 2003 it entered it as a date. To solve this problem use the following
code:

Private Sub Calendar1_Click()
Range("A1") = CDbl(Me.Calendar1.Value)
End Sub

You can add this to the calendar control by putting choosing Developer tab,
Design Mode. Then right click the Calentdar and choose View Code. You may
need to change the name of your calendar and the cell where it is putting the
date.