Clearing A Combobox's List

[Ark68]

With my user form, the user selects a value from the dropdown list of combobox 1.
Based on the value combobox 1 (CB1), combobox 2 (CB2) is given a list unique to the result of CB1. The user can then select an appropriate value from CB2.

If the user changes their mind, and selects a different value from CB1, that change will normally repopulate CB2 with an updated list based on that change in CB1.

However, there are times when a user selects a value from CB1, and instead on CB2 being assigned a list, just a simple default value is sent to the control CB2. This normally isn't an issue. However, consider this scenario:

User selects COW form CB1. That in turn assigns CB2 a list of "Guernsey, Jersey, Holstein, Brown, Dairy,". The user can select from this list in CB2.
User selects COW from CB1, but changes their mind before selecting from CB2 (which has the appropriate list assigned to it). They select CHICKEN from CB1.
There is no list for CB2 associated with the selection of CHICKEN in CB1. Rather, just a single default value ... Silkie. So, rather than the user havingto select from a list of 1, the code automatically populates the CB2 control with silkie. combobox1.value="Silkie".

But this results in an error "Could not set the value property. Invalid property value."

I assume this error is a result of the list from the original selection from CB1 (COW) still residing in CB2? That original list consists of Guernsey, Jersey, Holstein, Brown, Dairy. The error comes from the fact that Silkie isn't in the list of available selections, so its not a value CB2 will accept.

My question ... how do I get rid of the list in CB2 when the user makes a different selection from CB1?

 

[gallen]

You don't mention if the Combobox is ActiveX or form control.

Assuming it's ActiveX:
You would still need to populate the Combobox with a single item list. You can just set the value of an empty box. The value must exist in the list I believe

    CB2.Clear
    CB2.AddItem "Silkie"
    CB2.ListIndex = CB1.ListCount - 1

My question ... how do I get rid of the list in CB2 when the user makes a different selection from CB1?

To clear the list simply use

    CB2.Clear

 

[Ark68]

Hello Gallen, thank you for sharing your skill.
You;re right. I failed to mention therse are userform controls. Does that change the approach?

One thing that concerns me is working with one item lists, simply because of my lack of full understanding.
Because I have so many different lists gathered from ranges on a worksheet, I group these ranges as named ranges and then assign the comobox list with a signgle statement. In other words, the line applying the list is dynamic. For example ...

            If func = "Slopitch" Then
                Set nr_leag = ws_lists.Range("F2:F15")
            ElseIf func = "Baseball" Then
                Set nr_leag = ws_lists.Range("F20:F31")
            ElseIf func = "Softball" Then
                Set nr_leag = ws_lists.Range("F36:F43")
            ElseIf func = "Fastball" Then
                Set nr_leag = ws_lists.Range("F48:F53")
            ElseIf func = "Camp" Then
                Set nr_leag = ws_lists.Range("F171:F171") 'single item list of summer
                permit.cbx_league = "Summer"
            ElseIf func = "Special Event" Then
                Set nr_leag = ws_lists.Range("F189:F196")

       With permit
            .tb_miof.Caption = miof
            .cbx_func.BackColor = vbWhite
            .cbx_league.Enabled = True
            .cbx_league.List = Range("nr_league").Value
            .cbx_league.BackColor = clr_blue
        End With

My understanding is that you can't assign the list property to a single item list? That being said, how would I adapt my code to handle the single list item situation that arises when 'func = "Camp"'? The current code would result in an error at the line in red as its a single item list?

I hope I make sense.

 

[gallen]

Ahh, form controls aren't my speciality. Although your code doesn't look like it uses form controls. Do you use an actual userform object?

Any way this code gets a form control on a sheet, deletes all items and adds an item.

    Dim s As Shape
    
    Set s = Me.Shapes("Drop Down 1") 'create a variable to hold the Combobox
    
    s.ControlFormat.RemoveAllItems 'delete all items
    
    
    s.ControlFormat.AddItem ("Hello") ' add an item
    
    s.ControlFormat.ListIndex = 1 ' set the combobox to the first item in the list

 

[Fluff]

@gallen
The Op is using controls on a userform.

 

[Ark68]

Hi Fluff, yes ... form controls. 'permit' is the userform, 'cbx_league' is the combobox control.

When the user has to select a value from the combobox, it has has a list property assigned based on a unique named range determined by the user selected value of func (func is actually selected from a preceeding combobox ... aka "CB1" of my original post). But there are times where the user does not have to select a value, and a default value is applied to the combobox (cbx_league). But based on my code, I can't do that properly as a list has to be applied and a list cannot be a single value.

The original problem is the the prevention of an error when a single default value is applied to cbx.league in which a list had been previously applied to that control and that list does not contain that value. For that single value to be applied without an error, the previous list has to be first eliminated.

 

[gallen]

Yep Misread the post. Confused my self.

how would I adapt my code to handle the single list item situation that arises when 'func = "Camp"'?

Instead of using the 'List' method, use the 'additem'.

.cbx_league.Clear
.cbx_league.Additem Range("nr_league").Value
.cbx_league.ListIndex = 0

 

[RoryA]

I'd just turn the single value into an array and assign that:

If Range("nr_league").Count = 1 then
.cbx_league.List = array(Range("nr_league").Value)
else
.cbx_league.List = Range("nr_league").Value
end if