steimel386
New Member
- Joined
- Jan 8, 2009
- Messages
- 33
I'm trying to create a macro that is essentially the search function in Windows, but only goes through the current directory and it's subfolders. I have this, and the code is as listed below (the module). Then, I have a userform that comes up with a list of the files that the string was found in (works). What I want to do is have the ability to click on the name of the file and open the workbook.
Currently I can open the workbook, but only if I manually put in the search term (rather than MyValue) in the UserForm sub procedure. Is there a way around this? Any help is much appreciated.
The module in the workbook:
The code in the Userform:
Currently I can open the workbook, but only if I manually put in the search term (rather than MyValue) in the UserForm sub procedure. Is there a way around this? Any help is much appreciated.
The module in the workbook:
Code:
Sub FindFiles()
MyValue = InputBox("This macro will only search the Excel files" & vbCrLf _
& "in this directory and the folders within it. " & vbCrLf _
& "" & vbCrLf _
& "Enter your search criteria below:", "Search Criteria", "Search")
With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path
.SearchSubFolders = True
.TextOrProperty = MyValue
.MatchTextExactly = False
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
UserForm1.ListBox1.AddItem .FoundFiles(i)
Next i
End With
UserForm1.Show
End Sub
Code:
Private Sub ListBox1_Click()
Workbooks.Open Filename:=ListBox1.Text
Set c = .Find("alexsteimel", LookIn:=xlValues)
'I don't want to use "alexsteimel", I want the variable "MyValue"
'from above to automatically be used. Also, is there a way to
'to automatically activate the cell that has MyValue in it?
End Sub