Hi, I've worked though Excel VBA Trainer 1st edition and currently nearly finished working through 2nd edition. Thank you for both; really excellent. My query: Is it possible to provide a user with the opportunity to enter an array of numbers (e.g. 3 columns by 200 or more rows long) via a user form? If not, then presumably the best option is to not use a user form but instead allow the data entry straight into Worksheet cells? Thank you.

Hello Colin, thanks for buying the book.

The short answer to your question from a programming standpoint is yes, it is possible for a user to enter data in a multi-column UI on a UserForm. The longer answer, however, would involve an understanding of what the user's expectations are, and how large the input table can possibly be in terms of how many records (rows) of data there will be.

For example, suppose the user's expectation is to enter data into a UserForm and have that data (as is usually the case) be transferred or stored on a worksheet so the data may be preserved after the workbook closes. Typically, a text box is used for data input, and with at least 3 columns and 200 rows, that would be 600 text boxes on the UserForm, if the design for that project calls for such an interface. It's a good idea to limit the count of controls to less than 600; that already approaches too many controls for an efficient and friendly UserForm. Not saying you were planning to do that, just giving some context as a starting point for what some workbook designers might be considering. If it were a static 3 columns and 10 rows, I'd say no problem, 30 controls are not unreasonable. But 600 or more is a bit much.

Perhaps more reasonably would be just 3 text boxes, one per field, so at each completion of entry, the data can be transferred and stored on a worksheet in its next available row. Or, if the data entry is mandated to be on the worksheet and wished to be viewed or edited, a single ListBox is a good option to display the data on a UserForm in real time; again, depending on what the project calls for. This way, the best of both worlds can be achieved, where you can enter new data on the UserForm with just 3 (or however many fields there are) textboxes, and view or edit the entire table with a ListBox. That would substitute 600 controls with just 4.

Hope this helps to answer your question. If not, please post back with more info about your project, so myself or someone can help you with a more relevant answer you can use, depending on your workbook's design and your anticipated results.

I have to say the last is the only practical solution, and maybe have navigation buttons on the form where you press next-button and the current entry is added on the sheet and the form fields cleared for the next input.

I am just trying to imagine the effects of 600 fields, or even a 10th of that, in computer resources: memory, file size & CPU usage.

__________________
Nostalgia 4 Infinity

Tom and Zakalwe, Thank you for your responses and advice. I finished working through the 2nd edition and will be testing your ideas and a few of my own this coming week. I will post the outcome; and perhaps seek your further comments. Thank you again.

After considering various options, I decided to create a 3-ColumnCount ListBox with a maximum of 20 rows plus a header row. I'm currently working on this but as a VBA novice am making slow progress. I want to provide the user with the co9lumn headings and a list of integers from 1 to 20 in the first column. The user then well enter numbers into the two empty columns, up to a maximum of 20 in each of the columns. The data will then be passed to a worksheet, to be included in the calculation process. I realise that this should be dead-easy, but any help or advice would be much appreciated!

Colin - -
My email address is on the "About the Author" page near the front of the book. Send me your email address and I will send you a workbook example based on the scenario you described.

Tom, thanks for sending the Excel workbook example. As I mentioned, I'm using textboxes to allow users to enter numerical data but may use your scheme in another part of the software tool that I'm building. Cheers, Colin