clear dependant drop down boxes

[keith barnes]

Hi Guys

Please find attached for help required.

It is a slightly old suggestion supplied by a kind Memeber that allow me to compile a userform that has dependant drop down boxes.
It work fine apart from now i need to create a "clear button" that removes entries from each of the combos.

I have tried using the " .combobox1.Value = "", which normally works but in this instance it comes up with an error.



Can someone please help

Regards

Keith

[JKwan]

You mean?



.combobox1.listindex=-1


This will make it blank

[keith barnes]

You mean?



.combobox1.listindex=-1


This will make it blank

[keith barnes]

Hi Friend

Thanks for the help but it stil comes up the the same error.

Help further required

Regards

keith

[nilem]

Hi ikeith barnes (http://www.vbaexpress.com/forum/member.php?84816-keith-barnes),
try it

Private Sub combobox1_Change()
With Me.Combobox2
.ListIndex = -1
If Me.combobox1.ListIndex <> -1 Then
.RowSource = Sheet1.ListObjects("table" & Me.combobox1.ListIndex + 1)
Else
.RowSource = ""
End If
' .RowSource = IIf(Me.combobox1.ListIndex <> -1, Sheet1.ListObjects("table" & Me.combobox1.ListIndex + 1), "")
End With
End Sub

[snb]

Avoid rowsource.
Why emptying the comboboxes ? Doesn't sound sophisticated.

This is all you need: (after removing the rowsource property of combobox1):


Private Sub UserForm_Initialize()
combobox1.Column = Sheet1.ListObjects(1).HeaderRowRange.Resize(, 4).Value
End Sub

Private Sub combobox1_Change()
If combobox1.ListIndex > -1 Then Combobox2.List = Sheet1.ListObjects(1).DataBodyRange.Columns(combobox1.ListIndex + 1).Value
End Sub

Private Sub CommandButton1_Click()
combobox1.Clear
Combobox2.Clear
End Sub