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.

--
Regards,
John

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

"John Keith" wrote in message
...
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.

--
Regards,
John

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