Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter a listbox (97 SR2)

    My listbox is on a userform, and represents the files in a given directory. Is there any way to filter this list based on another control in the form? Or perhaps a hard-coded criteria? Each file in this directory is saved using a specific naming schema, uname-mm-dd-yyyy.xls. I would like to filter this by username, allowing the user to see only thier files.

    Any ideas?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Filter a listbox (97 SR2)

    When populating the listbox, could you just select the files for the appropriate user.
    What code are you using to fill the listbox ?

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter a listbox (97 SR2)

    Now that you mention it, I'm also looking for a way to filter my listbox based on a column of data it will eventually represent, for example, one of the columns in my spreadsheet is Date. the listbox will also have a column for date, is there a way to filter the contents of the listbox based on this?

    The following code is populating my listbox:
    <pre>Sub Recovery()
    Dim MyPath, MyName
    MyPath = "k:mydir"
    MyName = Dir(MyPath, vbDirectory)
    Do While MyName <> ""
    If MyName <> "." And MyName <> ".." Then
    'If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
    lbRecovery.AddItem MyName
    'End If
    End If
    MyName = Dir
    Loop
    End Sub</pre>


    Then, all i'm doing is using a workbook.open event to open the value of the listbox, and do something to the opened file.

    So, an ideal scenario is that, since the file only exists on the K: drive IF they entered call data on that date, the date value selected in the list box would be used to filter another listbox, one which lists specific details for that date.

    Lost yet? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Filter a listbox (97 SR2)

    Before adding the file MyName to lbRecovery, test the file name to see if the curent user name matches that used in the filename. If your naming convention calls for a 5 digit uname, you could use <pre>If Left(MyName,5) = uname ' i.e. the current user name</pre>

    etc.. If the uname is variable instead of 5 use Len(uname). Have you considered allocating a sub folder for each user, and just selecting files from the appropriate folder for each user.

    A similar approach (not a seperate folder) could be adopted to filter by date as that also is part of your naming convention, and so you could test for a match.

    Hope that helps

    Andrew

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter a listbox (97 SR2)

    Thanks, I think this will help alot.

    Now, how can I filter the contents of my listbox if they reflect my spreadsheet, and I only want to populate it with rows which represent a specific date?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter a listbox (97 SR2)

    Don't you have code loading the listbox? That code should be able to put only what you program it to into the listbox.
    Legare Coleman

Posting Permissions

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