| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Sequential Object names
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 -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Sequential Object names
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. J Streger wrote: 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 -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Sequential Object names
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. -- Regards, Tom Ogilvy "J Streger" wrote: 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 -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Changing Sequential Object names
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. -- Regards, Tom Ogilvy "Dave Peterson" wrote: 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. J Streger wrote: 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 -- ********************* J Streger MS Office Master 2000 ed. MS Project White Belt 2003 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatically change sequential worksheet names in formulas | Excel Worksheet Functions | |||
| Syntax in Varying Object Names | Excel Programming | |||
| Changing VB Component Names to match Worksheet names using VBE | Excel Programming | |||
| Non-sequential VLOOKUP function -OR- sequential sort of web query | Excel Worksheet Functions | |||
| Sequential names on Sequential pages | Excel Worksheet Functions | |||
Linear Mode
