Difficult Question for the experts - Control Indexes

Hi Steve,

You really can't change these indices. They are used internally by Excel to keep track of ALL objects (e.g., worksheets have index numbers). The indices always are numbered 1 through the number of objects in the collection. Thus, if you only have one worksheet in a workbook, its index can never be anything but one. You can cause Excel to re-assign and re-order indices by adding or deleting objects in a collection. For example, if you have 5 checkboxes, and you delete the second (Checkboxes(2)), Excel will re-number checkboxes 3 to 5 and make them 2 to 4. So you will immediately have a new index 2 checkbox.

Be aware that every object also has a Name property, and this does not change when the index numbers change. For example, when you delete the second worksheet in a workbook, now a worksheet named "Sheet3" becomes index number 2, because now Sheet3 is the second worksheet in the workbook. I find that it is always better to refer to objects by their Names rather than index numbers unless there is a good reason why you need to specifically refer to the Nth element in the object's collection.

You didn't mention why you want to change the index. There is no reason that I can think of that one would ever want to. If you could explain why you want to, perhaps there is another way of accomplishing what you want.

I hope this helps.

Damon

Re: Damon followup - Control Indexes

Damon,

Thanks for the word on idexes, you have confirmed what I feared. The reason I am attempting to change the indexes is that I am reading the data from say 5 columns in a worksheet x Nth row. the data in each column 1 - 5 is loaded into each control on a userform aligning up using the controls index (1-5). Each set of controls is in a different frame so that I can process the data from the worksheet frame by frame. If I put the controls into the frame in the order of the columns - no prob. It is just that this is not always the way thing turn out so I wished to control my own destiny. Perhaps the snip it of code below may shed some light onto the prob:

For iFrmCntr = 2 To 2
For Each Ctl In Me("Frame" & iFrmCntr).Controls
If (TypeName(Ctl) = "TextBox") Or (TypeName(Ctl) = "ComboBox") Or (TypeName(Ctl) = "CheckBox") Then
Col = Col + 1
Set CurrentCell = Cells(CurrentRecord + RowOffset, Col + ColumnOffset)
Ctl = CurrentCell
' Check for True/False cells (they would appear as 0 or -1) '
If Application.WorksheetFunction.IsLogical(CurrentCell) Then
Ctl = CurrentCell.Text
End If

' Is the cell displaying an error value '
If Err <> 0 Then
Ctl = CurrentCell.Text ' Display this if the cell has an error value '
Err = 0
End If
' SaveArray(Col) = Ctl '
If Cells(CurrentRecord + RowOffset, Col + ColumnOffset).HasFormula Then
Ctl.Enabled = False
Ctl.BackColor = RGB(192, 192, 192)
Else
Ctl.Enabled = True
Ctl.BackColor = RGB(255, 255, 255)
End If
End If
Next Ctl
Next iFrmCntr

Steve