+ Reply to Thread
Results 1 to 18 of 18

Looping a data input form based on form option

  1. #1
    Registered User
    Join Date
    04-08-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Looping a data input form based on form option

    Good Evening (or morning, depending on your geographic location).

    I have only recently started with VBA, and have managed to piece together some code that will allow me to enter some data into a worksheet. Pretty standard stuff. I include the code below for the "submit" button.

    Please Login or Register  to view this content.
    I am looking to add another input to my form with "quantity", where the user can optionally insert a number. The same information will then be entered except for the "ID.Value" which will increase by one for each repetition. Is anyone able to advise a way of doing this, or telling me if I have made a bodge-job of the code above?

    Regards
    Ralph
    Last edited by bologne; 04-17-2011 at 08:50 AM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,044

    Re: Looping a data input form based on form option

    Hi bologne,

    You could let your ID.Value = Max("A:A") +1 on the sheet itself. This way your ID would always be one greater than the biggest current number.

    The next time you add a record it will be one bigger.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Looping a data input form based on form option

    I'm assuming you mean each "click" you want to increment the id... please not that you will need to reset the clickCount whenever you load a new record.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-08-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Looping a data input form based on form option

    Quote Originally Posted by nimrod View Post
    I'm assuming you mean each "click" you want to increment the id... please not that you will need to reset the clickCount whenever you load a new record.

    Please Login or Register  to view this content.
    Thank you both for your responses

    Not so much each "click", but rather adding an input field that would have a default quantity of 1 for submit, but could be changed to any other number, e.g. 4.
    Upon submitting, it would simply do 4 entries with an incrementing stock number.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,044

    Re: Looping a data input form based on form option

    Hi bologne,

    To give you a better answer we need a sample workbook with the userform included. You should also show where you want the quantity field on the sheet.

    To attach a sample click on the "Go Advanced" below the message area and then on the Paper Clip Icon above the message area. This will allow you to attach a sample file.

  6. #6
    Registered User
    Join Date
    04-08-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Looping a data input form based on form option

    Please find attached the relevant worksheet.

    The userform I am referring to is the "purchase" form.

    The "quantity" box the thread is about is at the bottom of the form, and is currently non-functional.

    Best Regards
    Ralph
    Attached Files Attached Files

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,044

    Re: Looping a data input form based on form option

    Hi Ralph,

    I don't understand why you can't simply type a number into the Quantity Box on the Userform.

    It looks like you buy and sell individual (unique) products and you are trying to create a automated way to keep track of inventory. The whole problem is to keep ACCURATE records with a MINIMUM of work.

    This means you should never type something that is already typed. You should simply be able to point and click with a mouse, if the product or person is already in your database/spreadsheet.

    You have already decided on Excel (good choice) but your middle two tabs are confusing me. I believe you need 2 separate tables of your data.

    1. A tab where you track all products you have for sale. The fields will be only about the items. Date of Purchase, Date of Sale, etc.
    2. A second tab is where you track Customers. This would be both people you buy from and sell products to. This would include Names, addresses and such.

    Now on a third tab you can have the connection between these two tables. It would have Date, Product ID, Customer ID at a minimum. This table/tab is for Transactions.

    The reason you create a UserForm is so you can have a ComboDropdown box where you point and click on a Customer Name in one Field and a Product they are selling or Buying in another of the Userform fields. Then using this Userform it changes the data in the Product table of the item to Sold, with the sell date, and Price of sale.

    Now the Invoice Printer needs a dropdown (or two). It should find the buyer/seller information from the Customer table and fill it in automatically. It should then allow you to point and click to what product they are dealing with and fill in that info automatically.

    On the Invoice form I envision a single Validation Dropdown box for Customer ID and it would fill in all the address and phone information using a VLookup formula.
    On the Invoice form also I see a Validation Dropdown for each item number and ti will fill in the Description and price using VLookup into the Product table.

    I believe that creating the structure of you data comes before creating the VBA userform. I believe you need to study and learn.
    1. Userform ComboBoxes see http://www.contextures.com/xlUserForm02.html
    2. Look at Office Templates to see how others have solved this problem http://office.microsoft.com/en-us/te...010117255.aspx

    I hope this will lead you in a better direction for getting a handle on invoices for your business.

  8. #8
    Registered User
    Join Date
    04-08-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Looping a data input form based on form option

    Quote Originally Posted by MarvinP View Post
    Hi Ralph,

    I don't understand why you can't simply type a number into the Quantity Box on the Userform.
    Thank you Marvin

    I will now look into everything else that you stated in your post, it seems I need to do a bit more research on the structure.

    In regards to the part I quoted above, this was because I had not yet implemented a code for that Quantity Box, and was wondering what code would best serve me in this manner.

    Best Regards
    Ralph

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,044

    Re: Looping a data input form based on form option

    Hi Ralph,

    It looked like you are going well with VBA but you were doing it without a good plan in place. Also, after looking at a website, I believe you can download all the inventory and not need to type anything for existing stuff. I don't know if you have a customer list that could be imported but that would be the next step. Then producing invoices with only a few clicks would be my goal.

  10. #10
    Registered User
    Join Date
    04-08-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Looping a data input form based on form option

    As per your posts above Marvin, I have rebuilt the stockbook from the ground upwards.

    Please find attached a new version of this. I have only done the "purchase" userform, which allows input of stock and clients.

    I am currently stumped with 2 things:
    1. The Stock number needs to be the last entered stock number +1, but must be automatically calculated, and not rely on user input. The same also needs to apply to Client ID, but presumably the same code can be adapted for each
    2. I need the quantity box to loop the input for the amount of times entered in the quantity (minus 1, for the initial input). The same data needs to be input, but increasing the stock number for each loop.

    Any help or advice would be much appreciated.
    Attached Files Attached Files

  11. #11
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,044

    Re: Looping a data input form based on form option

    Hi,

    I added a variable to CommandButton2_Click() code

    Please Login or Register  to view this content.
    and then fixed these lines
    Please Login or Register  to view this content.
    This should give you the idea of how to increment the Client ID or Stock number.

  12. #12
    Registered User
    Join Date
    04-08-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Looping a data input form based on form option

    Thank you once again Marvin it seems pretty self explanatory once I've seen it, it's just getting there in the first place.

  13. #13
    Registered User
    Join Date
    04-08-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Looping a data input form based on form option

    After having got my "Purchase" Userform working exactly as envisaged, I now only have the one remaining question on this subject.

    I still need to loop the entry with the quantity field on my userform, this was explained in more detail a couple of posts up.

    I have attached the most recent version of my document.

    Any help would be greatly appreciated.

    Best Regards
    Ralph
    Attached Files Attached Files

  14. #14
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,044

    Re: Looping a data input form based on form option

    Hi,

    Don't you want the quantity as a number to simply put on you spreadsheet? If a customer buy 3 of them you would put in a 3 in the quanitity field. If you loop the quantity it will show three lines of 1. I don't think that is what your want. Also, you need to be more specific on which code you want "Looped".

    When you ask questions, assume we know nothing and be more specific about the problem and what you expect as an answer. "Loop the Quantity" is a little to vague to act upon.

  15. #15
    Registered User
    Join Date
    04-08-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Looping a data input form based on form option

    Hi Marvin

    I did try to explain a few posts back, but I apologise for not being clear enough.

    I do actually require the "3 lots of 1" as you explain it. Due to the nature of our business we need to add purchaser information to every stock item that is sold, so if we were to purchase 300 of one item, we would actually need to enter 300 items of stock individually on to our stock books, as per our accountants request. This is one of the main reasons for me changing our currently hand-typed excel spreadsheets into VBA powered userforms.

    Rather than the entry looking like this (for 3 items added)
    Stock no: 1 | Purchase price: 300 | Quantity: 3

    I would require this instead:
    Stock No: 1 | Purchase Price: 300
    Stock No: 2 | Purchase Price: 300
    Stock No: 3 | Purchase Price: 300

    Hopefully this makes it a little clearer.

    So, I'm assuming the part of the code I need to loop is the "copy data to spreadsheet" part of my code. The only part that changes within the loop is incrementing the stock number for each item.

    Thank you for your time and patience Marvin

  16. #16
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,044

    Re: Looping a data input form based on form option

    Hi,

    See if this loops based on Quantity sold.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    04-08-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Looping a data input form based on form option

    Hey Marvin,

    That certainly does seem to loop the input, starting from 1. I'm sure I'll be able to get it to increment from the previous stock number on the stock sheet, but will say if I have any issues.

    Once again, you're a godsend. Thank you.

  18. #18
    Registered User
    Join Date
    04-08-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Looping a data input form based on form option

    Apologies for the double-post again.

    Your code works perfectly, and I have implemented it as I had intended, thank you again!

    There is just one small bug in the code in that it adds a blank row between each data entry.

    Once again I have attached the document in it's most recent form.

    I also copy the relevant code here:

    Please Login or Register  to view this content.
    Thank you

    Edit: I fixed this by changing the offset value of lRow, which I should have considered before.
    Attached Files Attached Files
    Last edited by bologne; 04-17-2011 at 08:49 AM. Reason: Fixed

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1