+ Reply to Thread
Results 1 to 15 of 15

Populating a userform based on a listbox selection

  1. #1
    Registered User
    Join Date
    06-08-2010
    Location
    NY, NY
    MS-Off Ver
    Office 2003, Office 2007
    Posts
    18

    Populating a userform based on a listbox selection

    I have come across an issue while trying to put together a spreadsheet. I am trying to create a database that will have 14 colums and an unlimited amount of rows. With the help of the amazing people of this site i have been able to make great progress. But i am at a roadblock.

    I now know that a listbox can only populate 10 colums. After conducting a search the listbox populates all matching rows up to the tenth column. When i click on one of the matching rows only the first 10 boxes on the userform populate. i cannot for the life of me figure out ho to get the other 4 boxes to populate.

    is there a way that i can select the appropriate row and then use offset to populate the userform?

    Any help would be greatly appreciated.
    Attached Files Attached Files
    Last edited by hey1000; 07-20-2010 at 09:21 PM.

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Populating a userform based on a listbox selection

    A listbox can have more than 10 columns, you just can't use Additem to populate it if it does. You need to create an array of your data and assign it to the listbox's List property. You can also set its rowsource to a range on a sheet but IMO you are better off using an array.
    Last edited by romperstomper; 07-20-2010 at 03:23 AM.
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Populating a userform based on a listbox selection

    Every time I run a search there seems to be missing TextBoxes
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    06-08-2010
    Location
    NY, NY
    MS-Off Ver
    Office 2003, Office 2007
    Posts
    18

    Re: Populating a userform based on a listbox selection

    Sorry about that roy the only thing at the moment that can be searched is arrest #. I have not been able to get past that to start working on searching for any other criteria.

    I have read up on arrays and i am not sure how i would incorporate that to populate the listbox.

    I dont really need the listbox to populate with all 14 colums. as long as when i selected one of the returned rows the entire userform populated, right now just the first 10 columns populate.

    Thank you guys for your quick replies

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Populating a userform based on a listbox selection

    Use Excel's builtin facilities:
    And put this in the Initialize-event
    Please Login or Register  to view this content.
    And
    Please Login or Register  to view this content.
    Last edited by snb; 07-20-2010 at 03:49 AM.

  6. #6
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,327

    Re: Populating a userform based on a listbox selection

    Hi hey1000

    or maybe
    Please Login or Register  to view this content.
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  7. #7
    Registered User
    Join Date
    06-08-2010
    Location
    NY, NY
    MS-Off Ver
    Office 2003, Office 2007
    Posts
    18

    Re: Populating a userform based on a listbox selection

    Thank you all for such quick replies.
    I have spent a week trying to figure that out and with your help was able to get that issue corrected in a half hour.

    You guys are the best. This is an amazing site. I hope i can figure out the rest of this spreadsheet but i know that if i have an issue you guys can surely lead me in the right direction.

    Thank you again.

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Populating a userform based on a listbox selection

    I've changed Findall2,this needs adapting & should be made more dynamic so that you don't need so many procedures that are similar. You could maybe use the Tag property to store the relevant Column reference for each TextBox

    I have changed the number of Columns to 14 in the listBox
    Attached Files Attached Files

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Populating a userform based on a listbox selection

    snb, why CurrentRegion? It won't work with a filtered range as required.

  10. #10
    Registered User
    Join Date
    06-08-2010
    Location
    NY, NY
    MS-Off Ver
    Office 2003, Office 2007
    Posts
    18

    Re: Populating a userform based on a listbox selection

    I understand what you mean. I know when i look at the code it is very redundant. I wasnt sure how to write the code once and have it search for which ever text box was populated. this was the only way a newbie like myself could figure it out to get it to work.

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Populating a userform based on a listbox selection

    Delete the code in the Find All Button& paste this codein
    Please Login or Register  to view this content.
    The code will loop through each control& if it is a TextBox or ComboBox and it has a value in it then it will use that value as the criteria to filter for. The Field is decided on that controls Tag property which must be the corresponding column number. See the first TextBox & ComboBox,they are the only ones that I have set up.

    You can then delete all those FindAll procedures

    I suggest that you read up on the use of the key word Me,you don't use it correctly
    Last edited by royUK; 07-20-2010 at 05:09 AM.

  12. #12
    Registered User
    Join Date
    06-08-2010
    Location
    NY, NY
    MS-Off Ver
    Office 2003, Office 2007
    Posts
    18

    Re: Populating a userform based on a listbox selection

    Hi Roy thank you for your help. I have replaced the exsisting code witht the code you provided. I am running into a problem. When i run the code i get a runtime error 13 type mismatch i am not sure if i did something wrong or what. can you advise what my problem is?



    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    06-08-2010
    Location
    NY, NY
    MS-Off Ver
    Office 2003, Office 2007
    Posts
    18

    Re: Populating a userform based on a listbox selection

    the error appears at the

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    06-08-2010
    Location
    NY, NY
    MS-Off Ver
    Office 2003, Office 2007
    Posts
    18

    Re: Populating a userform based on a listbox selection

    ok so i added tags to all controls. now i am having an issue where the listbox is not being populated after finding matching entries. i am not sure why

  15. #15
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Populating a userform based on a listbox selection

    It worked fine for me in the example you attached
    Attached Files Attached Files

+ 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