Can the Autoshape number in the namebox be reset?

[John Keith]

I have a macro that is generating lots of autoshapes each time it is run. At
the top of this macro all shapes are deleted then new ones are generated.
After running this several times the number that is assigned to each object
new, continually increments. It doesn't start over at 1 (even though all
shapes are deleted)


AutoShape 7600 - AutoShape 9020

The next time the macro is run....
AutoShape 9021 - AutoShape 10441

Even after closing down excel and restarting...
Will this number top out? causing errors?
I just want the deletes... to start the numering over. If possible.

 

[Peter T]

Hi John,

I know exactly what you mean, annoying isn't it!

There appears to be an internal Object counter in the sheet which never gets
reset. Renaming objects does not seem to affect the counter.

However, delete ALL objects on the sheet, save, close, and reopen. Only then
does the counter appear to get fully reset.

If you want to save and keep some objects, can reset the counter to the
number of existing objects by creating a copy of the sheet and (optionally)
deleting the old, something like this:

Sub CopyDeleteSheet()
Dim sht As Object, ws As Worksheet
Dim s As String
Set ws = ActiveSheet
s = ws.Name
For Each sht In Sheets
i = i + 1
If sht Is ws Then Exit For
Next
ws.Copy Sheets(i)

ws.Name = s & "Old"

ActiveSheet.Name = s

Application.DisplayAlerts = False
ws.Delete
set ws = nothing
Application.DisplayAlerts = True

End Sub

Alternatively, if it's OK to delete all objects, better to save, close and
reopen.

Not nice, look forward to seeing if anyone has a better solution!

Will this number top out?

I've gone into 100k's without a problem, but I guess it must top out
eventually.

Regards,
Peter T

 

[Peter T]

PS
Forgot to add a warning about the CopyDeleteSheet I posted. If there are
links to the sheet that's going to be deleted on other sheets, they will end
up with REF! It is solvable with routines before & after, to mark linked
formulas and convert to text and reinstate when done. Like I said, not an
ideal solution.

Regards,
Peter T

 

[John Keith]

Thanks for the 2nd post!... I would have been bitten by that bug had I tried
to copy. What I did try was the save, close, unload excel, reload excel,
load the file... But the numbers still were not reset. I had to create a
"new" workbook and copy over the parts that were needed and rebulding the
shapes.

Im using Excel2000 now, could that be the difference?

Thanks again for the responces

 

[Peter T]

Comments in line:

What I did try was the save, close, unload excel, reload excel,
load the file... But the numbers still were not reset.

That's right. The counter will only get reset if there are NO objects on the
sheet. However the counter should get reset on a copied sheet if you save,
close & reopen, even with objects.

I had to create a "new" workbook and copy over the parts that were needed and
rebulding the shapes.

It's only the sheet that needs rebuilding. Each sheet has it's own counter.

Thanks for the 2nd post!... I would have been bitten by that bug had I tried
to copy.

It was careless of me not to include the warning in the first post, glad you
picked it up. Also, I wasn't thinking with the workaround I suggested.
Assuming any links to the sheet that's going to be discarded are only in
formulas in other sheets, I think an approach like this would be simple and
reliable (obviously on a backup):

- Make a copy of the sheet per my macro
- (might need to save / close here to reset counter, not sure)
- Get the name of the new sheet, say NewSheetName
- With calculation manual,
- Search and replace OldSheetname with NewSheetName, looking in formulas,
loop all sheets except NewSheet.
- Then delete the old sheet
- Rename NewSheetName with OldSheetname
- Save, close, reopen.

I imagine a quick search of this ng will find a bit of code to do the search
& replace. More complicated if links are elsewhere besides formulas, eg
charts, or worse in other workbooks. But there are some good addins around
that can edit all links.

Excel seems to have a number of object identifiers and counters that are
frustratingly elusive!

Regards,
Peter T