Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RowSource for a ListBox (Excel XP/2000)

    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

  2. #2
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RowSource for a ListBox (Excel XP/2000)

    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
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

  3. #3
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RowSource for a ListBox (Excel XP/2000)

    You would have to use AddItem instead of RowSource

    i.e.

    Dim x as Long
    Dim LastRow as Long

    LastRow = Range(

  4. #4
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RowSource for a ListBox (Excel XP/2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •