Userform's listbox values depending on values on certain matrix

[Juhanen II]

Hi all,

I have problems with my userform's listboxes. I have two listboxes, and I want second listbox's values to be dependent on first listbox's values.

And even more complicated, I need second listbox's values to be dependent on values on certain matrix.

In that matrix, row headings are listbox1's values and column headings are listbox2's values. How ever there are blanc cells on that matrix aswell. So if there is a blanc cell(s) on a row which (heading) is selected at listbox1, then I don't want that column (heading) which intersects with the blanc cell to be included to my listbox2 values.

Finally I want to insert the selected values from listboxes and the value from the intersection of those listbox values (headings) on that matrix to worksheet.

I included an attachment, where you can see my point better. However, as you can see, now the listbox values are not dependent on that matrix. Otherwise it is working like I want it to work.

-Juhanen-

[Juhanen II]

Hi,

I hope someone can help with my question, because I'm still stuck on it.

Thanks

-juhanen-

[Juhanen II]

Bump no response

[royUK]

What exactly do you want to display in ListBox2? Say Wed is chosen in ListBox1 do you want ListBox2 to contain 5,7,11 or w2,w3,w4?

[Juhanen II]

Hi Roy,

Sorry if I failed to be spesific...

For example if Wed is chosen in Listbox1 , then I want ListBox2 to contain w2,w3,w4

Thanks,
-Juhanen-

[royUK]

Remove the RowSource of ListBox2 in the Properties. Then use the Click event of ListBox1

Please Login or Register  to view this content.

[Juhanen II]

Thanks Roy. However, there's a mistake in the code:

For example if Wed is chosen in Listbox1 , instead of w2,w3,w4 ListBox2contains w5, w7, w11. (So instead of headings of the matrix, "w"+nonblanc values of the specified row are selected to Listbox2)

Secondly, I would be thankful if the code would use range names when referring to ranges, so that I could use the same code in different matrixes.

-Juhanen-

[Juhanen II]

bump no response

[royUK]

Thanks Roy. However, there's a mistake in the code:

For example if Wed is chosen in Listbox1 , instead of w2,w3,w4 ListBox2contains w5, w7, w11. (So instead of headings of the matrix, "w"+nonblanc values of the specified row are selected to Listbox2)

There's no mistake in the code, it does exactly what I thought that you wanted. However, I think you want the header row so I have changed the code

Please Login or Register  to view this content.

Secondly, I would be thankful if the code would use range names when referring to ranges, so that I could use the same code in different matrixes.

-Juhanen-

You will need to adapt the code to suit whatever ranges you want to use

[Juhanen II]

Thanks,

However, the new code doesn't work. (There is no error message, but second listbox is fully empty no matter which value you select form listbox1)

-Juhanen-

[royUK]

It works fine in the example workbook that you uploaded.


Try this slight amendment

Please Login or Register  to view this content.

[Juhanen II]

Thanks RoyUK

Now it works well. U saved my day.

-Juhanen-