Number automatically shapes in categories

[Yacine]

I do myself a lot of drawings where I need to number shapes in categories.
By categories, I mean that if you got Shapes that you would categorize into let's say "actors" and "sensors" and within the "actors", you have a sub-category of "motors" and "valves", then you may want to number your shape in this hierarchy. eg: A-M-1, A-M-2, A-V-1, etc.
If you get some hundreds of these shapes, then finding the last number or a gap in the numbering can get quite cumbersom.

Here's some VBA-code I use to make this numbering.

The trick is to use a recordset, that is not bound to any database. It's just there for holding the data and for fast sorting.

I use an ADO.recordset. Read the data of all relevant shapes on the page, sort it by the relevant categories (eg Actor/Sensor and Motor/Valve).
Then I icrement a counter each time one of the categories changes and assign this counter to a temporay variable ("newNr" in my code). I was not capable of incrementing directly the "number" field, it made the recordset cursor jump to arbitrary positions.

The last loop would then set the "number" field to the value of the temporary "newNumber".

Voila. I would have liked to set up a solution that is universally usable, with a dialog that lets you chose the fields that define the hierarchy, but I am not "that" bored. 

Start the macro "renumberShapes" to see the routine in action.

The solution is quite flexible, as it allows to allocate the shapes to new categories and get the shapes resorted.
Even changing the shape order is possible, if you manually change the "number" value - even to decimal values - and run the macro again.

Hope this is of use for someone.

Cheers,
Yacine