Selected Answer
G1zmos
This answer is based in part on Don's tutorial Filter Data as you Type in Excel
In the revised file attached, this sheet macro launches a UserForm if a single cell in column G of Sheet1 is selected:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'do nothing if more than one cell selected or not in column G
If Target.CountLarge <> 1 Or Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
' otherwise open the form
UserForm1.Show
End Sub
This code sets up the form with a list of files from Sheet2 column C:
Private Sub UserForm_Initialize()
ListBox1.Clear
' add a caption about the selected cell
Label1.Caption = "Select a file to record in cell " & Selection.Address(0, 0)
' put cursor in the search textbox
TextBox1.SetFocus
' create a list of filenames...
With Sheet2
Set filterRange = .Range("C2:C" & .Cells(.Rows.Count, 3).End(xlUp).Row)
End With
' .. by looping through cells
For Each Cl In filterRange.Cells
ListBox1.AddItem CStr(Cl.Value)
Next Cl
End Sub
A textbox is enabled with a wildcard search like *<<entry>>* so you can type any part of the file name (e.g. just 4) and this code filters that list as you type:
Private Sub TextBox1_Change()
Dim filterInput As String, filterRange As Range
filterInput = TextBox1.Text
With Sheet2
Set filterRange = .Range("C2:C" & .Cells(.Rows.Count, 3).End(xlUp).Row)
End With
' filter the range
filterRange.AutoFilter Field:=1, Criteria1:="*" & filterInput & "*", VisibleDropDown:=False
' clear the list and repopulate but stop if nothing is found
ListBox1.Clear
On Error Resume Next
If filterRange.SpecialCells(xlCellTypeVisible) Is Nothing Then
filterRange.AutoFilter
Exit Sub
End If
For Each Cl In filterRange.SpecialCells(xlCellTypeVisible)
ListBox1.AddItem CStr(Cl.Value)
Next Cl
End Sub
If you pick a file from the ListBox, the Submit button turns green and, if you click that, this macro runs:
Private Sub CB_Submit_Click()
'Find the chosen file
For Each Cl In filterRange.SpecialCells(xlCellTypeVisible)
If Cl.Text = ListBox1.Text Then
'if found, write file choice to selected cell (column G -6)
Selection.Value = ListBox1.Text
' add hyperlink to column A
Sheet2.Hyperlinks.Add Anchor:=Selection.Offset(0, -6), _
Address:=Cl.Offset(0, -1).Value & "\" & ListBox1.Text, _
TextToDisplay:=ListBox1.Text
Exit For
End If
Next Cl
' clear the filter and close UF
filterRange.AutoFilter
Unload Me
End Sub
which puts the friendly file name in column G and a hyperlink in column A. A few clicks do it all!
The code to add the hyperlink is in bold above.
Hope this makes sense and works as a solution for you. If so, please remember to mark this Answer as Selected.
