RowSource for a ListBox (Excel XP/2000)

Topic

New Reply

I have a listbox on a user form that currently lists all employees. The RowSource for the box is a named range on a hidden sheet in the template called EmpSelect. It works fine, but there are nearly 500 employees. So I’d like to be able to display only those employees in a specific office and/or with a specific job title. I can include the respective codes for those two items in the EmpSelect range, but I don’t know how to (or if I can) limit the choices displayed in the listbox based on that kind of criteria (probably selected from a combo box). I’d know how to do it in Access, and I could do it in Excel if my data were in Access, but if I leave the data in Excel, is there a way in VBA to say, “Give me all the rows in EmpSelect where Office equals 5200 and/or Title equals 358”? I tried searching for an example of someone doing this, but I didn’t even know what to search for. What I did find all had to do with Access.

I was thinking that maybe there would be a way to apply filters to the data through code and then have the named range somehow dynamically resolve to only visible cells. Or something like that.

Many thanks for a point in the right direction!

–Karyl

Reply | Quote

Replies

I’ve done this before.
Just a suggestion. Go to Tools>Macro Record macro.
Go to your data page. Go to Data>Filter Auto filter.
Make your selections and then stop the recording.
You’ll have some code that can be used to solve your problem

Reply | Quote

I can filter the data okay. I just don’t know how to get the filtered results to fill my list box, since the other values aren’t gone, just not visible. All of the entries still show up in the list box when the filter is applied. Do you know how to grab just the visible cells? Oh, I just had a flash of an idea of how I might do it. I’ll be back if it works. . .

Well, the idea worked, it just DIDN’T work the way I wanted it to. I limited the list, then selected the original named range. From there, I then selected only the visible cells within that range and gave THAT range a new name. And that worked. My new named range included only the people I wanted, even when the filters were turned off. But when I tried to use THAT named range as the row source for the list box, it came up empty, even though it should have had three names. It didn’t matter if the filters were on or off. So, back to the drawing board. . .

–Karyl

Reply | Quote

I can filter the data okay. I just don’t know how to get the filtered results to fill my list box, since the other values aren’t gone, just not visible. All of the entries still show up in the list box when the filter is applied. Do you know how to grab just the visible cells? Oh, I just had a flash of an idea of how I might do it. I’ll be back if it works. . .

Well, the idea worked, it just DIDN’T work the way I wanted it to. I limited the list, then selected the original named range. From there, I then selected only the visible cells within that range and gave THAT range a new name. And that worked. My new named range included only the people I wanted, even when the filters were turned off. But when I tried to use THAT named range as the row source for the list box, it came up empty, even though it should have had three names. It didn’t matter if the filters were on or off. So, back to the drawing board. . .

–Karyl

Reply | Quote

I’ve done this before.
Just a suggestion. Go to Tools>Macro Record macro.
Go to your data page. Go to Data>Filter Auto filter.
Make your selections and then stop the recording.
You’ll have some code that can be used to solve your problem

Reply | Quote

You would have to use AddItem instead of RowSource

i.e.

Dim x as Long
Dim LastRow as Long

LastRow = Range(

Reply | Quote

You would have to use AddItem instead of RowSource

i.e.

Dim x as Long
Dim LastRow as Long

LastRow = Range(

Reply | Quote