Autofilter and Listbox how to acomplish?

[jose luis]

Hi,

I have a worksheet with data (7 columns, almost 200 rows). I would like
to see the results of an Autofilter operation in this worksheet to be
"reflected" on a Listbox at another wksheet. The listbox is form Active
Control, but could be changed to a Listbox from Forms. Wich one is
better o easier to implemment? Could you give some direction on how to
procced?


Thank you in advance,

Regards

Jose Luis

 

[Dave Peterson]

I used the listbox from the control toolbox toolbar (ActiveX controls) and put
it on sheet1.

Then I used this code behind the worksheet.

Option Explicit
Private Sub Worksheet_Activate()

Dim wks As Worksheet
Dim rng As Range
Dim rngF As Range
Dim myCell As Range
Dim iCtr As Long

Set wks = Worksheets("sheet2")
Set rng = wks.AutoFilter.Range

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

With Me.ListBox1
.Clear
.ColumnCount = rng.Columns.Count
For Each myCell In rngF.Cells
.AddItem (myCell.Value)
For iCtr = 1 To rng.Columns.Count - 1
.List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Value
Next iCtr
Next myCell
End With

End Sub

If you filter the data on sheet2, then go back (and activate sheet1), the
listbox gets updated.

 

[jose luis]

Thank you Dave,

I ve tried your recommendation, unfortunately I can't make ru
smoothly. It post a message telling me "Automation error" "Unspecifie
error" "Permission Denied"
in the line


Code
-------------------
"With Me.ListBox1
.Clea
-------------------


Besides, Could you explain me what is the function of:


Code
-------------------
With rng
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)
End Wit
-------------------


I'm trying to fully understand your post to implemment you
recomendation,

Thanks again for your time and knowledge!

Jose Luis

 

[Dave Peterson]

My bet is you assigned the .listfillrange to a range on one of those worksheets.

You can either change the .listfillrange property to nothing manually or in
code:

With Me.ListBox1
.ListFillRange = ""
.Clear

....

==========
Set rng = wks.AutoFilter.Range

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

Rng represents the autofilter range on your worksheet.

the "with rng" means that everything that begins with a dot will refer to rng
while you're in that

With rng
....
end wigh
structure.


rng.rows.count -1 just finds the number of rows in the autofilter range and
subtracts 1.

So if you filtered A1:C100 (100 rows), rng.rows.count -1 will give 99 (rows).

The .resize() portion means to take the size of the autofilter rng (100 rows by
3 columns in my example) and change it to 99 rows by 1 column
".Resize(.Rows.Count - 1, 1)".

But just resizing it would mean that we're looking at A1:A99. So we come down
one row and over 0 columns (.offset(1,0)).

So now the example will point at A2:A100.

The .cells.specialcells(xlcelltypevisible) means to just use the visible cells
in that column.

 

[jose luis]

Thanks Again Dave , Now the application is running smoothly. And I think
i understood in a better way your code. Just to finish, could you guide
me to format the last "field" in the ListBox? I wrote this but is not
working:



Code:
--------------------
With Sheets(1).ListBox1
.ListFillRange = ""
.Clear
.ColumnCount = rng.Columns.Count
For Each myCell In rngF.Cells
.AddItem (myCell.Value)
For iCtr = 1 To rng.Columns.Count - 1
if iCtr = 6 then
.List(.ListCount - 1, iCtr) = Format(myCell.Offset(0, iCtr).Value,"#,##0.#0")
else
.List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Value
end if
Next iCtr
Next myCell
End With
--------------------


Thank you very much for your help.

Regards

Jose Luis

 

[Dave Peterson]

maybe instead of checking:
if iCtr = 6 then
use:
if iCtr = rng.columns.count -1 then

(Your code worked ok for me--was it just a question about getting the format for
the last column?)

If the values in the cells are pretty, you could use .text instead of .value
(and format()).

..AddItem myCell.Text

And
..List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Text

 

[jose luis]

Thank you Dave,

Now the application is running and the numbers looking pretty .
Thanks again.

Regards

Jose Luis