VBA - Populate 3 cascading Listboxes with Data found on a Spreadsheet that cant be changed

[davidhasselhoff]

Hi at all!

I got a big table in the following style:

vegetable green zucchini
vegetable green broccoli
vegetable red tomato
fruit red strwaberry
fruit red raspberry
fruit yellow banana

I already managed to have 3 listboxes popping up subsequently but they allow the user to pick all of the choices, not only the combinations found on the spreadsheet.

Now would it be possible, to limit the choices in the "colour" listbox according to the choice the user made in the vegetable/fruit listbox, and to do the same with the 3th listbox?

Up to now, i have the 3 choices the user made be transferred to 3 different cells, which then are used to apply a sumproduct formula in order to find the value corresponding to the 3 of them in the table. I have a message popping up if the value is 0, saying there are no cooresponding values, but this solution doesn t seem very elegant to me...

I'd greatly appreciate your help,

Marc

 

[davidhasselhoff]

by the way, I'm using excel 2007, and my current formula is:

Public Sub UserForm_Initialize()
Dim AllCells As Range, Cell As Range, newcells As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

With ActiveWorkbook.Sheets("TransportCostcatalogue")
Set AllCells = .Range("n2", .Range("n2").End(xlDown))
End With


' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell
' Resume normal error handling
On Error GoTo 0
' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

For Each Item In NoDupes
UserFormagent.ListBox1.AddItem Item
Next Item
End Sub

Private Sub CommandButton1_Click()
UserFormagent.Hide
End Sub

The remove duplicate and sorting part comes from somewhere on the internet, there are 3 subsequent forms like this popping up