Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resetting the chart count
Hi,
I've written some code that automatically generates about 30 small graphs. Periodically these have to be deleted and regenerated. This could happen as many as 20 times a day. I've noticed that each new chart is assigned a number which increments by 1 but it does not reset when I close Excel. Although it does not affect the program, I'm concerned that within a year the count may grow to a ridiculously large number. Is there any way I can reset the count back to zero when i close the program. Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resetting the chart count
From my experience with excel 2003 restarts numbering when excel closes and reopens. If I have sheets : sheet2, sheet3, sheet4 in a workbooks and close excel then reopen excel. I next add a new sheet it gets assigned sheet1. If I add another sheet it will get assigned sheet5. The graphs work the same way. I'm not sure if you are really deleting the graphs or not closing excel. Closing the workbook without closing excel the numbering probably will continue to count to higher numbers. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=182745 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resetting the chart count
The Sheet's object id counter increments each time any sort of object is
added to the sheet, it doesn't decrement when items are deleted. There's no problem if the id increases to 64k, even after then for most situations there's no problem. The only way to reset the object counter is to delete all objects (not only chartobjects), eg Activesheet.Drawingobjects.Delete then save, close and reopen the file. Maybe in your situation if/when all the charts need to be deleted, also delete the sheet and add a new one. But as I say, although intuitively the high number looks problematic, in practice it is very unlikely ever to become so. Regards, Peter T "Ben" wrote in message ... Hi, I've written some code that automatically generates about 30 small graphs. Periodically these have to be deleted and regenerated. This could happen as many as 20 times a day. I've noticed that each new chart is assigned a number which increments by 1 but it does not reset when I close Excel. Although it does not affect the program, I'm concerned that within a year the count may grow to a ridiculously large number. Is there any way I can reset the count back to zero when i close the program. Thank you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resetting the chart count
Thank you for your comments but there seems to be another related problem.
The line of code Charts.add is causing a compile error whenever it is encountered. The error message is "Method or data member not found" Here is the snippet of code which I've extracted to use as a test Sub Testgraph() Range("E6:E22").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("30Graphs").Range("E6:E22") ActiveChart.Location Whe=xlLocationAsObject, Name:="30Graphs" Range("H10").Select End Sub If I copy the exact code onto a new workbook it will work without a compile error. Has the first workbook been corrupted in some way or have I just added and deleted too many charts for it to handle ? "Peter T" wrote: The Sheet's object id counter increments each time any sort of object is added to the sheet, it doesn't decrement when items are deleted. There's no problem if the id increases to 64k, even after then for most situations there's no problem. The only way to reset the object counter is to delete all objects (not only chartobjects), eg Activesheet.Drawingobjects.Delete then save, close and reopen the file. Maybe in your situation if/when all the charts need to be deleted, also delete the sheet and add a new one. But as I say, although intuitively the high number looks problematic, in practice it is very unlikely ever to become so. Regards, Peter T "Ben" wrote in message ... Hi, I've written some code that automatically generates about 30 small graphs. Periodically these have to be deleted and regenerated. This could happen as many as 20 times a day. I've noticed that each new chart is assigned a number which increments by 1 but it does not reset when I close Excel. Although it does not affect the program, I'm concerned that within a year the count may grow to a ridiculously large number. Is there any way I can reset the count back to zero when i close the program. Thank you . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resetting the chart count
I really don't know what the problem is there.
Charts.Add This adds a Chart-sheet (later changed to an embedded chart), how many chart-sheets do you have in the workbook? Did this problem suddenly start happening after changing something else? FWIW there is a different and I think better way to add the chart which might avoid the problem, however best first to clear this up. Regards, Peter T "Ben" wrote in message ... Thank you for your comments but there seems to be another related problem. The line of code Charts.add is causing a compile error whenever it is encountered. The error message is "Method or data member not found" Here is the snippet of code which I've extracted to use as a test Sub Testgraph() Range("E6:E22").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("30Graphs").Range("E6:E22") ActiveChart.Location Whe=xlLocationAsObject, Name:="30Graphs" Range("H10").Select End Sub If I copy the exact code onto a new workbook it will work without a compile error. Has the first workbook been corrupted in some way or have I just added and deleted too many charts for it to handle ? "Peter T" wrote: The Sheet's object id counter increments each time any sort of object is added to the sheet, it doesn't decrement when items are deleted. There's no problem if the id increases to 64k, even after then for most situations there's no problem. The only way to reset the object counter is to delete all objects (not only chartobjects), eg Activesheet.Drawingobjects.Delete then save, close and reopen the file. Maybe in your situation if/when all the charts need to be deleted, also delete the sheet and add a new one. But as I say, although intuitively the high number looks problematic, in practice it is very unlikely ever to become so. Regards, Peter T "Ben" wrote in message ... Hi, I've written some code that automatically generates about 30 small graphs. Periodically these have to be deleted and regenerated. This could happen as many as 20 times a day. I've noticed that each new chart is assigned a number which increments by 1 but it does not reset when I close Excel. Although it does not affect the program, I'm concerned that within a year the count may grow to a ridiculously large number. Is there any way I can reset the count back to zero when i close the program. Thank you . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resetting the chart count
The problem with Charts.add is solved. The reason the code did not run is
that I renamed the module name from the default "Module1" to "Charts". This must be a reserved word because as soon as it encountered the line "Charts.add" it would stop with a compile error. Renaming the module from "Charts" to "DoGraphs" has solved the problem but it took hours to discover that. Thanks for your help. You suggested that there is a better alternative to Charts.add. I would be very interested in that. "Peter T" wrote: I really don't know what the problem is there. Charts.Add This adds a Chart-sheet (later changed to an embedded chart), how many chart-sheets do you have in the workbook? Did this problem suddenly start happening after changing something else? FWIW there is a different and I think better way to add the chart which might avoid the problem, however best first to clear this up. Regards, Peter T "Ben" wrote in message ... Thank you for your comments but there seems to be another related problem. The line of code Charts.add is causing a compile error whenever it is encountered. The error message is "Method or data member not found" Here is the snippet of code which I've extracted to use as a test Sub Testgraph() Range("E6:E22").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("30Graphs").Range("E6:E22") ActiveChart.Location Whe=xlLocationAsObject, Name:="30Graphs" Range("H10").Select End Sub If I copy the exact code onto a new workbook it will work without a compile error. Has the first workbook been corrupted in some way or have I just added and deleted too many charts for it to handle ? "Peter T" wrote: The Sheet's object id counter increments each time any sort of object is added to the sheet, it doesn't decrement when items are deleted. There's no problem if the id increases to 64k, even after then for most situations there's no problem. The only way to reset the object counter is to delete all objects (not only chartobjects), eg Activesheet.Drawingobjects.Delete then save, close and reopen the file. Maybe in your situation if/when all the charts need to be deleted, also delete the sheet and add a new one. But as I say, although intuitively the high number looks problematic, in practice it is very unlikely ever to become so. Regards, Peter T "Ben" wrote in message ... Hi, I've written some code that automatically generates about 30 small graphs. Periodically these have to be deleted and regenerated. This could happen as many as 20 times a day. I've noticed that each new chart is assigned a number which increments by 1 but it does not reset when I close Excel. Although it does not affect the program, I'm concerned that within a year the count may grow to a ridiculously large number. Is there any way I can reset the count back to zero when i close the program. Thank you . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resetting the chart count
I'm glad you found that, we could have gone on a long time trying to track
that one down! FWIW if the problem could also have been resolved had you done - ActiveWorkbook.Chart.Add But best never to use any Keyword either as a module or a variable name. Another thing to keep in the back of your mind never use the same name as a Defined Name and a procedure name. Here's another way to add your chart Sub Testgraph2() Dim rngPos As Range Dim ws As Worksheet Dim chtObj As ChartObject Dim cht As Chart Set ws = ActiveWorkbook.Worksheets("30Graphs") Set rngPos = ws.Range("F6:M21") With rngPos Set chtObj = ws.ChartObjects.Add( _ .Left + 9, .Top + 3, _ .Width, .Height) End With Set cht = chtObj.Chart cht.SetSourceData ws.Range("E6:E22") cht.ChartType = xlLineMarkers End Sub Note there's no need to select anything, even the sheet does not need to be active. However if the workbook is not active change Activeworkbook.etc to Workbooks("wbName.xls").etc. To assist with more changes, type "cht." and look for intellisense after the dot. Regards, Peter T "Ben" wrote in message ... The problem with Charts.add is solved. The reason the code did not run is that I renamed the module name from the default "Module1" to "Charts". This must be a reserved word because as soon as it encountered the line "Charts.add" it would stop with a compile error. Renaming the module from "Charts" to "DoGraphs" has solved the problem but it took hours to discover that. Thanks for your help. You suggested that there is a better alternative to Charts.add. I would be very interested in that. "Peter T" wrote: I really don't know what the problem is there. Charts.Add This adds a Chart-sheet (later changed to an embedded chart), how many chart-sheets do you have in the workbook? Did this problem suddenly start happening after changing something else? FWIW there is a different and I think better way to add the chart which might avoid the problem, however best first to clear this up. Regards, Peter T "Ben" wrote in message ... Thank you for your comments but there seems to be another related problem. The line of code Charts.add is causing a compile error whenever it is encountered. The error message is "Method or data member not found" Here is the snippet of code which I've extracted to use as a test Sub Testgraph() Range("E6:E22").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("30Graphs").Range("E6:E22") ActiveChart.Location Whe=xlLocationAsObject, Name:="30Graphs" Range("H10").Select End Sub If I copy the exact code onto a new workbook it will work without a compile error. Has the first workbook been corrupted in some way or have I just added and deleted too many charts for it to handle ? "Peter T" wrote: The Sheet's object id counter increments each time any sort of object is added to the sheet, it doesn't decrement when items are deleted. There's no problem if the id increases to 64k, even after then for most situations there's no problem. The only way to reset the object counter is to delete all objects (not only chartobjects), eg Activesheet.Drawingobjects.Delete then save, close and reopen the file. Maybe in your situation if/when all the charts need to be deleted, also delete the sheet and add a new one. But as I say, although intuitively the high number looks problematic, in practice it is very unlikely ever to become so. Regards, Peter T "Ben" wrote in message ... Hi, I've written some code that automatically generates about 30 small graphs. Periodically these have to be deleted and regenerated. This could happen as many as 20 times a day. I've noticed that each new chart is assigned a number which increments by 1 but it does not reset when I close Excel. Although it does not affect the program, I'm concerned that within a year the count may grow to a ridiculously large number. Is there any way I can reset the count back to zero when i close the program. Thank you . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resetting the chart count
"Peter T" <peter_t@discussions wrote in message
Typo - I'm glad you found that, we could have gone on a long time trying to track that one down! FWIW if the problem could also have been resolved had you done - ActiveWorkbook.Chart.Add <snip should read ActiveWorkbook.Charts.Add Peter T |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resetting the chart count
Thank you so much for your help "Peter T" wrote: "Peter T" <peter_t@discussions wrote in message Typo - I'm glad you found that, we could have gone on a long time trying to track that one down! FWIW if the problem could also have been resolved had you done - ActiveWorkbook.Chart.Add <snip should read ActiveWorkbook.Charts.Add Peter T . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart worksheet names keep resetting | Charts and Charting in Excel | |||
Slightly Urgent: Problem Resetting Chart Value Ranges | Excel Programming | |||
Why are my options resetting themselves? | Excel Discussion (Misc queries) | |||
having the chart NOT count zero | Charts and Charting in Excel | |||
Resetting chart ranges | Excel Programming |