Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input programming decision time ??
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input programming decision time ??
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 Bill Case wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decision Matrix with multiple criteria and decision | Charts and Charting in Excel | |||
programming input box | Excel Programming | |||
can you input time (hh:mm:ss) without having to input the colon i. | Excel Discussion (Misc queries) | |||
Programming using military time | Excel Programming | |||
Date & Time programming | Excel Programming |