Filtered List as Listbox RowSource

[Ken McLennan]

G'day there once again, One & All,

I'm currently trying to finish off a userform which has a
multicolumn ListBox that has a dynamic range as a rowsource. I've
managed to get most of it working the way I want it too, my columns
display as intended, and sort themselves according to selections made
via optionbuttons. I even have a sort option for ascending & descending
order.

What I want to do is give my users the option of displaying data
that only belongs to a user defined category. Eg, click the "OSA" option
button and the listbox will display only those employees who are paid
Operational Shift Allowance, etc.

I tried several variations of Autofilter before looking on the net
& finding that filtering the list has no effect (something I'd already
seen for myself). I then had a few goes at using combinations of
range.rows.hidden, and another method I can't recall at the moment, but
which had a criteria of xlcelltype = visible.

Having no success there, I tried copying the visible data from my
range, and then pasting it to a different location. The problem then was
that I couldn't (and still can't) reset the ListBox's rowsource range.
It gives me a 1004 error when I try to point it at the copied/pasted
data.

I've not tried the .additem method because I've set the rowsource
earlier in the code and "Help" tells me that I can't use it when the
rowsource is assigned.

I've no doubt this has occurred previously and there has to be a
workaround, but I can't find it. Google searches with various criteria
bring me plenty of hits, but nothing that I've found useful so far.

Can any one please offer advice, or a site or reference that might
explain house it's done?

Thank you once again,

 

[Dave Peterson]

Try setting the rowsource to "". Then use .additem.

Me.ListBox1.RowSource = ""

This may give you an idea (or maybe not!):

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim wks As Worksheet
Dim RngF As Range
Dim RngV As Range
Dim myCell As Range

Set wks = Worksheets("Sheet1")

With wks
Set RngF = .AutoFilter.Range
If .FilterMode = False Then
Me.ListBox1.RowSource _
= RngF.Resize(RngF.Rows.Count - 1, 1).Offset(1, 0) _
.Address(external:=True)
Else
Me.ListBox1.RowSource = ""
If RngF.Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
.Cells.Count = 1 Then
'no visible cells except the header
'what should be done
Else
With RngF
Set RngV = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With
For Each myCell In RngV.Cells
Me.ListBox1.AddItem myCell.Offset(0, 1).Value
Next myCell
End If
End If
End With

End Sub

 

[Ken McLennan]

G'day there Dave,

Try setting the rowsource to "". Then use .additem.

Me.ListBox1.RowSource = ""

This may give you an idea (or maybe not!):

That it did. I've managed to get the rowsource = "" bit working
correctly, not that this was the difficult part, but the rest of it
hated me.

I've not really had much of a chance to play with it yet, but I
think my problem was that I was trying to be too clever and missed the
"Set" command, so that my references to the range were out of whack
right from the start. I'll (hopefully) have another opportunity to work
with it tonight. I'll let you know how I go.

Thanks very much for your ideas. I really appreciate your help.

 

[Ken McLennan]

G'day there Dave,

Try setting the rowsource to "". Then use .additem.

Me.ListBox1.RowSource = ""

This may give you an idea (or maybe not!):

Thanks again for your help. I reviewed my code & rechecked where I
was using "set", but no matter what I looked at I couldn't find what was
going wrong.

After thinking about it for a bit (it made my head hurt) I decided
to adopt another approach. Because my data is in a list, and hence
structured, I now use an offset to check the contents of the appropriate
field:

If myCell.Cells(1, 1).Offset(0, 3).Text = "TRUE" Then

I have a "For each" structure to check each row in my range, and
the results are fed into a string array. I then display this array in my
listbox:

Me.ListBox2.RowSource = ""
If Not found Then
Me.ListBox2.Clear
Exit Sub
End If
Me.ListBox2.Column = gStr1

You may recognise the RowSource = "" part from your suggestion. I
hope you've not copyrighted it =)

However, I now can't recall why the ListBox2.Clear is in there.
I'll have to go and check.

Thanks once again, I really do appreciate the assistance I get.

See ya

 

[Dave Peterson]

If you added the .rowsource manually (while you were creating the form), this
line just makes sure that "tie" is broken.

Me.ListBox2.RowSource = ""

And this line
me.listbox2.clear
removes any entries in that listbox.

(You can't clear a listbox that is tied back to a rowsource--so it's just a
precautionary measure.)

 

[Ken McLennan]

G'day there Dave,

If you added the .rowsource manually (while you were creating the form), this
line just makes sure that "tie" is broken.

Me.ListBox2.RowSource = ""

And this line
me.listbox2.clear
removes any entries in that listbox.

(You can't clear a listbox that is tied back to a rowsource--so it's just a
precautionary measure.)

Bingo!!

It's a good thing that one of us knows what they're doing!! <g>

You're quite right, of course. If I recall correctly, I got those
lines of code from one of John Walkenbach's books.

My entire project appears to consist of bits and pieces from
experts cobbled together with my tinkerings. Still, I like to think I'm
learning =).

Thankyou once again, Dave.

 

[Dave Peterson]

Someday, you may look back and wonder why you approached a problem in a specific
way--you may want to add a comment here or there.

'This seems to work, I have no idea why...


'Added on Sept 20, 2005 to fix...



Just a small reminder of what you did.

 

[Ken McLennan]

G'day there Dave,

Someday, you may look back and wonder why you approached a problem in a specific
way--you may want to add a comment here or there.

'This seems to work, I have no idea why...

'Added on Sept 20, 2005 to fix...

Just a small reminder of what you did.

Now THERE'S a good idea! I will most certainly note my code
with that type of comment. It won't necessarily stay that way though.

If/when I get this thing up & running the chances are it will be
maintained by someone from another section. Therefore it'll need as many
notes and explanations as I can give it. If I can't remember why
something is there, then I can't really expect someone else to figure it
out.

But then again, by then it won't be my problem =)

See ya

 

[Dave Peterson]

But if you give away too many secrets, there goes your job security!

 

[Ken McLennan]

G'day there Dave,

But if you give away too many secrets, there goes your job security!

Heeheeheechuckle. I work for... Oops...

I'm "employed by" our state government. By definition I therefore
don't work and if they've not found out yet then I'm probably secure
already =)

See ya