PDA

View Full Version : Sleeper: UserForm ComboBox using data



lovett10
08-24-2011, 06:18 AM
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 6462

Aflatoon
08-24-2011, 06:44 AM
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
08-24-2011, 06:50 AM
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
08-24-2011, 06:55 AM
Then how do the other comboboxes get populated?

lovett10
08-24-2011, 07:00 AM
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
08-24-2011, 07:06 AM
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
08-24-2011, 07:10 AM
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
08-24-2011, 07:19 AM
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
08-24-2011, 07:29 AM
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
08-24-2011, 08:25 AM
Please Help

mikerickson
08-24-2011, 04:58 PM
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