+ Reply to Thread
Results 1 to 8 of 8

VBA - Populate Listbox based on value selected in another ListBox (On Userform)

  1. #1
    Registered User
    Join Date
    12-12-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    VBA - Populate Listbox based on value selected in another ListBox (On Userform)

    Okay, I know this has been posted before, and answered elsewhere, however nothing I try seems to work for me. I can do this extraordinarily easily in Access, but am working in Excel so am in to different territory now.

    I have a userform with 2 list boxes - one contains a list of transaction IDs (stored as 'Check'), the second needs to give a list of items that are in that transaction when the 'check number' is clicked on the first listbox.

    I have a 'Data' spreadsheet that contains a continuous list of items, prices and the transaction ID associated with them - I therefore have 3 columns (Product, Price and ID) - the first listbox is generated from the 'DataCheck' sheet, selecting the check numbers (sheet contains a list of check numbers and totals)

    So - Listbox1 's OnChange event needs to update Listbox2 's data to show filtered data based on the selection made, and this needs to be 3 columns of data (Product, Price and Check).

    All code I have tried seems to pull over only one column (which is no use as I need at least 2), or starts pulling the wrong information, or sometimes doesn't even filter at all.

    I have tried using auto filter, and that is not really much use as I cannot filter the actual Data Sheet itself because of dynamic updating that can occur to it.....

    I have managed to code everything I need, but this has really stumped me!

    I am excel 2003 currently, and have tried most of the options found here - additem doesn't seem to work in the right way, neither does auto filter...

    Help please as i cannot find an answer to this anywhere.

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

    Re: VBA - Populate Listbox based on value selected in another ListBox (On Userform)

    What code have you tried?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    12-12-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: VBA - Populate Listbox based on value selected in another ListBox (On Userform)

    I have tried a variety of options and changes - probably all the code I could find on here; mainly looping through and adding items. I cannot paste any because I have tried, edited and removed so much code.

    the main thing here is that i need to ensure that I can find the matching transactions, then edit them; which presents a further issue in itself. It is not a necessity to have this, but would be nice feature.

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

    Re: VBA - Populate Listbox based on value selected in another ListBox (On Userform)

    This should be straightforward, and you can probably be done without a loop.

    One way you could do it is to use advanced filter instead of auto to copy the result of the filter to a separate worksheet.

    You can then populate the listbox from that worksheet using List.

    If you attach a sample workbook I'll show you what I mean.

  5. #5
    Registered User
    Join Date
    12-12-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: VBA - Populate Listbox based on value selected in another ListBox (On Userform)

    I have this as the latest attempt, however it actually does nothing....

    Please Login or Register  to view this content.
    the listbox that contains the check number is 'FindCheck' and the one that needs to populate with data is called 'CheckInfo'. All data is on the 'Data' sheet - headers in A1, B1, C1 and data in A2, B2, C2 that could go as far as C20000....

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

    Re: VBA - Populate Listbox based on value selected in another ListBox (On Userform)

    That code looks fine apart from the part where you only add items to one column.

    Try this, though I'm not sure how it will work if rList is non-contiguous.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-12-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: VBA - Populate Listbox based on value selected in another ListBox (On Userform)

    where in the code do you refer to?

  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: VBA - Populate Listbox based on value selected in another ListBox (On Userform)

    This is where you are adding to the listbox, and you are only adding to one column.
    Please Login or Register  to view this content.
    Have you tried what I suggested?

+ 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