Filtering ComboBox

  • Thread starter Thread starter shrekut
  • Start date Start date
S

shrekut

I am getting a Run Time Error 70 Permission Denied! on this line in th
below code (which I got from another Thread).
ComboBox1.List = Filter(varr1, ComboBox1.Value, True, vbTextCompare)
Any suggestions?

Thank you.

Private Sub Combobox1_Change()
Dim rng As Range, varr1 As Variant
Set rng = Range("d1:e6")
varr1 = rng.Value
varr1 = MakeOne(varr1)
ComboBox1.List = Filter(varr1, ComboBox1.Value, True,
vbTextCompare)
End Sub

Private Sub Combobox1_Click()
Exit Sub
Dim rng1 As Range, rng As Range
Dim res As Variant
Set rng = Range("d1:e6")
res = Application.Match(ComboBox1.Value, rng, 0)
If Not IsError(res) Then
Set rng1 = rng(res)
Range("a1").Value = rng1.Row
Else
Range("a1").Value = "No Match"
End If
End Sub

Public Function MakeOne(varr As Variant)
Dim varr2 As Variant, i As Long
ReDim varr2(LBound(varr, 1) To UBound(varr, 1))
For i = LBound(varr, 1) To UBound(varr, 1)
varr2(i) = varr(i, LBound(varr, 2))
Next
MakeOne = varr2
End Functio
 
Is data bound to the combobox using RowSource or ListFillRange? If so, it
can't also be populated using vba. Use either vba or binding, not both.
 
Thanks Tim. That explains it. I was using ListFillRange. That brings u
another question then. Since I have the user enter the filter strin
into the ComboBox, should I use that value to filter the data and us
AddItem on only that data? I have not played around with that muc
since I found the ListFillRange property...

Thanks
 
Back
Top