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.

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
"hgdev" wrote in message
om...
| 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.

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?


"Jim Rech" wrote in message ...
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
"hgdev" wrote in message
om...
| 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.

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





hgdev wrote:

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.

--

Dave Peterson

set rng = Range("A1:A10")
Listbox1.Rowsource = rng.Address(external:=True)

--
Regards,
Tom Ogilvy

"hgdev" wrote in message
om...
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?


"Jim Rech" wrote in message
...
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
"hgdev" wrote in message
om...
| 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.

Dave, gracious thanks.
Sorry, I neglected to say in my original post that I want the Headers
to appear in the Listbox. This was my reason for trying a different
approach such as the AutoFilter _FilterDatabase approach. Because I
knew(from testing) that .AddItem will not provide the natural Headings
that Rowsource produces.

Is there any line in your provided code that will allow the Headings?

Thanks

Dave Peterson wrote in message ...
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





hgdev wrote:

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.

Is there any line in your provided code that will allow the Headings?

Unfortunately, as you discovered, if you do not use Rowsource you cannot
have headings in a listbox.


http://support.microsoft.com/default...99&Product=xlw


--
Jim Rech
Excel MVP

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.)

hgdev wrote:

Dave, gracious thanks.
Sorry, I neglected to say in my original post that I want the Headers
to appear in the Listbox. This was my reason for trying a different
approach such as the AutoFilter _FilterDatabase approach. Because I
knew(from testing) that .AddItem will not provide the natural Headings
that Rowsource produces.

Is there any line in your provided code that will allow the Headings?

Thanks

Dave Peterson wrote in message ...
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





hgdev wrote:

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.

--

Dave Peterson