Reset object number

When I insert an object, excel will assign a new number.
How can I reset such number to 1 after deleting all existing objects?

I suspect that you'd have to shut down Excel and open it up again. Unless the object number is stored somewhere within the workbook, that should clear it.

No guarantees but worth a try.

Regards

I actually meant close down Excel and restart it, not just close the spreadsheet and re-open it.

Regards

Is there a reason you can't just rename the object?
The short answer is that you cannot reset excel's internal counter without a restart, and apparently even that doesn't work for you anyway.

In VBA you need a consistent way to address objects. Excel lets you refer to them by their "excel determined" index number (Object(1), Sheet(1), etc...) or by their name.

If your environment has you adding and deleting objects regularly, I would never try to use Excel's index numbers to refer to them. I would take control of how the objects are added and named, then refer to them by name all the time.

If I needed to replace my Object named "Object1" in VBA, I would:

1) First delete the existing "Object1" object
2) Add a new replacement object
3) Rename it immediately to "Object1" while I still have control of the temp object

Now I have permanent control of that object.

Please reread post #7.

I think the consensus is that you cannot control Excel's internal numbering system/process ... so you need to work with it.

Perhaps you could share your code and someone may then be able to advise you how best to cope with the situation.

As JB has said, it is best to control the naming yourself so you know to what you are referring with no ambiguity.

Regards

I hope you're not misunderstanding my point in post #7. I'm saying to name your objects something unique, then control them via that unique name.

1) Delete the object named "MyObjectCat"
2) Add a new object
3) Rename the object to "MyObjectCat" while you still have temporary control of the object regardless of object index number

Continue to control the object by it's name.

The trick is to think of them by name. Well, and make sure you have control of the NEW object as you add it. Declare an object variable. Then set that object in the "add object" command so the new object is referenced in the known variable, even though the object itself might be object99 at this particular moment. By setting the variable reference at the same time you add the object, you already have control off it.... rename it something you can control permanently despite the index number of 99.

What is the code you use to delete the object currently?
What is the code you use to add an object to your sheet?

That is correct. You have answers to the questions I posed?

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.