Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel VBA 24-Hour Trainer 2nd edition
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 30th, 2015, 06:36 PM
Authorized User
Points: 44, Level: 1
Points: 44, Level: 1 Points: 44, Level: 1 Points: 44, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2015
Location: Aberdeen, UK
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.
Reply With Quote
  #2 (permalink)  
Old January 1st, 2016, 12:19 PM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 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.
Reply With Quote
  #3 (permalink)  
Old January 8th, 2016, 11:17 PM
Authorized User
Points: 168, Level: 3
Points: 168, Level: 3 Points: 168, Level: 3 Points: 168, Level: 3
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Oct 2015
Location: South Africa
Posts: 40
Thanks: 0
Thanked 4 Times in 4 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 8th, 2016 at 11:22 PM. Reason: spell error
Reply With Quote
  #4 (permalink)  
Old January 9th, 2016, 11:59 AM
Authorized User
Points: 44, Level: 1
Points: 44, Level: 1 Points: 44, Level: 1 Points: 44, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2015
Location: Aberdeen, UK
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.
Reply With Quote
  #5 (permalink)  
Old February 19th, 2016, 10:46 AM
Authorized User
Points: 44, Level: 1
Points: 44, Level: 1 Points: 44, Level: 1 Points: 44, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2015
Location: Aberdeen, UK
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!
Reply With Quote
  #6 (permalink)  
Old February 20th, 2016, 12:57 AM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 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.
Reply With Quote
  #7 (permalink)  
Old March 1st, 2016, 11:57 AM
Authorized User
Points: 44, Level: 1
Points: 44, Level: 1 Points: 44, Level: 1 Points: 44, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2015
Location: Aberdeen, UK
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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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



All times are GMT -4. The time now is 07:31 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.