Filter a listbox (97 SR2)

[Kel]

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?

[Andrew Cronnolly]

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

[Kel]

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!

[Andrew Cronnolly]

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

[Kel]

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?

[LegareColeman]

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