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


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

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

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

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

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

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

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