AutoFilter _FilterDatabase Rowsource Listbox

[hgdev]

Excel 2000

I am trying to get AutoFilter data into a ListBox.
The below code gives me an "Type mismatch" error when I try to add the
Listbox Rowsource line. I read about using _FilterDatabase but could
not get the right syntax.

Private Sub UserForm_Initialize()
Dim myVisibleRng As Range
Dim myFilterRng As Range
Set myFilterRange = Sheet1.Range("a1:e13000")
myFilterRange.AutoFilter Field:=5, Criteria1:="18650"
Set myVisibleRange = myFilterRange.SpecialCells(xlCellTypeVisible)
Debug.Print myVisibleRange.Address
ListBox1.RowSource = myVisibleRange '<--type mismatch
End Sub


Thanks.

 

[Jim Rech]

RowSource is looking for a string (the range address as a string), not a
range object. check out the example in Help.

--
Jim Rech
Excel MVP
| Excel 2000
|
| I am trying to get AutoFilter data into a ListBox.
| The below code gives me an "Type mismatch" error when I try to add the
| Listbox Rowsource line. I read about using _FilterDatabase but could
| not get the right syntax.
|
| Private Sub UserForm_Initialize()
| Dim myVisibleRng As Range
| Dim myFilterRng As Range
| Set myFilterRange = Sheet1.Range("a1:e13000")
| myFilterRange.AutoFilter Field:=5, Criteria1:="18650"
| Set myVisibleRange = myFilterRange.SpecialCells(xlCellTypeVisible)
| Debug.Print myVisibleRange.Address
| ListBox1.RowSource = myVisibleRange '<--type mismatch
| End Sub
|
|
| Thanks.

 

[hgdev]

Ok. Thanks for response.
I see that now in Help; object.RowSource[=String]

It has taken me hours to get this far.
How do I turn/convert the Range Object output into a string to satisfy
the .RowSource?

 

[Dave Peterson]

You should add:

Option Explicit
to the top of your module.

You have some variables that use Rng and/or Range.
Dim myFilterRng As Range
Set myFilterRange = ....

The "option explicit" will catch those typos for you and could save you lots of
time later.

And to add to Jim's post, you'd want to do something like this:

listbox1.rowsource = myvisiblerange.address(external:=true)

but this won't work if your range is discontiguous. I think you'll have to loop
through those visible cells and .additem each value.

Option Explicit
Private Sub UserForm_Initialize()
Dim myVisibleRng As Range
Dim myFilterRng As Range
Dim myCell As Range
Dim cCtr As Long

Set myFilterRng = Sheet1.Range("a1:e13000")
'instead of 13000 rows, can you use a column (like A) to determine
'the lastrow?
'with sheet1
' set myfilterrng _
= .range("a1:e" & .cells(.rows.count,"A").end(xlup).row)
'end with

myFilterRng.AutoFilter Field:=5, Criteria1:="18650"

If myFilterRng.Columns(1) _
.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'no details found
Exit Sub
End If

With myFilterRng
Set myVisibleRng = .Resize(.Rows.Count - 1, 1) _
.Offset(1, 0).Cells _
.SpecialCells(xlCellTypeVisible)
End With

Debug.Print myVisibleRng.Address
With ListBox1
.ColumnCount = 5
For Each myCell In myVisibleRng.Cells
.AddItem myCell.Value
For cCtr = 2 To 5 'B:E
.List(.ListCount - 1, cCtr - 1) _
= myCell.Offset(0, cCtr - 1).Value
Next cCtr
Next myCell
End With
End Sub

 

[Dave Peterson]

If you change this line:

Set myVisibleRng = .Resize(.Rows.Count - 1, 1) _
to
Set myVisibleRng = .Resize(.Rows.Count, 1) _

then you'll see "headers"--but they can be selected. (not sure if that's
acceptable.)