PDA

View Full Version : Sleeper: Exit/hide combobox when item in dropdownlist is chosen



sirfred33
03-19-2015, 05:24 AM
Hi there,

I have 4 regular datavalidated dropdowns in total each activate a combobox which changes the format of the dropdown



In the 1st picutre you can see the combobox in A9 is still active and in B9 I have chosen the "Oak Soap" variant, but the combobox is still active
Is there a way to exit or hide a combobox when an item is selected from the dropdown list activated by the combobox?

I'm using this code at the moment, which only closes the other dropdowns, but doesn't exit their comboboxes



Dim str As StringDim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
If Target.Address = "$A$9" Is Nothing Then
Set cboTemp = ws.OLEObjects("TempCombo")
ws.OLEObjects("TempCombo2").Visible = False
ws.OLEObjects("TempCombo3").Visible = False
ws.OLEObjects("TempCombo4").Visible = False
ElseIf Not Application.Intersect(Target, Range("B9")) Is Nothing Then
Set cboTemp = ws.OLEObjects("TempCombo2")
ws.OLEObjects("TempCombo").Visible = False
ws.OLEObjects("TempCombo3").Visible = False
ws.OLEObjects("TempCombo4").Visible = False
ElseIf Not Application.Intersect(Target, Range("C9")) Is Nothing Then
Set cboTemp = ws.OLEObjects("TempCombo3")
ws.OLEObjects("TempCombo").Visible = False
ws.OLEObjects("TempCombo2").Visible = False
ws.OLEObjects("TempCombo4").Visible = False
ElseIf Not Application.Intersect(Target, Range("D9")) Is Nothing Then
Set cboTemp = ws.OLEObjects("TempCombo4")
ws.OLEObjects("TempCombo").Visible = False
ws.OLEObjects("TempCombo2").Visible = False
ws.OLEObjects("TempCombo3").Visible = False
Else
Exit Sub


Also, can I set the first item in the a combobox dropdown list as chosen by default?

The 3 dropdowns in B9,C9,D9 are dependent to A9 so if the first value in those 3 could be auto selected.

See the 2nd picture for better understanding

I tried this as a test for the dropdown in B9, but it doesn't auto select (also tried changing the value from 0 to 1)


Private Sub Tempcombo_Selectionindex()TempCombo2.SelectedIndex = TempCombo2.Items.Count - 0
End Sub


Thank you for helping

sirfred33
03-20-2015, 04:04 AM
Solved the first problem, so only need an answer for the last part about selecting the first item/value in the 3 dropdowns