+ Reply to Thread
Results 1 to 17 of 17

Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

  1. #1
    Registered User
    Join Date
    06-06-2011
    Location
    Utah
    MS-Off Ver
    Excel 2003
    Posts
    13

    Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    Hello All,

    I have been a member for awhile now and have been able to find most answers to my questions on this site and the internet. I am self taught in this VBA stuff and really a beginner. Anyway here is my dilemma and maybe someone can help me on this one. I have one userform with a 10 column listbox and textbox that i use to bring data from a work sheet. In the textbox a quantity value is entered. When the command button is clicked I need the data transferred to the second userform listbox with 11 columns. The second userform is running in the background. Every the command button is clicked the data is transferred to a new row. The below code i have only will transfer 10 columns and i cannot figure out why. Thank you in advance for your help on this one. This is my first post so if I am doing something wrong please let my know the correct way to post.

    Please Login or Register  to view this content.
    Last edited by sparkoft; 05-16-2013 at 02:59 PM.

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    Quote Originally Posted by Leith Ross View Post
    A VBA ListBox will not display more than 10 columns of data unless you link the ListBox to Range using the "RowSource" property.
    or building a 2D array and using this as source for the listbox.
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Registered User
    Join Date
    06-06-2011
    Location
    Utah
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    Will the RowSource property work with the addItem from the textbox also?

  4. #4
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    Nope, if the rowsource property is used you cannot add items with "ListBox.AddItem". I'd suggest to use an array
    -> each time you want to add a new entry:
    - Read the items which exists already in the listbox and save them in an array
    - enlarge the array by one "row" and write the new values into it, then set the array as rowsource property

  5. #5
    Registered User
    Join Date
    06-06-2011
    Location
    Utah
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    Does anyone know how to do the above with an array? I do not have any experience with arrays?

  6. #6
    Registered User
    Join Date
    06-06-2011
    Location
    Utah
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    Does anyone know of any examples that i can look at to replace the additem formula above. thank you in advance.

  7. #7
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    Hi,

    could you upload the workbook you are working on including the forms and some sample data?

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    You can't use an array with RowSource, but you can populate a listbox with an array using List.
    If posting code please use code tags, see here.

  9. #9
    Registered User
    Join Date
    06-06-2011
    Location
    Utah
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    My first attempt at using an array. i am still having the same result not populating the last column.

    What am i doing wrong?

    this cannot be that difficult. i am trying to populate a 11 column listbox on one userform from a textbox value & a 10 column listbox on another userform the textbox value being placed in the first column. i am very new to arrays and how they work so any examples would be much appreciated.

    Thanks,
    Please Login or Register  to view this content.
    Last edited by sparkoft; 05-16-2013 at 02:58 PM.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


    Once you've done that I suggest you upload a sample workbook.

  11. #11
    Registered User
    Join Date
    06-06-2011
    Location
    Utah
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    Thanks for the info on the code tags Norie!

  12. #12
    Registered User
    Join Date
    06-06-2011
    Location
    Utah
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    Attached is an excel file as an example of what i am trying to accomplish.

    1. Click button 1 to display userform1.
    2. Click launch userform2 button to launch userform2.
    userform2 listbox1 is typically populated from a nodupes additem from a database selection on userform1.
    3. a number quantity is entered in textbox1 and the add items userform1 button is selected. i would like the textbox quantity
    and the ten columns of the listbox to be transferred to userform1 listbox1. i also want to add multiple rows to the listbox
    each time the add items button is selected, which it does. as you can see the last column is not transferred.
    4. i would also like to be able to add all of the rows in userform1 listbox1 to the worksheets last unused row with out
    selecting the rows in the listbox.

    i hope i have explained things well enough.

    thank you for taking a look.

    Book1.xlsm

  13. #13
    Registered User
    Join Date
    06-06-2011
    Location
    Utah
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    Ok, so i think i got this figured out. the below array works fine for loading and transferring the data. One question i have is how to get the data to the first unpopulated row in the listbox? if row one in the receiving listbox has data, it is overwritten when the data is transferred. See below the new code.

    Please Login or Register  to view this content.
    Thanks,

  14. #14
    Registered User
    Join Date
    06-06-2011
    Location
    Utah
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    Well i guess what i am asking cannot be accomplished.

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Listboxes don't have unpopulated rows, or they shouldn't have.

    If you want to append new data take a look at using AddItem.

  16. #16
    Registered User
    Join Date
    06-06-2011
    Location
    Utah
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns

    The add item will not work for over 10 columns for what im told. i get people telling me to use add item then i get people telling me to use arrays and back to add item.

    The add item works great if you only want to transfer 10 columns of data or less.
    The array method works great if you want more than 10 columns but is limited transfering and cannot add a row on top of another row.

    that is why i said i am stuck and i am not getting any real help from this forum.
    Last edited by sparkoft; 05-18-2013 at 10:56 AM.

  17. #17
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    If you want to append to the existing data put that data in an array.

    Resize that array and add the new data.

    Then repopulate the listbox with the resized array.

+ 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