Search a Userform Listbox for matching text from another Listbox

[rrenis]

Hi - I have the following Code I found hunting on google which I thought would allow me to search the results listed in ListBox2 based on the Selection in ListBox1.

The contents of ListBox1 is a list of files in a particular folder which have been truncated so as not to show the folder path. This is due to the long length of the folder names and sub folders.

I therefore added the full path and file names to ListBox2 (which I intend to hide using ListBox2.Visible = False once the userform is working) so that I could call on this to return the full path and filename of the selected file in ListBox1. The reason for this is that I want to launch the selected file and therefore need the full path along with the filename.

Here's the code I have so far but it's not working. I'm sure I'm doing something daft - or maybe someone can suggest a better way to acheive what I'm looking for?

[vba]
Private Declare Function ShellExecute _
Lib "shell32.dll" _
Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim strFile As String
Dim strAction as String
Dim lngErr As Long

strFile = FindStringinListControl(ListBox2, ListBox1.Value)
strAction = "OPEN"
lngErr = ShellExecute(0, strAction, strFile, "", "", 0)

End Sub


Public Function FindStringinListControl(ListControl As Object, _
ByVal SearchText As String) As Long

'Input:
'ListControl: List or ComboBox Object
'SearchText: String to Search For
'Returns: ListIndex of Item if found
'or -1 if not found

Dim lHwnd As Long
Dim lMsg As Long
On Error Resume Next
lHwnd = ListControl.hWND
If TypeOf ListControl Is ListBox Then
lMsg = LB_FINDSTRINGEXACT
ElseIf TypeOf ListControl Is ComboBox Then
lMsg = CB_FINDSTRINGEXACT
Else
FindStringinListControl = -1
Exit Function
End If
FindStringinListControl = SendMessagebyString _
(lHwnd, lMsg, -1, SearchText)
End Function[/vba]

Thanks for looking!

Cheers,
rrenis

[Oorang]

Hi rrenis,
There may be an easier way to do this. What I like to do is load the full path into column one of the list box and then set column 1 width to 0 so it cannot be seen. At the same time I load just the file name into column 2. The only thing the user sees is Column 2. Then I put a label (or textbox) underneath (or above) the list box, and in the selection change event I change the caption of the label to the value of the listbox's selected item column 1.
The net effect is that the filename is listed in the listbox, and the path of the selected item is listed underneath.

[rrenis]

Hi Oorang

Thanks for the reply. Unfortunately I'm not sure I can use this method as the userform will also be used in Outlook to open up files and I don't think the Selection Change event will be available??

Cheers,
rrenis

[tstom]

You could associate an array with the ListIndex. Create a userform with ListBox1 and paste in this code...

[vba]
Private Paths(2) As String

Private Sub ListBox1_MouseUp(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
MsgBox Paths(ListBox1.ListIndex)
End Sub

Private Sub UserForm_Initialize()

ListBox1.AddItem "File1"
Paths(ListBox1.ListCount - 1) = "c:\ParentFolder\File1.pdf"

ListBox1.AddItem "File2"
Paths(ListBox1.ListCount - 1) = "c:\ParentFolder\File2.pdf"

ListBox1.AddItem "File3"
Paths(ListBox1.ListCount - 1) = "c:\ParentFolder\File3.pdf"

End Sub
[/vba]

[rrenis]

Thanks tstom - that works perfectly!!

Cheers,
rrenis