Hi there,
How do I auto-clear dependent dropdowns when the parent cell is changed, while using a combobox to format the dropdown lists?
The dropdown lists are datavalidated cells and the size+font in the lists is formatted with an ActiveX combobox and coding
The parent cell is A9 and the dependent cells are B9:D9. I've tried the following which only works when I use the regular dropdown list, but not when using the dropdown formatted with combobox:
<code style="font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; display: block; font-size: 1em; line-height: 1.42857142857143em; padding: 0px !important; background-color: transparent;">Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A9")) Is Nothing Then
Target.Offset(0, 1).ClearContents
Target.Offset(0, 2).ClearContents
Target.Offset(0, 3).ClearContents
Target.Offset(0, 4).ClearContents
End If
End Sub</code>
To get a better image of what's happening:
This is the guide I've used to make the dropdowns with combobox (I use Code Sample 1): http://www.contextures.com/xlDataVal10.html
Long post I know, but I've tried finding a solution for some time and wanted to be sure my problem is understood.
Thanks for helping
How do I auto-clear dependent dropdowns when the parent cell is changed, while using a combobox to format the dropdown lists?
The dropdown lists are datavalidated cells and the size+font in the lists is formatted with an ActiveX combobox and coding
The parent cell is A9 and the dependent cells are B9:D9. I've tried the following which only works when I use the regular dropdown list, but not when using the dropdown formatted with combobox:
<code style="font-family: monospace, monospace; margin: 0px 2px; border: 0px; border-radius: 2px; display: block; font-size: 1em; line-height: 1.42857142857143em; padding: 0px !important; background-color: transparent;">Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A9")) Is Nothing Then
Target.Offset(0, 1).ClearContents
Target.Offset(0, 2).ClearContents
Target.Offset(0, 3).ClearContents
Target.Offset(0, 4).ClearContents
End If
End Sub</code>
To get a better image of what's happening:
- I Double click A9 and the dropdown formatted by combobox appears: http://i.imgur.com/ibKxDAN.png
- I choose a new type, CH20, but the other 3 cells don't clear: http://i.imgur.com/JYij4vK.png
- I instead single click on A9 and only the regular dropdown can be used. I choose a new type, CH29 and the other 3 cells clear like I want them to: http://i.imgur.com/VzDjXd5.png
This is the guide I've used to make the dropdowns with combobox (I use Code Sample 1): http://www.contextures.com/xlDataVal10.html
Long post I know, but I've tried finding a solution for some time and wanted to be sure my problem is understood.
Thanks for helping