Number of Objects Limitation in Sheet

[BobTheBuilde]

Hey guys,

For further reference I'm using Excel 2003 - I think memory might be an issue here.

I'm developing a spreadsheet that does a whole lot of deleting and re-making objects.

My question is that, at some point I end up getting a run-time error (1004) when trying to do something similar to the following (creating any object):

[VBA]
ActiveSheet.CheckBoxes.Add(ActiveCell.Left - 1, ActiveCell.Top + 21, 11, ActiveCell.Height * 0.25).Select
[/VBA]


This error occurs for ALL objects, not just checkboxes. I have recently found out, however, that this only happens to one of my sheets (one that does most of the deletining-and-then-recreating object). Furtermore, you'll be interest to hear that the number in the objects text are just above 65536 (the maximum number of rows allowed in a cell!).

For example:
If I were to create a checkbox it would be checkbox65537 (or something like that)
If I were to creat a commandbutton it would be commandbutton65537 (or something like that)

I'm thinkin' all I need to do is tell excel to take some stuff out to the garbage but I can't figure it out! Does anyone know how to fix this?


Thanks in advance

[mikerickson]

Have you tried creating a new sheet, copy/paste the old cells to the new sheet, copy worksheet code(if any) and deleting old sheet, and re-nameing the new sheet to the old name?
I think that will clear your numbering issue.

Would your code work if, instead of creating and deleting objects, you created sufficiant objects at design time and then hide/make_visible rather than delete/make_anew?

[BobTheBuilde]

Hey mikerickson,

I know that the delete-and-recreate sheet thing is a work around, I was hoping I could avoid this as I will be leaving my position (intern work) and noone at the office would be able to figure that out for when the problem re-occurs in the future.

As far as the hiding of the objects, that's something I'd never thought about. Unfortunately, I already have thousands of lines of code that rely upon recreating objects so the hide/unhide objects work-around probably wouldn't be an option (easily).

I was hoping someone would have an easy way to just reset the number after the object created - but I've been searching online and it doesn't seem like there is...

Anyways, thank you very much mikerickson - I think you were the only person to reply to my last thread too. - Much Appreciated


==================UPDATE!=======================

http://www.rondebruin.nl/controlsobjectsworksheet.htm

Hey mikerickson,

I was looking on the internet and found the above link. It finally clicked that these numbers after the objects are indexes of the shapes collection. I was also reading one of your post (http://www.ozgrid.com/forum/showthread.php?t=87510) on ozgrid. Perhaps you know a simple programmatic work-around for this indexing problem?