Filtering ComboBox
 

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

--
Message posted from http://www.ExcelForum.com

Filtering ComboBox
 

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.


"shrekut " wrote in message
...
I am getting a Run Time Error 70 Permission Denied! on this line in the
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 Function


---
Message posted from http://www.ExcelForum.com/

Filtering ComboBox
 

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

--
Message posted from http://www.ExcelForum.com