check it out.. i know you were asking for something a little different. i acknowledged your code, but don't have time now to try it cuz i am in a rush. i worked on this last night. the main module code has changed. update it.. then follow directions afterwards to create a listbox.
i just editted this post today (4/27) to help explain what certain parts of the code do and what you will need to edit to suit your project
Sub goopy()
Dim fArray() As String
Dim sString As String, wbkPath As String
Dim find1st As Range, curRange As Range, fRange As Range
Dim fCount As Long
wbkArray = Array("a.xls", "b.xls") 'see explanation #5
wbkPath = "c:\testproject\"
sString = InputBox("Enter Search")
If Trim(sString) = "" Then
MsgBox "Enter something to search for!"
Exit Sub
End If
For i = 0 To UBound(wbkArray)
Workbooks.Open (wbkPath & wbkArray(i))
Set fRange = Workbooks(wbkArray(i)).Sheets(1).Cells
Set find1st = fRange.Find(sString, LookIn:=xlFormulas, lookat:=xlWhole) '#6
Set curRange = find1st
If Not curRange Is Nothing Then
Do
fCount = fCount + 1
ReDim Preserve fArray(1 To 4, 1 To fCount) 'see explanation #1
fArray(1, fCount) = wbkArray(i)
fArray(2, fCount) = fRange(curRange.Row, 1).Value 'explanation #2a
fArray(3, fCount) = fRange(1, curRange.Column).Value '#2b
fArray(4, fCount) = Replace(curRange.Address, "$", "")
Set curRange = fRange.FindNext(after:=curRange)
Loop Until curRange Is Nothing Or curRange.Address = find1st.Address
End If
Workbooks(wbkArray(i)).Close '#3
Next i
If fCount > 0 Then
UserForm1.ListBox1.ColumnCount = 4 '#4
UserForm1.ListBox1.Column = fArray
UserForm1.Show
End If
End Sub
now create a form with a listbox. change the columncount property to 4. in the listbox1_change event add this code
Private Sub ListBox1_Change()
Dim tString As String
For i = 1 To Workbooks.Count
tString = tString & Workbooks(i).Name & ","
Next i
If InStr(tString, ListBox1.Column(0)) = 0 Then _
Workbooks.Open ("c:\testproject\" & ListBox1.Column(0))
Workbooks(ListBox1.Column(0)).Activate
Rows(Range(ListBox1.Column(3)).Row).Select
End Sub
it works real nice. every time you click a record, it opens the specified workbook if it is not already open, switches to it, and selects the proper row. the listbox displays the specific location where the data was found so you can home in on the record you want. pretty swift if i do say so myself =).
explanations
- this line builds a 2D dynamic array that acquires all the found data that will eventually populate listbox1. the integer 4 represents the number of possible record categories that data could be found in.
- these two lines record the location of found data. the integer value 1 for 2a represents the column where your name headings are located (ie, john, mary, etc). 2b is the row location where your record heads are (ie, name. hair color). change these to match the layout of your own workbooks.
- this line closes the workbooks after the program is finished scanning records. they will later be opened with the listbox event. delete this line if you want all workbooks to remain open and it will not change the process.
- this line defines the number of columns in the listbox, one column for each possible record. make sure this integer is the same as the one defined in expl. #1.
- this line creates an array of all workbooks you want to search. the process is dynamic, so feel free to add string values defining names of other workbooks you want included in the overall inquiry.
- the constant xlWhole will only return a successful search on an item in a cell if the ultimate calulated value is equal to the search string. searching for "cdefg" will fail against a cell containing "abcdefg". if you want to be able to find "cdefg" within "abcdefg" then change the constant to xlPart.