Listbox 2 takes the value of Listbox 1

[Guest]

Hi, I have an excel sheet with two listboxes controlled by forms. The
listboxes are populated from different data and when clicked populate
information in Sheet1. Listbox1 has 20 rows of data, Listbox2 has 10 rows.
When the first listbox pops up I can select any of the entries. If I select
row 2 the data is populated in the correct place in sheet1. The second
listbox doesn't show up but the data from row 2 of the second list box is
automatically populated in the correct place in sheet1. If I run this again
and pick row 11 in listbox1 the second list box shows up and everything works
correctly. The second listbox appears to be picking up the value from the
first listbox and only shows up if that number doesn't correspond to
anything. Any ideas on how to fix that?

 

[Guest]

Hi,

Excel -- Data Validation -- Create Dependent Lists.

Use this URL: http://www.contextures.com/xlDataVal02.html

Challa Prabhu

 

[Guest]

Thanks for the thought, However I'm using listboxes from forms.
Dropdown lists are unfortunately not suitable for what I am doing.

 

[Guest]

I have found a workaround. By putting a message box at the end of listbox2
everything works correctly. The extra click seems to stop the next listbox
from running automatically. I still have no idea why this is happening so any
further ideas would be greatfully received. (At least I have a quick and
dirty workaround for now)

--Listbox1
Private Sub ListBox1_Click()
ActiveSheet.Range("B2") = ListBox1.List(ListBox1.Value, 0) & " " &
ListBox1.List(ListBox1.Value, 1)
MsgBox "test"
Unload Me
End Sub

--Listbox2
Private Sub ListBox2_Click()
ActiveSheet.Range("B3") = ListBox1.List(ListBox1.Value, 0) & " " &
ListBox1.List(ListBox1.Value, 1)
Unload Me
End Sub