Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
excel 2007 and calendar control
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. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
excel 2007 and calendar control
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. -- Cheers, Shane Devenshire "Sheldon" wrote: 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. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
excel 2007 and calendar control
Shane,
Thanks for the reply. I do not have any code behind the calendar. I simply "linked" the control(under properties) to the cell I want to change. It worked previously, but in 2007 the control makes it a "text" cell every time it updates. I have made a make-shift solution by telling the date cells in my graph's data range that it is a date format using the "date value" function. This works for now. "ShaneDevenshire" wrote: 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. -- Cheers, Shane Devenshire "Sheldon" wrote: 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. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
excel 2007 and calendar control
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. -- Cheers, Shane Devenshire "Sheldon" wrote: Shane, Thanks for the reply. I do not have any code behind the calendar. I simply "linked" the control(under properties) to the cell I want to change. It worked previously, but in 2007 the control makes it a "text" cell every time it updates. I have made a make-shift solution by telling the date cells in my graph's data range that it is a date format using the "date value" function. This works for now. "ShaneDevenshire" wrote: 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. -- Cheers, Shane Devenshire "Sheldon" wrote: 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. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
excel 2007 and calendar control
Shane,
Thank you. That worked perfectly. "ShaneDevenshire" wrote: 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. -- Cheers, Shane Devenshire "Sheldon" wrote: Shane, Thanks for the reply. I do not have any code behind the calendar. I simply "linked" the control(under properties) to the cell I want to change. It worked previously, but in 2007 the control makes it a "text" cell every time it updates. I have made a make-shift solution by telling the date cells in my graph's data range that it is a date format using the "date value" function. This works for now. "ShaneDevenshire" wrote: 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. -- Cheers, Shane Devenshire "Sheldon" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calendar control | Excel Discussion (Misc queries) | |||
I need help with Excel 2007 and VB6 OLE Control | Charts and Charting in Excel | |||
Calendar Control in Excel 2000 can't display date in Excel 2003? | Excel Discussion (Misc queries) | |||
How do I delete a Calendar Control 11.0 from Excel worksheet? | Excel Discussion (Misc queries) | |||
how do you use the ActiveX Calendar control in an Excel Worksheet. | Excel Worksheet Functions |