Clear second and third dependent worksheet ActiveX combobox with named ranges

[Markus71]

Dear all,

I am facing a logical problem which I am not able to solve.
I have three dependent comboboxes in an worksheet.
Based on Combobox1 selection, Combobox2 loads and based on Combobox2 selection ComboBox3 loads. All from namged ranges which are produced by an SQL query.
So far everything works fine, except the fact, that the last selection of Combobox 2 and Combobox three is still visible after the refresh.
This is confusing for the user, because it looks like a selection has already been made.
After repopulating of the combobox the combobox should show either the first value of the list or nothing.

I tried the following to clear the third Combobox.
Code was executed with Combobox2 change event.

FamilySelect.Value = Null
FamilySelect.ListIndex = 0
FamilySelect.Value = ""

Works all fine, but doing that results in the situation, that the Combobox3 do not show the actual selection anymore.
It always stays empty. After removal of any . Clear method is shows again the selection which was made.
Where is the missing link in my thoughts?

Sub MakeSelect_Change()
      Call SQL_Size
      Sheets(1).OLEObjects("SizeSelect").ListFillRange = "Size"
End Sub
Sub SizeSelect_Change()
      Call SQL_Family
      Sheets(1).OLEObjects("FamilySelect").ListFillRange = "Family"
End Sub
Sub FamilySelect_Click()
     Call Pattern_Dimensions
     Call Bolts
      Call RefreshPatternName
End Sub

Maybe somebody can bring me back on track. Thanks in advance
Markus

 

[Markus71]

Hi, for some reason, the same effect shows on another ComboBox when I try to set a semi dynamic range.
After populating the ComboBox, the values show up, but after clicking an list item, the ComboBox stays empty ...

Sub URange()
' ----------------------------------------------------------------------------------------
' Purpose.....: Resize range
' ----------------------------------------------------------------------------------------
ActiveSheet.USelect.ListFillRange = "Menu!A2:A" & ActiveSheet.SizeSelect.Value + 2
End Sub

Can't find the problem .... hope somebody can help me to understand

 

[Markus71]

Finally found the trigger: Bound Column was set to 0