Clear second and third dependent worksheet ActiveX combobox with named ranges

Markus71

New Member
Joined
May 30, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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?

Code:
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Markus71

New Member
Joined
May 30, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
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 ...

VBA Code:
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

New Member
Joined
May 30, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
Finally found the trigger: Bound Column was set to 0

Capture.JPG
 
Solution

Forum statistics

Threads
1,140,926
Messages
5,703,211
Members
421,282
Latest member
hogie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top