Possible to change the index numbers within a collection?

I have created a userform that upon clicking a commandbutton adds a line of 4 textboxes. Everytime the user clicks the commandbutton a new line of textboxes is created. The 4 textboxes in each line are described by a class (hope I am using the terminology correctly). And each instance is saved in a collection. The problem is that I am giving the user the ability to insert a new row in between two existing rows. When this happens, I need all of the index numbers to re-order so that they are consecutive from the top of the form to the bottom. Example: The user enters 6 rows of data and then realizes that they missed an item that needs to be inserted between rows 3 and 4. Currently the rows are indexed 1 to 6 with 1 being the row at the very top of the form and 6 being the row at the very bottom of the form. If I insert a row in between 3 and 4 I will end up with the index numbers going from top of the form to bottom (1,2,3,7,4,5,6). Is it possible to reorder the index numbers so that the inserted rows index number will be 4 and each row after that will go up by one?

Option Explicit
Public Cashflows As Collection
Public Sub test()
Set Cashflows = New Collection
UserForm1.Show

End Sub

Class Module CashFlowControl

Option Explicit

Public CashFlowDate As MSForms.TextBox
Public Cashflow As MSForms.TextBox
Public EMV As MSForms.TextBox
Public CashFlowType As MSForms.ComboBox

Userform1 Module

Option Explicit

Private Sub CommandButton1_Click()

Dim CF As CashFlowControl



Set CF = New CashFlowControl
Set CF.CashFlowDate = Me.Controls.Add("Forms.Textbox.1", "CashFlowDate" & Cashflows.Count, True)
With CF.CashFlowDate
    .Left = Label1.Left
    .Width = Label1.Width
    .Top = Label1.Top + Label1.Height + (Label1.Height * Cashflows.Count) + 5
    .Height = Label1.Height
    .Visible = True
End With

Set CF.Cashflow = Me.Controls.Add("Forms.Textbox.1", "Cashflow" & Cashflows.Count, True)
With CF.Cashflow
    .Left = Label2.Left
    .Width = Label2.Width
    .Top = Label2.Top + Label2.Height + (Label2.Height * Cashflows.Count) + 5
    .Visible = True
End With

Set CF.EMV = Me.Controls.Add("Forms.Textbox.1", "EMV" & Cashflows.Count, True)
With CF.EMV
    .Left = Label3.Left
    .Width = Label3.Width
    .Top = Label3.Top + Label3.Height + (Label3.Height * Cashflows.Count) + 5
    .Visible = True
End With

Set CF.CashFlowType = Me.Controls.Add("Forms.Combobox.1", "Cashflowtype" & Cashflows.Count, True)
With CF.CashFlowType
    .Left = Label4.Left
    .Width = Label4.Width
    .Top = Label4.Top + Label4.Height + (Label4.Height * Cashflows.Count) + 5
    .Visible = True
    .AddItem "Contribution"
    .AddItem "Withdrawal"
    .AddItem "Fee"
    .Style = fmStyleDropDownList
End With

With CommandButton1
    .Top = Label1.Top + Label1.Height + (Label1.Height * (Cashflows.Count + 1)) + 5
    .Left = Label1.Left
End With


Cashflows.Add CF

If CommandButton1.Top + CommandButton1.Height + 25 > UserForm1.Height Then
    UserForm1.Height = CommandButton1.Top + CommandButton1.Height + 25
End If



End Sub

Originally Posted by romperstomper

I take it a ListBox wouldn't work for what you are doing? It would be a lot easier to work with I suspect based on what you describe.

I've never used a listbox before so I am not sure if it would be better or not. I will do some quick research on it to make that determination. Thanks for the suggestion!

I'm going to experiment with the before and after parameter. Thanks for the suggestions.

Is there a quick way to return the index value that a specific command button is associated with within a collection? In other words, each of my lines has a commandbutton next to it that when clicked will add a new line above it. In order to place everything properly I need to know the index value of the commandbutton that was clicked.

Originally Posted by Andy Pope

You could add the button to your class.

You could create a separate class and collection for the command buttons.

It's hard to tell without knowing more about your project but I would explore RS's suggestion of a multi column listbox.
You can display the selected listitem in a static set of textboxes. With buttons to add/update/insert/delete records.
This way the listbox handles the dynamic nature of your data rather than creating/deleting controls.

Thanks Andy. I think I have figured it out and it is working pretty well. When I create the controls I name them by "ControlName" & number. When the user clicks on one of the lines "add" button I use the "Right" function to return the number of the control. I pass that number to a subroutine that moves all of the line numbers below that control down one on the userform and then inserts a new line in between them. It also renames all of the controls below that line number with a new name that associates the number with their relative place on the form. I also have a "delete" button that does the same thing only in reverse. I have the partially finished form attached.

I gotta say. I have learned so much about this stuff in the past few months and I am really enjoying it. Thanks so much for all of the assistance.