+ Reply to Thread
Results 1 to 21 of 21

3 Dynamic ComboBoxes to filter data and populate a ListBox

  1. #1
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    3 Dynamic ComboBoxes to filter data and populate a ListBox

    Good Day,

    I hope this mail finds you all well.

    I have attached a sample sheet of what I am trying to accomplish.

    I managed adapt some code to populate my 3 ComboBoxes. However I am struggling to get the duplication removed from ComboBox 1 and 2 so that it is the same with ComboBox 3. After selecting the options, I would like to create a list in ListBox1 to show the project information of column A to L.

    I would also like to know, when lets say, selecting an option in ComboBox 1 and 3 (skipping ComboBox2) would it also be possible to populate the ListBox?

    I thank you in advance!

    Kind Regards,
    Raymond
    Attached Files Attached Files

  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: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Raymond

    Try this for populating the second combobox with unique values.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Hi Norie,

    Thank you so much for the assistance, it works for all my 3 ComboBoxes.

    Any idea how to add the information according to the selection of the Comboboxes within the ListBox as per my post description?

  4. #4
    Valued Forum Contributor dotchiejack's Avatar
    Join Date
    05-21-2015
    Location
    Antwerp,Belgium
    MS-Off Ver
    2016
    Posts
    507

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Something like this perhaps?
    I remove items instead of adding.
    See attached.
    Attached Files Attached Files
    Click the * Add Reputation below to say thanks.

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

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    What should the 3rd combobox be populated with?

  6. #6
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Morning dotchiejack,

    Wow impressive, thanks for the enormous effort in designing something for me. Much appreciated.

    What would the code be so that you do not need to press the reset button once you decide to change the combobox options? Let say you press Actve project, but then would like to change it immediately to Future, but without staring over by using the reset button?

    @Norie, I figured out to populate ComboBox with the current gate data, just needed a way of adding info to the ListBox as per the response above. Thank you as well.

    Thank you!

  7. #7
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Hi dotchiejack,

    I have seen that you used the name manager for creating or referencing the table for all the data.

    How would I adapted the reference of code to always include the last row for the reference. Why I need this, is that the list will grow with rows of project being added hence the reference should change.

    Thanks!
    Last edited by Raylou; 10-02-2018 at 04:32 AM. Reason: update

  8. #8
    Valued Forum Contributor dotchiejack's Avatar
    Join Date
    05-21-2015
    Location
    Antwerp,Belgium
    MS-Off Ver
    2016
    Posts
    507

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    proj_tbl is the name of the table
    In my example go to formulas defined names. there you will find the table name.

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

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Try this code for populating the listbox from columns A-L based on the selections in the 3 comboboxes.
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Hi there,

    I have added the table name etc to my worksheet with all my data.

    For some reason in your code, when I activate the following lines to populate the comboboxes it give me a run-time error 42.

    I have been looking and trying things but nothing seems to work.

    Am I missing something with the setup for the proj_tbl or the AFP_1st etc.?

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Hi Norie,

    That code is doing the trick, thanks a lot.

    Just one follow up question on your code. I see when I open the userform and then do my selections, when I change my option in ComboBox3 the list updates, but if I do the same with the other two it gives me an error.

    As per the attachment (with you code), is it possible to adapt ComboBox 1 & 2 so it reacts the same as with ComboBox 3. Not sure if it is possible.

    thanks you!
    Attached Files Attached Files

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

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    What exactly do you want to happen?

    The error is easy to fix, all we need to add is a check that each combobox has a selection - see below.
    Please Login or Register  to view this content.
    Last edited by Norie; 10-02-2018 at 06:23 AM.

  13. #13
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    I would like to update the list every time one of the options in the Comboxes are changed without having to reset all the Comboxes each time. When you launch the Userform and you select options in Combobox 1, 2, 3 it give you a list of projects, which is great. But when I change ComboBox 3 it automatically updates the list, however when you change either Combobox 1 or 2 it does not and gives an error.

    Hope it makes sense. You will see what I mean in the example sheet.

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

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    What if you change combobox1/combobox2 and for what you've selected there are no matches to what's selected in combobox3?

  15. #15
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    I was thinking more in the sense that when you change any of 1 or 2 then 3 should be cleared and repopulated.

    Otherwise, plan B, I created the reset button to clear all the comboboxes and the listbox1, but there comes an error each time on this line of code:

    Please Login or Register  to view this content.
    I did not want to run or initialize the whole form as a reset.

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

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    When you clear the comboboxes then you'll trigger their change events, and since there's nothing selected in them you'll have problems.

    If you add the code I suggested in post #12 at the top of the Change event for ComboBox3 that should fix the problem, I hope.

  17. #17
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Morning Norie,

    Thank you for all your help. It is so much appreciated, you can not imagine.

    I added the code to my project and it does the trick.

    Whilst testing I encountered one problem with the code where it seem that once you select something in ComboBox 2 it does not update or limited the options to the selection in ComboBox 2.

    I have added the example file for you to see to the post and an image of the selection. As per the example there are no projects for the Primary Energy option which is in the ERA gate. So once I select ERA it give me an error due to the above mention problem of not updating the ComboBox 3 list accordingly.

    Once I can sort this out, I can close the thread as SOLVED .
    Attached Images Attached Images
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Good Day,

    I was just following up if might know the reason as per my previous post #17 why does the comboboxes not updatte as per the worksheet and the example image I send.

    Thank you!

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

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Not had a chance to look at that yet and don't have time right now.

    Off the top of my head it should be a simple fix, check if any matches were found, inform the user and exit the sub.

    When I get a chance I'll post back.

  20. #20
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Thanks appreciated very much.

  21. #21
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: 3 Dynamic ComboBoxes to filter data and populate a ListBox

    Hi Norie,

    It seems that I can not seem to figure it out what I am doing wrong. Long week, think a Guinness beer is in order.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Listbox to populate from a dynamic range of another workbook
    By Aomar M in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-02-2017, 01:47 PM
  2. Replies: 7
    Last Post: 01-29-2017, 03:22 PM
  3. Populate (ca 200) Txtboxes depending on listbox selection, live-filter for listbox &1 more
    By InternInNeed in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-14-2016, 09:56 AM
  4. [SOLVED] Filter worksheet data using 4 dependant comboboxes and populate in 'ListBox' on a userform
    By p_nayak268 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-27-2014, 12:41 PM
  5. VBA userform listbox populate data entered from comboboxes
    By Hilton1982 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-05-2012, 11:29 PM
  6. Populate Dynamic ComboBoxes
    By gmcconnell in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-11-2009, 05:01 AM
  7. [SOLVED] Initialize Userform, Populate Listbox, Dynamic RowSource?
    By RShow in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2005, 03:05 PM

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