Creating Dependent Searchable Lists (List Box Dependent on Combo Box)

[antman2988]

Hi All,

I am not entirely sure how I should go about accomplishing this because I believe there are a few ways to do this.

I have a list of data similar to below:

0582 051
0582 954
0582 725
1275 654
1275 777
5290 936
5290 003
5290 051
5290 923
5290 000

Basically, I want to create dependent list boxes, combo boxes, or drop down lists. I feel as though combo boxes would be the best because the first box is going to be populated with over a thousand codes (first column) so it would be best if the codes were searchable. Depending on the choice selected in the first box, only 20 or less options will appear as choices in the second box. However, the second box needs to allow for multiple selections. A list box would probably be the best option for this. How could I create a list box dependent on a combo based on the setup of the data above?

In addition, would the second box populate another cell with its data? For example, all of the codes that are selected in the second box should appear in the cell next to it.

If there is any more information I can provide, please let me know.

Any help is appreciated!

Thanks,
Anthony Smith

 

[MickG]

Try this for starters:-
https://app.box.com/s/g6mn11it5kc7vnkxc1z7ayzm65u6jc9v

 

[antman2988]

I really like the way this is set up. However, I am newer to VBA (been trying to learn it recently) so I an unaware of how to modify the code to include a different range for column data and where to place the output. How would I place the output data all in one cell separated by commas?

Thank you for the help.

 

[MickG]

Please Specify range address of your data and the cell address where you would like the results to go.
Please show example of expected results

 

[antman2988]

Please Specify range address of your data and the cell address where you would like the results to go.
Please show example of expected results

The data starts in cell A2 and ends at A8667 for the first column of data. The dependent data starts in cell B2 and ends at B8667. I would like the output data (column B) to go into another worksheet in the same workbook in cell D30. All of the codes will be in the same cell separated by a comma. The destination sheet name is Assign_Project_Program_Codes. The forms button will be placed in cell D19 of the same sheet, and the column A value will be placed next to the form button in D20.

Thanks.

 

[MickG]

Try this:-
https://app.box.com/s/6mdt8hp4y4u8em3wox452swp7xzi9iph

 

[antman2988]

I apologize for a late reply as I had to put this project on the back burner since more pressing matters came up. When I tried to implement the code in my workbook, I got a run-time error '381' in regards to the ComboBox. I adjusted the code in the original workbook that you sent to ensure my adjustments were working fine and they were. I can always just copy and paste my data over, but how do I fix this run-time error?

Thanks.

 

[antman2988]

Also, is there a way to make the drop-down list on the form searchable? There are many codes and it would be a lot easier if the user did not have to scroll through the entire list.

Thank you for any assistance you can provide.

 

[antman2988]

Update: I still get the same error when trying to post my data in the workbook you sent.

I was wondering if you could assist me with something else. How can I implement a second drop-down list into the form in which a user selects one code that the list box is dependent on? There will be three columns of data and the second drop-down list will be dependent on the first and the list box will be dependent on the second drop-down list.

I apologize for asking for so much help. Again, any assistance you can provide me with is better than nothing.

Thanks!

 

[MickG]

Can you send a example file (Box.com or Dropbox or similar) showing your data, with a covering note explaining what you want and showing how you expect the code to work.