Input programming decision time ??

[Guest]

Hi;

I have just completed an UserForm with 45 input fields which get
manipulated, concatenated and optioned down to 25 items of data. The 25
items will be inserted into 25 different worksheet database columns. The
worksheet columns are not consequitive and are random with regards to the
Userform input. I can think of two strategies for getting the data from the
UserForm to the Worksheet.

1) On entering the data, after manipulation, the item is stored in a 2D
ReDim-able array, Array(25, 0). As more members are added the Array is
ReDim-ed for each new member. On clicking Save, the required number of rows
is added to the database and the program loops through the array inserting
the data items into the appropriate column. This has an extra step by using
the intermediary array but has the advantage of not inserting rows until the
end of the process and makes undoing easier. On the other hand,...

2) Simply inserting a new row as each member entry is initiated and drectly
transfering the data from the UserForm input field to the appropriate
worksheet column as it is entered seems a bit simpler - but not a lot.

Becasue of the scattering of the worksheet columns in relation to the input
order, the only solution I can think of is using a Select:Case for 25
different Array indicies. This seems excessive to me -- but I am not
experienced enough to know for sure.

What is my best strategy for transfering my data from the Userform to the
worksheet? And, is there a better way to match Array index to worksheet
column than using Select?

Regards Bill

 

[Zone]

Bill, if you name your worksheet columns and give your userform
controls the same name, it should simplify things. For instance, the
contents of txtCity would go into column City. Or you could just name
the first cell in each column and then use an offset to enter each new
row, again referring to the column that contains each name. Something
to mull over. James