ComboBox List Selection Issue

[MichaelRSnow]

Hi

I have a very annoying issue that i can;t get my head around, I have a ComboBox change event that refreshes a list as its typed and reduces the options in the drop down list. Works fine

BUT

Once I have selected a item from the list, running any other code/macros elsewhere in the document re-runs the change event which interrupts and breaks these macros and it re-selects the question previously selected.

Is there anyway i can get this list to refresh whilst typing in the ComboBox list box but not Auto-refresh this list when using other ComboBox's and VB Macro afterwards

Code used to create dynamic drop down selection list that refreshes and reduces as words are typed

Sub Question_Change()
'Question
If Sheets("ASK").Range("B8").Value = "" Then
ComboBox1.ListFillRange = "DropDownList1"
Me.ComboBox1.DropDown
Else
ComboBox1.ListFillRange = "DropDownList2"
Me.ComboBox1.DropDown
End If
End Sub

 

[RoryA]

Don't use the ListFillRange. Set the List property to the value of the range - like this:

Sub Question_Change()
'Question
If Sheets("ASK").Range("B8").Value = "" Then
ComboBox1.List = Application.Range("DropDownList1").Value
Me.ComboBox1.DropDown
Else
ComboBox1.List = Application.Range("DropDownList2").Value
Me.ComboBox1.DropDown
End If
End Sub

 

[MichaelRSnow]

I get a Run-time error 70, permission denied error if i make that adjustment

debugs on this line of code

ComboBox1.List = Application.Range("DropDownList1").Value

List Range is defined as
=DATA!$N$2:INDEX(DATA!$N$2:$N$4994,MAX(DATA!$M$2:$M$4994),1)

 

[RoryA]

You'll need to clear the ListFillRange from the combobox properties first.

 

[MichaelRSnow]

You'll need to clear the ListFillRange from the combobox properties first.

Thanks that has got past the error, but the list range is filtered following another ComboBox selection, its not pulling that updated list through if you select the dropdown to view the list, it only updates once you have started typing a a word

You see a list of over a thousand, when in reality there is only 20 in the list following the other ComboBox Selection.

Is there a way for the list to update then when you type a word that list reduces further?

 

[RoryA]

Whatever code you use to filter that list will also need to update the List property of this combobox. There is no direct link between the two (which is why you no longer have the issue of the Change event being triggered by other things you do).

 

[MichaelRSnow]

Whatever code you use to filter that list will also need to update the List property of this combobox. There is no direct link between the two (which is why you no longer have the issue of the Change event being triggered by other things you do).

Thanks RoryA, much appreciated, i'll keep playing with it

 

[MichaelRSnow]

Thanks RoryA, much appreciated, i'll keep playing with it

Sorry one final question, didn't think to select from the list once it filters it down, when It gets down to one item it my list range, i.e. selected an list item, i get this error

Run Time Error 381 could not set the List Property. Invalid property Array index?

The list range does contain this 1 item?

 

[RoryA]

If there's only one cell in the range, the Value property doesn't return an array, so you'll need to cater for that. I'd add a separate function:

Function ArrayFromRange(r as range) As variant
If r.cells.count = 1 then
    arrayfromrange = array(r.value)
else
   arrayfromrange = r.value
end if
end function

then replace the previous code with:

Sub Question_Change()
'Question
If Sheets("ASK").Range("B8").Value = "" Then
ComboBox1.List = arrayfromrange(Application.Range("DropDownList1"))
Me.ComboBox1.DropDown
Else
ComboBox1.List = arrayfromrange(Application.Range("DropDownList2"))
Me.ComboBox1.DropDown
End If
End Sub

 

[MichaelRSnow]

If there's only one cell in the range, the Value property doesn't return an array, so you'll need to cater for that. I'd add a separate function:

Function ArrayFromRange(r as range) As variant
If r.cells.count = 1 then
    arrayfromrange = array(r.value)
else
   arrayfromrange = r.value
end if
end function

then replace the previous code with:

Sub Question_Change()
'Question
If Sheets("ASK").Range("B8").Value = "" Then
ComboBox1.List = arrayfromrange(Application.Range("DropDownList1"))
Me.ComboBox1.DropDown
Else
ComboBox1.List = arrayfromrange(Application.Range("DropDownList2"))
Me.ComboBox1.DropDown
End If
End Sub

Awesome, that's perfect. Thanks Again