Assigning ID's to Shapes in VBA

[gemcgraw]

Hello -
I'm working on a new project where a client is wanting a spreadsheet to track where products are staged in their warehouse. They keep pallets staged as "inventory markers" for a different products. Their concept is to enter a pallet number ("Pallet 001") in their Excel database (Sheet1) and then it updates the pallet (Sheet2) with a product number.


My suggestion would be to have "shapes" on Sheet2, representing a pallet. When they enter the pallet number (shape) ID on Sheet1, it would append the text in the shape (sheet2) and include the product number on Sheet2. (e.g. Shape1 has a default text of "Pallet 001". When a product is assigned to Shape1 "Pallet 001" on sheet1, the text is appended, "Pallet 001 - Product XYZ" on Sheet2.


My problem is trying to figure out how to tie an "ID" to shapes. I know how to have VBA create a given number of shapes, but how do I assign them unique IDs (like a listbox) so that they can be referenced again, to update the text of any given shape?

 

[JoeMo]

Maybe you can adapt something like this.

Sub AddShapesWithNames()
Dim i As Long
Application.ScreenUpdating = False
For i = 1 To 10
    ActiveSheet.Shapes.AddShape(msoShapeRectangle, 5 * i, 10 * i, 25, 25).Select 'change size & positions to suit
    Selection.Name = "Pallet " & Choose(Len(CStr(i)), "00", "0", "") & i
Next i
Application.ScreenUpdating = True
End Sub