Worksheet_Change(ByVal Target As Range)

[julhs]

My goal is to “Reset” to “Clear/Blank/Empty/Nothing” Combobox #3 and #4 on “Change” of Combobox #2

I have a Userform that has 4 Comboboxes, #1 is NOT dependent on ANY and works independently. #2 is the Primary the one ; it IS connected TO, but NOT dependent on either #3 or #4.
Both #3 & #4 ARE dependent on #2.
#4 is also dependent #2, but only really dependent on #3

As I have used a “Change Event” earlier for “cmbExpenditureSubGroups”, it appears I can’t re-use “Change Event” again for the same “Combobox”, just get “Ambiguous Name” error.

So I thought I could try different approach and use “Worksheet_Change(ByVal Target As Range)”
But I can’t get it it to operate;
Relevant info;
Combobox#2 = cmbMainExpenditureGroups
Combobox#3 = cmbExpenditureSubGroups
Combobox#4 = cmbSubGroupsList
and using “Named Ranges” to fill all Comboboxes
This is the code that is not working/resetting things.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = cmbMainExpenditureGroups Then
Range(cmbExpenditureSubGroups).Value = ""
Range(cmbSubGroupsList).Value = ""
If Target.Address = cmbExpenditureSubGroups Then
Range(cmbSubGroupsList).Value = ""
End If
End If
Application.EnableEvents = True
End Sub

 

[6StringJazzer]

Worksheet_Change will be called only if a cell changes, not controls like comboboxes. You need to manage this with cmbExpenditureSubGroups_Change but you said you already had that. Why can't you add code in that Sub to do this:

Private Sub cmbExpenditureSubGroups_Change()

   cmbExpenditureSubGroups.Clear
   cmbSubGroupsList.Clear

' Other stuff you already want to do if this combobox changes

End Sub

 

[julhs]

Thanks 6StringJazzer

I have restructured things as you suggested and that has dealt with the “Ambiguous Name” error.

Think I have narrowed down the issue I’m trying to resolve?
It is confined to the resetting of "cmbSubGroupsList" to "Clear/Blank/Empty/Nothing" on a change
to the INITIAL “cmbMainExpenditureGroups” selection; the “cmbSubGroupsList” is retaining the initial list.

Using cmbSubGroupsList.Value = "" does prevent anything actually being displayed in "cmbSubGroupsList" but not clearing it of course.
The “cmbExpenditureSubGroups” does behave as expected on change in “cmbMainExpenditureGroups”.
I've tried including “cmbSubGroupsList.Clear” to both “cmbMainExpenditureGroups_Change()” and “cmbExpenditureSubGroups_Change()” codes

but results in a “Run-Time error ‘-2147467259 (8004005) Unspecified error”

Do you have any insight as to why?

 

[julhs]

Can someone confirm whether or not I can use "cmbList.Clear" in Excel 2010, if you can what element do I need to add to the "References" that will enable me to use it?
Because at the moment I just get "Run-Time error ‘-2147467259 (8004005) Unspecified error”.
Note; I am using "RowSource" to populate the Combobox and not "Add Item" if that makes the difference?

 

[6StringJazzer]

You cannot use Clear if you are populating it with RowSource. (No reference is needed.)

Can you clarify what you mean by this:

My goal is to “Reset” to “Clear/Blank/Empty/Nothing” Combobox #3

My explanation was to clear out the list of entries in the combobox. Did you want to do that, or simply to set it so that no entry is selected?

 

[julhs]

It seems I may have missed the fundamental point you were making!!
So to be clarify; if I'm using "RowSource" I cant use " cmb.Clear"?
But using; cmb.Value = "" ONLY sets the cmb box to DISPLAY a "Blank cmb box", but it is not "Clear"
Because when I click the cmb drop down arrow it still retains in memory the last selection.

I'm in a conundrum, if I can' use "cmb.Clear" and cmb.Value = "" does not "Clear" the cmb box then it seems I'm a bit stuffed!?

 

[6StringJazzer]

Clear removes all entries from the combobox. You can't do that if it is using a worksheet range to fill the data.

If you just want to clear the user selection use

cmb.ListIndex = -1

 

[julhs]

I'll try that and see where it gets me, as apposed to

cmb.Value = ""

Many thanks

 

[julhs]

6StringJazzer, really sorry to be coming back to you again!!

Yes, cmb.ListIndex=-1 does clear the Combobox WINDOW to “Blank”, but so does cmb.Value = "" and cmb = ""
But what I’m trying to address is that when I click on the ComboBox dropdown arrow you still see (retained in memory) the list from the last selection that was made.

If it helps to clarifying what I mean by “Clear/Blank/Empty/Nothing”, it is to return the ComboBox to the "NULL" state they are in when you “Initialize” the UserForm.
At the moment the only way I see to achieve that is to CLOSE the UserForm and Re-Initialize/Open it again, which seems "OTT"

Do you think I need to go back and use a different method to populate the ComboBoxs to ultimately be able to do (with any one of your suggestions) what I want to do to reset them?

This has become a right royal pain in the "A***" for what is simply an annoying issue, right now I’m about to give up and live with it !!!

Your thoughts are greatly appreciated.

 

[6StringJazzer]

Do you have access to a file sharing service like Dropbox? At this point I can't make any more progress without being able to dig into your file.