Combobox and two listBoxes

[Yokosuka]

Hi vba Programmers,

I am tired of trying to do it but I am not able to get the results since I lack of knowledge. I want to populate a combo box and two listboxes with unique values+ visible cells only. That means when i apply a filter on the sheet I want the combobox and both listboxes to be updated with the filter applied.

I also try to link combobox1 with two listboxes and make them both multiselect ones, but they are not populated.

To sum up, when I choose a unique value in combobox1, I want to see all the values matching that item from combobox1 in listbox1 and listBox2.

This is my poor code: (please also take a look to the sample workbook)

Please Login or Register  to view this content.

Thank you in advance guys. Advise me please.

[Norie]

Which sheet should the listboxes be populated from and which column is the combobox being used to filter?

[Yokosuka]

I am using sheet1 as worksheet and for combobox it is populated from Column A, ListBox 2 from Column B and ListBox3 from column C. the filter could be applied to any of the three columns.

Thank you

[Norie]

Sorry I'm not really following, Sheet1 only has 1 column of data.

Sheet2 has 3 columns of data but I can't see how any of the values in column 1 Sheet1 could be used to filter the data on that sheet to allow populating the listboxes.

[jindon]

See if this is how you wanted
Added CommandButton x 1

Please Login or Register  to view this content.

[Yokosuka]

Arigatou gozaimasu Jindon It is working super fine One more thing I would like to do is, when you click on an item in a listBox 2, get only maching values for that selected item in ListBox 3. Is it possible?

Thank you again.

Yukiko.

[bakerman2]

Try this. Double click on item in Listbox2 to see result in ListBox3.

Please Login or Register  to view this content.

[jindon]

Arigatou gozaimasu Jindon It is working super fine One more thing I would like to do is, when you click on an item in a listBox 2, get only maching values for that selected item in ListBox 3. Is it possible?

Thank you again.

Yukiko.

Can not use Click event since ListBox2 is multiselect. So DoubleClick on ListBox2.
Change to

Please Login or Register  to view this content.

[Yokosuka]

Thank you so much guys you are awesome. Thank you so much Jindon. I am trying to understand your code therefore, I would like to add a listbox 4 changed by listbox3 with the same principle I added lisbox3 change and listbox3_DbClick blocks but it doens't work. I copy paste some values in column D to be assigned to listBox4.

when I double click on an item in listbox 3 to get its values in Listbox 4 I got a run time '381' error (Could not get the list property. Invalid property array index.)

So it means that did not understand the code well. is it related to . compareMode? should it be 2 to be able to add a second scripting dictionary? I am trying to understand. Thank you again for your efforts to help me.
Yuki

[jindon]

I don't understand what you are trying to do and what you did... So too hard to comment.

[Yokosuka]

This is what I have added to your code: (I already declared d and dif as objects)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

what I understood is .compareMode= number of column. I am just trying to understand, so if you can help me I will be thankful. Sorry if I disturbed you Jindon.

[jindon]

1) my original code has 3 lines that should be deleted. (still no harm without deletion)

Please Login or Register  to view this content.

2) There is no 3 for dictionary compareMode, only 1(textcompare) or 0(binarycomapre, defalt).

[Yokosuka]

Understood Jindon.Thank you so much. when I add this code it show error 424: Object required.

Please Login or Register  to view this content.

[jindon]

Too difficult for me to comment.

LB3_change

Please Login or Register  to view this content.

But LB_Dclick

Please Login or Register  to view this content.

Nee to see whole code, not just the part.

[Yokosuka]

this is the code:

Please Login or Register  to view this content.

I added this line to combobox1_change, but now I got value from column D in LB3.

Please Login or Register  to view this content.

[jindon]

1) Does it compile in the first place?

2) What are you trying to do with the red line?

Please Login or Register  to view this content.

3) What would be the reason for b declared as global variable?

Please Login or Register  to view this content.

[Yokosuka]

No answer Have a wonderful weekend.

[Yokosuka]

Sorry Jindon I have not seen your last message, it was in the second page. Please excuse my ignorance.

To be able to add a listbox 4 May be I need to do same approach but using the global variable b instead of a?

This variable b was actually initially in the code provided by you. I was asking myself why is b declared but it doesn't figure in the code.

The red line was actually a comment. I am having a bad time with this, it is very hard for me. If I can do it for listbox 3 and listbox 4 exactly as you did it for LB2 and LB3, i can add more columns and therefore more listboxes.

Thank you Jindon for the help and sorry again.

[jindon]

This variable b was actually initially in the code provided by you

1) I can not find variable "b" in any of my codes...

2) You said

I am tired of trying to do it but I am not able to get the results since I lack of knowledge. I want to populate a combo box and two listboxes with unique values+ visible cells only.

I thought you would filter the range first.

Are we talking about the same issue?

[Yokosuka]

Hello Jindon,

Sorry about that. the b was on the excel sheet. I think i am the one who added it. You copy paste the data on the same sheet from K1. I don't want to copy paste on the same sheet but on a different one. Since I failed to do so, I commented that line.

[Yokosuka]

I attached the excel file. It would much easy to show what I need to do. Sorry for not being clear from the beginning. I thought it would be easy to add the columns I want if I know how to do ot with two columns. But it is not that easy.

Than you so much.

[jindon]

Is this how you wanted?

Please Login or Register  to view this content.

[Yokosuka]

hello Jindon,

Thank you for your efforts and help. I was trying to solve a problem again. When I execute the code, it shows " Run time Error 10004" Unable to get the CurrentRegion property of the range class" .

I do not know what does it mean.

thanks Jindon.

[jindon]

If you are talking about files that have different sheet layouts, no idea.
Correction:

Please Login or Register  to view this content.

[Yokosuka]

I rated this thread as excellent and marked it as resolved. You are awesome guys especially Jindon. Thank you for the help.

Enjoy your weekend.

Yuki

[Yokosuka]

Hi Jindon,

I deeply apologize for disturbing, but i just figured out that for listbox4, the filtering is done based only on listbox 3. I want to filter values in listbox4 based on listbox 3 and listbox 2 selection. Please Jindon support me.

Yukiko

[jindon]

It should be filtered from Combo1, List2 and List4.

[Yokosuka]

This is my last requirement. You really helped me a lot. If you can help me for the last time with the following:

Actually your code is amazing and working fine. But when I double click on listbox3, I want to show results in listbox4 filtered according to what is selected in listbox 2 and listbox 3. This is my last quiry, I promesse.

I cannot thank you enough.

Yuki