How to auto-clear dependent dropdowns when parent cell is changed while using combobox to format list?

[sirfred33]

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:

• 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

So the 3 dependent cells won't clear when using the dropdown formatted with combobox
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

 

[L. Howard]

This is a bit of a long shot, but MAY be in the theme of what you are trying to do with combo boxes...??

There is a link to a workbook, (not in English) but seemed to do some of what you ask.

VBA - How to create a cascading combo box in a userform

It puts them all on a userform and the dependent choices are deleted when a MAIN category is chosen.

Just a stab on my part, I admit.

Howard