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?

Please Login or Register  to view this content.

Class Module CashFlowControl

Please Login or Register  to view this content.

Userform1 Module

Please Login or Register  to view this content.

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

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!

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.