Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel VBA 24-Hour Trainer 2nd edition
|
BOOK: Excel VBA 24-Hour Trainer 2nd edition
This is the forum to discuss the Wrox book Excel VBA 24-Hour Trainer 2nd Edition by Tom Urtis; ISBN: 978-1-118-99137-4
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel VBA 24-Hour Trainer 2nd edition section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 30th, 2015, 07:36 PM
Authorized User
 
Join Date: Dec 2015
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Entering columns of numerical data via userform

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.
 
Old January 1st, 2016, 01:19 PM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

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.
 
Old January 9th, 2016, 12:17 AM
Authorized User
 
Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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

Last edited by Zakalwe; January 9th, 2016 at 12:22 AM.. Reason: spell error
 
Old January 9th, 2016, 12:59 PM
Authorized User
 
Join Date: Dec 2015
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old February 19th, 2016, 11:46 AM
Authorized User
 
Join Date: Dec 2015
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Still struggling!

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!
 
Old February 20th, 2016, 01:57 AM
Wrox Author
 
Join Date: Jan 2015
Posts: 35
Thanks: 0
Thanked 6 Times in 6 Posts
Default

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.
 
Old March 1st, 2016, 12:57 PM
Authorized User
 
Join Date: Dec 2015
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Entering data into an SQL database jmsherry SQL Server 2000 3 July 24th, 2007 01:00 AM
VBA code for entering the same data for dates rohit_ghosh Access VBA 1 May 3rd, 2007 09:45 AM
Entering data to dynamic table cells weebadbilly XSLT 0 June 30th, 2004 07:15 AM
Protect input-field from entering data rvw Javascript How-To 4 October 22nd, 2003 05:40 AM
entering data Kelly Johnson Classic ASP Databases 1 August 7th, 2003 07:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.