Set back Dropdown selection in one dropdown after the selection in an other dropdown has been changed

[Nadine1988]

Hello,

so first of all - this is not about dependent dropdown lists - The dropdown lists in my excel file are already dependent from each other. What I would like to know if it's possible to automatically change what is set in one dropdown when changing something in an other one.
I do have a huge amount of date and I can't put this in one If formula within every dropdown. So to summarize:
Main Dropdown is located in field C33 - There are two dependent Dropdown list in C35 and C46- So if you change C33 after you've changed C35 or C46 i would like the dropdown list in C35 and C46 to adjust automatically to "_Please choose" (which is part of the data list)
Additionally I have 3 dropdown lists each depending on C35 and C46 - (C35: C37, C39 and C41 / C46: C48, C50 and C52) - so if you change C35 and / or C46 the related dropdowns needs to adjust automatically to "_Please choose" again.

And then again. I do have an other dropdown in Field C59 - dependent on the dropdown in C57 and three dropdowns in C61, C63 and C65 depending on the dropdown in C59

I hope this is not too confusing and someone can help me with the code here. I actually don't have any idea and can't find anything when googleing

Thanks for your assistance
Nadine

 

[Micron]

Is this not as simple as (using your control names of course)

Private Sub ComboBoxMain_Change()
ComboBoxSub1 = "_Please Choose"
ComboBoxSub2 = "_Please Choose"
End Sub

or are these lists validation lists?
Your details don't seem to be the same in the cases you outline. In one case, the subs are to change only if they've already had selections made.

if you change C33 after you've changed C35 or C46

I the other case, no such dependency seems to apply.

if you change C35 and / or C46 the related dropdowns needs to adjust automatically

 

[Nadine1988]

Hello Micron - thank you for your answer.
I am working with validations lists, yes - sorry if I wasn't clear about that - I'm actually not a pro in excel, I'm just solving one error after an other until I'll receive the result I'm looking for

Not sure if you get what you mean in the second part of your message but I'll try to explain the realtion again

Main: C33
- C35
- C46

Main: C35
- C37
- C39
- C41

Main: C46
- C48
- C50
- C52

So every time you change the "Main" dropdown the related dropdowns below need to be changed back to "_Please choose" - hope that's clearer

Thanks
Nadine

 

[Micron]

You realize that if you change C33 (first condition you show) and as a result edit 35 and 46, the 2nd and 3rd conditions are also in effect? So change those also?
If you change 35 or 46 no cascading changes are made, but there are cascading changes if you change 33. Sorry for not completely grasping the goal here.
I have written code that might do what you want but it needs to be altered if there is to be cascading. Also, it doesn't exactly follow this

if you change C33 after you've changed C35 or C46

My code affects 35 and 46 regardless of whether or not anything was previously chosen there. That means if you're starting with blanks there, they will change to the text. If that text is already there, then it will remain (basically, it gets over-written with the same text).

 

[Nadine1988]

Hi,

Yes, it actually all depends at the end on C33 that's correct. Sorry I wasn't clear. I think I'm getting confused here with all the codes and everything.

So if you change C33 all of them (so - C37- C39 - C41 - C46 - C48 - C50 and - C52) needs to be set back to "_Please choose"
If you change C35 only
- C37
- C39
- C41
needs to be set back

and if you change C46
- C48
- C50
- C52
needs to be set back.

Does this make sense now?

Thanks so much!
Best
Nadine

 

[Micron]

See if this works for you. Code goes into the sheet module. It may need not be suitable if you use it over thousands of rows because in order to do as you request, it needs to call itself again. That is, changing C33 will cause the code to change other cells and those changes will cause the code to call itself again.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str As String

str = "_Please Choose"
''Application.EnableEvents = False 'don't use - code needs to call itself
Select Case Target.Address
     Case "$C$33"
          Range("C35") = str
          Range("C46") = str
     Case "$C$35"
          Range("C37") = str
          Range("C39") = str
          Range("C41") = str
     Case "$C$46"
          Range("C48") = str
          Range("C50") = str
          Range("C52") = str
End Select
''Application.EnableEvents = True
End Sub

Actually, I think it might be best to allow the code to call itself (be recursive) only if C33 is changed but not for 35 or 46. I think test what I posted first.

 

[Nadine1988]

Oh good that's actually working :-O I will make some tests tomorrow when I'm back at work and will let you know. Thanks so much!

Best
Nadine

 

[Nadine1988]

Thank you - works perfectly fine!

 

[Micron]

Glad I could help & thanks for the recognition. In case you or anyone down the road could make use of an error handler when cycling application settings, here's one that is dependent upon your situation. That is, this event code should be prevented from repeatedly running when you have not edited C33. However, when you've edited C33 you want this procedure to call itself (changing other cells like C35 and C46 is a sheet change, so this code will call itself as a result). One has to be careful with this approach because you can end up with endless repetitions.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str As String

On Error GoTo errHandler
str = "_Please Choose"

Select Case Target.Address
     Case "$C$33"
          Range("C35") = str
          Range("C46") = str
     Case "$C$35"
          Application.EnableEvents = False
          Range("C37") = str
          Range("C39") = str
          Range("C41") = str
     Case "$C$46"
          Application.EnableEvents = False
          Range("C48") = str
          Range("C50") = str
          Range("C52") = str
End Select
exitHere:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub