Autofilter and Listbox how to acomplish?
 

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 :confused:


--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=381432

Autofilter and Listbox how to acomplish?
 

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 wrote:

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 :confused:

--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=381432

--

Dave Peterson

Autofilter and Listbox how to acomplish?
 

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 Wrote:
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 wrote:

Hi,

I have a worksheet with data (7 columns, almost 200 rows). I woul
like
to see the results of an Autofilter operation in this worksheet t
be
"reflected" on a Listbox at another wksheet. The listbox is for
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 ho
to
procced?

Thank you in advance,

Regards

Jose Luis :confused:

--
jose luis

------------------------------------------------------------------------
jose luis's Profile
http://www.excelforum.com/member.php...o&userid=13312
View this thread
http://www.excelforum.com/showthread...hreadid=381432

--

Dave Peterso

--
jose lui
-----------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...fo&userid=1331
View this thread: http://www.excelforum.com/showthread.php?threadid=38143

Autofilter and Listbox how to acomplish?
 

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 wrote:

Thank you Dave,

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

Code:
--------------------
"With Me.ListBox1
.Clear
--------------------

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 With
--------------------

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

Thanks again for your time and knowledge!

Jose Luis

Dave Peterson Wrote:
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 wrote:

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 :confused:

--
jose luis

------------------------------------------------------------------------
jose luis's Profile:
http://www.excelforum.com/member.php...o&userid=13312
View this thread:
http://www.excelforum.com/showthread...hreadid=381432

--

Dave Peterson

--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=381432

--

Dave Peterson

Autofilter and Listbox how to acomplish?
 

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 Wrote:
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 wrote:

Thank you Dave,

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

Code:
--------------------
"With Me.ListBox1
.Clear
--------------------

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 With
--------------------

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

Thanks again for your time and knowledge!

Jose Luis

Dave Peterson Wrote:
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 wrote:

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 :confused:

--
jose luis


------------------------------------------------------------------------
jose luis's Profile:
http://www.excelforum.com/member.php...o&userid=13312
View this thread:
http://www.excelforum.com/showthread...hreadid=381432

--

Dave Peterson

--
jose luis

------------------------------------------------------------------------
jose luis's Profile:
http://www.excelforum.com/member.php...o&userid=13312
View this thread:
http://www.excelforum.com/showthread...hreadid=381432

--

Dave Peterson


--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=381432

Autofilter and Listbox how to acomplish?
 

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 wrote:

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 Wrote:
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 wrote:

Thank you Dave,

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

Code:
--------------------
"With Me.ListBox1
.Clear
--------------------

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 With
--------------------

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

Thanks again for your time and knowledge!

Jose Luis

Dave Peterson Wrote:
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 wrote:

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 :confused:

--
jose luis


------------------------------------------------------------------------
jose luis's Profile:
http://www.excelforum.com/member.php...o&userid=13312
View this thread:
http://www.excelforum.com/showthread...hreadid=381432

--

Dave Peterson

--
jose luis

------------------------------------------------------------------------
jose luis's Profile:
http://www.excelforum.com/member.php...o&userid=13312
View this thread:
http://www.excelforum.com/showthread...hreadid=381432

--

Dave Peterson

--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=381432

--

Dave Peterson

Autofilter and Listbox how to acomplish?
 

Thank you Dave,

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

Regards

Jose Luis

Dave Peterson Wrote:
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 wrote:

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 Wrote:
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 wrote:

Thank you Dave,

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

Code:
--------------------
"With Me.ListBox1
.Clear
--------------------

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 With
--------------------

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

Thanks again for your time and knowledge!

Jose Luis

Dave Peterson Wrote:
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 wrote:

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 :confused:

--
jose luis



------------------------------------------------------------------------
jose luis's Profile:

http://www.excelforum.com/member.php...o&userid=13312
View this thread:
http://www.excelforum.com/showthread...hreadid=381432

--

Dave Peterson

--
jose luis


------------------------------------------------------------------------
jose luis's Profile:
http://www.excelforum.com/member.php...o&userid=13312
View this thread:
http://www.excelforum.com/showthread...hreadid=381432

--

Dave Peterson

--
jose luis

------------------------------------------------------------------------
jose luis's Profile:
http://www.excelforum.com/member.php...o&userid=13312
View this thread:
http://www.excelforum.com/showthread...hreadid=381432

--

Dave Peterson


--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=381432