Excel VBA - Populate combo box from autofiltered list in spreadsheet 2

[Chats]

Hi,

I'm using Excel 2000.

I have a speadsheet which has a table of data 3 columns wide (CODE, DESCRIPTION, CATEGORY). What I want to do is have the user to use autofilter on the CATEGORY column, and then to use VBA to populate the items of a combobox using ONLY the visible values from the autofiltered list (columns CODE and DESCRIPTION to be used, with CODE being the bound column).

How do I obtain ONLY the visible values from a filtered list and feed these into a combobox? I have tried using a For Next loop to check each row in turn for its property .Visible and if this is TRUE then use AddItem, but can't seem to get the syntax right.

Can anybody post some sample code which would get me on the right track?

Many thanks,

Ade

 

[Loomah]

Hi
Here's one way to populate a list box from an already filtered list (one column with header row). Dunno about using arrays for this so will be looking with interest!

Private Sub UserForm_Initialize()
Dim c As Range
For Each c In Worksheets(2).Range("A2:A" & _
    Cells(65536, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    Me.ListBox1.AddItem c.Text
Next
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

Hi
You can ignore my post. I had a small connection problem and when I got back this was intended for another thread but I didn't check I was posting to the right thread!

Having looked at your question some of what I've posted may be relevant....

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Chats]

Good luck for me that you had a connection problem - that has just about sorted out what I needed!

I think I have this one sorted now, so thanks.

Ade

 

[Chats]

I thought I had it sorted... one final hurdle.

My listbox is multi-column (2 columns). Using the code you posted (above) I can populate a one column of values in the listbox, but can't get it to fill in two columns.

I tried changing the range specified to two columns (Range("A2:B") in the For statement, but this just lists the items below eachother in a single column, rather than as two columns.

Any suggestions???

Thanks

Ade

 

[xlbo]

Loomah - to do it without a loop:

Dim ListArr() As Variant
ListArr() = Range("A1:B" & Cells(65536, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)

But you'd still need to loop from lBound to uBound with .additem to populate

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Loomah]

xlbo
Thanks for that. It's something I'm just realising, trying to get the data into 2 columns here. Unfortunately it's something I've never done before and I'm running out of time (big match tonight!)

I can populate a listbox with 2 columns of data but the problem arises when the data is filtered. I can only get the first AREA of the filtered data into the listbox.

Any support on this appreciated as I think I've gone as far as I can for now!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

Big match tonight indeed - I ain't gonna have any fingernails left but...... I have managed to get this to work - don't think itgets much quicker or simpler than this:

Private Sub UserForm_Activate()
Dim listArr() As Variant
ListBox1.Clear
listArr() = Range("A2:B" & Cells(65536, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
ListBox1.List() = listArr
End Sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Loomah]

xlbo
I'd got that far but, as I say, it fails (for me anyway) with a filtered list and only populates the list with the data in the first area of the range and no more.

Soon be time to go - could take up to an hour to get to Stamford Bridge!!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

Missed that - all my filtered values were together initially. Looks like you can't assign a non-contiguous range to an array. Looping'll have to do it but you can at least use an array for part of it....

Option Base 1

Private Sub UserForm_Activate()
Dim listArr() As Variant, lRow As Long, incr As Long
ListBox1.Clear
[COLOR=green]'Find last row[/color]
lRow = Cells(65536, 1).End(xlUp).Row
[COLOR=green]' Re-dimension array[/color]
ReDim listArr(Range("A2:A" & lRow).SpecialCells(xlCellTypeVisible).Count, 2)
incr = 1
[COLOR=green]'loop through visible cells[/color]
For Each c In Range("A2:B" & lRow).SpecialCells(xlCellTypeVisible)
listArr(incr, c.Column) = c.Text
If c.Column = 2 Then incr = incr + 1
Next
[COLOR=green]'Apply array to listbox[/color]
ListBox1.List() = listArr()
End Sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Loomah]

xlbo
Interesting thing with your code is that it doesn't work if used in the Initialize event but is fine as it is. I don't think I'll ever get to grips with these "quirks"! I was a little way to what you have, trying to ReDim (something I've never done before) but still failed.

I tip my cap to you, sir. Worthy of a purple point pip!

Anyway, with a little help (Tom Ogilvy), here's a working version of what I was trying to achieve - for reference.

Private Sub UserForm_Initialize()
Dim cell As Range, rng As Range

With Worksheets(1)
    Set rng = .Range("A2:A" & _
        .Cells(65536, 1).End(xlUp).Row) _
        .SpecialCells(xlCellTypeVisible)
End With

With Me.ListBox1
    .ColumnCount = 2
   For Each cell In rng
    .AddItem cell
    .List(.ListCount - 1, 1) = cell.Offset(0, 1)
   Next
End With
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

PPPs don't seem to be working but I will give one!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

I used my code in the form ACTIVATE event - seemed to work fine

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Loomah]

I acknowlege that fact but it does seem strange that it doesn't work properly in the initialize event!?

That said I must admit to not using many of the form events and have little (none at all!) idea of what some of them are for!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

odd - works for me in the initialize event.....

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Loomah]

I give up!! I can't even begin to think why it works in one for me but not the other! (when I say it doesn't work, what happens is only one column is populated) Nevermind

ho-hum
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

I set up the columncount property (to 2) and the boundcolumn property (to 0) before running the code - it may well be something to do with one or the other of the above

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question