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