Sleeper: UserForm ComboBox using data

[lovett10]

Hi i really need to know if its possible to have a userform with combo boxes that are dependant to each other. For example in the attached file there is 3 lots of glands i would like a drpo down menu for colour (Black, Grey, White) and then linked to that the sizes e.g. 20mm, 25mm so that if you selected White and then 20mm you would get a third comboBox saying the item description and then you select that.

I know that this is possible to do so using data validation but i would like to use it as a form if possible.

Another thing i would like is for the chosen item to be displayed on the spreadsheet

any help would be amazing

Attachment Testttt.xlsx

[Aflatoon]

Are your items always in that precise format? In other words, is the size always the first 'word', the colour always the last 'word'? Or do you perhaps have a set list of sizes and colours that could be used for filtering?

[lovett10]

Are your items always in that precise format? In other words, is the size always the first 'word', the colour always the last 'word'? Or do you perhaps have a set list of sizes and colours that could be used for filtering?

no the items arent always of a precise format so the labels will have to change when the first combo box is selected e.g. the first will be glands

sorry i didnt mention above and thanks for commenting

[Aflatoon]

Then how do the other comboboxes get populated?

[lovett10]

Then how do the other comboboxes get populated?

thats my question i dont know how to populate the comboboxes using the table and make them dependant on each other.

[Aflatoon]

I think you are perhaps missing my point - how should the code know which words it is to put in which comboboxes if the format is not always the same? If you cannot determine a rule for that, I for one certainly cannot code it.

[lovett10]

I think you are perhaps missing my point - how should the code know which words it is to put in which comboboxes if the format is not always the same? If you cannot determine a rule for that, I for one certainly cannot code it.

OK how do u do it if the format does stay the same?

thanks for the help btw <3

[mikerickson]

It could be done. But with that lay-out (specificaly the blank lines between colors), the easiest would be to do it explicitly. Also, it appears that the size are the same, no matter what the color is. And that the third combobox (description) would have at most two entries (if the size chosen was 20mm)

Is your data more complex than that?
Will your data be changing over time?

[lovett10]

It could be done. But with that lay-out (specificaly the blank lines between colors), the easiest would be to do it explicitly. Also, it appears that the size are the same, no matter what the color is. And that the third combobox (description) would have at most two entries (if the size chosen was 20mm)

Is your data more complex than that?
Will your data be changing over time?

yes thats just an example so i know the code then i can adapt the rest of the information to it.

it is going to be a price estimating software so it will have all the stock we use eventually, so yes the data will be changing over time

thanks for the help

[lovett10]

Please Help

[mikerickson]

Are you always going to have those blank lines between colors?
If so, this won't work, so I removed them.
If you absolutly have to have them use a loop rather than Advanced Filter.

Dim rngData As Range
Dim critRange As Range

Private Sub ComboBox1_Click()
    Dim oneCell As Range
    If ComboBox1.ListIndex <> -1 Then
    critRange.Cells(2, 1).Value = "*" & ComboBox1.Text
    rngData.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=critRange, Unique:=False
    ComboBox2.Clear
    For Each oneCell In rngData.Columns(3).Offset(1, 0).SpecialCells(xlCellTypeVisible)
        ComboBox2.AddItem Left(oneCell.Value, 4)
   Next oneCell
   ComboBox3.Clear
    End If
End Sub

Private Sub ComboBox2_Click()
    Dim oneCell As Range
    If ComboBox2.ListIndex <> -1 Then
    critRange.Cells(2, 1).Value = ComboBox2.Text & "*" & ComboBox1.Text
    On Error Resume Next
    rngData.Parent.ShowAllData
    On Error GoTo 0
    rngData.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=critRange, Unique:=False
    ComboBox3.Clear
    For Each oneCell In rngData.Offset(1, 0).Columns(3).SpecialCells(xlCellTypeVisible)
        ComboBox3.AddItem oneCell.Value
    Next oneCell
    End If
End Sub

Private Sub UserForm_Initialize()
    Set rngData = Sheet1.Range("A2").CurrentRegion
    Set critRange = rngData.Offset(0, rngData.Columns.Count + 1).Resize(2, 1)
    critRange.Cells(1, 1).Value = "Description"
    With ComboBox1
    .AddItem "Black"
    .AddItem "Grey"
    .AddItem "White"
    End With
    Me.Caption = rngData.Address
End Sub

Private Sub UserForm_Terminate()
    With critRange
    On Error Resume Next
    .Parent.ShowAllData
    On Error GoTo 0
    .ClearContents
    End With
End Sub