Changing Sequential Object names

[J Streger]

I have a sheet where each row the user types into creates 3 objects. They are
indicators that change as the data in cells change through the
worksheet_change function. I refer to those objects in the change function by
their name, for example "TPM_Ind_Bar_Row_14" I have protected the sheet and
need to add two buttons to insert and delete rows. I need to run through all
the objects on the sheet and either increase or decrease and delete certain
objects. The problem I run into is the For Each functions runs through the
index in order and in the instance where I try to change an object higher, it
errors as I have yet to change the next higher object. I was thinking of a
For iCount = me.shapes.count to 1, but then what happens if an object is
created in the middle rows, it will be out of sequence and cause the same
error. Is there a way, short of running through the three indicator names
from row.count to 1 to accomplish this? Below is a snipet of the for each
code I was using.

For Each oShape In Me.Shapes
If InStr(1, oShape.Name, "Row_") > 0 Then
If CLng(Mid(oShape.Name, InStr(1, oShape.Name, "Row_") + 4))

= arRows(iCount) Then

'increase row number on shape name
oShape.Name = Mid(oShape.Name, 1, InStr(1, oShape.Name,
"Row_") + 3) & _
RoundUpToInteger(oShape.Top /
Me.Range("TPM_Sense").Height)
'CLng(Mid(oShape.Name, InStr(1, oShape.Name, "Row_")
+ 4)) + 1
ElseIf CLng(Mid(oShape.Name, InStr(1, oShape.Name, "Row_") +
4)) = arRows(iCount) Then
'part of the row being inserted, no action needed.
shapes will move auto

End If
End If
Next

 

[Dave Peterson]

Maybe you can loop through the shapes twice.

Once you can rename them to something unique:

TPM_Ind_Bar_Row_14_Temp

Then the second time through, you can name them to what you want.

 

[Tom Ogilvy]

In general:
If you are adding items below the highest, you need to loop from highest to
the item before the added items, incrementing names as you go.
If you are deleting items below the highest, you need to loop from the item
after the deleted item to the highest, changing names as you go.

 

[Tom Ogilvy]

Seems like it would be easier to reason out a solution that avoids that, but
the OP seems to like it, so whose to say.