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