[RESOLVED] Simple search form

Okay, I am new to this VBA thing so forgive me if I seem a little slow.

Basically what I would like to do is make a search form for a spreadsheet in excel. I have the sheet with all of the data and a basic form made. I just need some help with the actual coding.

Currently in the excel file there is 1 sheet with the data, 5 columns worth (EQ#, Spacer, DESC, PATH, LINK). I also have a form with a textbox, a search button, a reset button, and a listbox.

What I would like to happen is to enter a seach string (1-4 numbers or letters) into the textbox on the form, have it search the EQ# column on the sheet, and return any matching values in the listbox (the return value needs to include the EQ#, Spacer, and DESC).

I have further asperations for this form, but this should be enough to keep me thoroghly confused for quite some time.

Thanks for any help that you can give me.

try this code in your button click, change all the names to suit

VB Code:

With Sheet1
    For i = 1 To .UsedRange.Rows.Count
        If .Cells(i, 1) = text1.Text Then
        list1.AddItem .Cells(i, 1) & ", " & .Cells(i, 2) & ", " & .Cells(i, 3)
    Next
End With

pete

Thanks Pete, after some playing I finally got the code to work. However, I now have a new issue.

My excel file has hyperlinks within it. I would like the results of the search to link to the appropriate links.

Example: If you search for "G020", you get the result "140 - G020 - Thermolators.doc" in the list box.

Thermolators.doc corresponds to a path (column D) and link (column E) in row 140. I want to be able to click the item in the list box and have it open the hyperlink found in the appropriate cell.

I am not sure where this code needs to go, on the list box or the button?

If it helps here is the code for the button at this point:
-------------------------------------
Private Sub SearchButton_Click()
Dim i As Integer
Dim SearchText As String
Dim SearchText1 As String
Set Sheet = ActiveWorkbook.Worksheets("Data")

'Set the start Point
i = 2

'Convert all text to uppercase
SearchText = SearchTextBox.Text
SearchText = StrConv(SearchText, 1)
SearchTextBox.Text = SearchText

'Search for a match
For i = 2 To Sheet.UsedRange.Rows.Count
If Sheet.Cells(i, 1) = SearchText Then
ResultsListBox.AddItem (i & " - " & Sheet.Cells(i, 1) & " " & Sheet.Cells(i, 2) & " " & Sheet.Cells(i, 3))
End If
Next

End Sub
---------------------------------


Also, is there a way to make it so that only part of the item needs to be entered into the search field? Ex.: enter G and get all EQ#'s starting or containing the letter G?

Thanks for your help.

last first,
change this line If Sheet.Cells(i, 1) = SearchText Then to

VB Code:

If left(Sheet.Cells(i, 1), Len(searchText.Text)) = SearchText Then

is Thermolators.doc an actual hyperlink, so if you click on it it opens the document?
if so try this, in your list box click event, but i haven't tested it

VB Code:

listi = Left(List1.Text, InStr(List1.Text, " ") - 1)
    Range.Cells(listi, 3).Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Application.WindowState = xlNormal

if it is not a hyperlink, you will need to read the path and file name from the cells then use shellexecute to open the file in an appropriate program


pete

Pete,
Thanks for the help. I could not get the second part to work at all, kept getting an error, but I ended up finding a line of code in one of the other threads and combining it with the code you gave me and that seemed to work. Thanks again for your help.
______________________________________________

I think there is just one more issue and the whole thing will work perfectly. The issue is still with the search function.

I have 3 search fields on my form: Equipment number, description, and department. The equipment number and department search work perfectly, but the description search will only return a value if the string starts with the entered value. Example: if I am trying to find BO_60.doc and search for "BO" I can find it because the string starts with "BO", bit if I search "60" I get nothing. Is there an easy way toi fix this?

here is the code I have for this search:

VB Code:

'Search By Description
If SearchVar = 2 Then
 
'Convert all text to uppercase
SearchText = DescTextBox.Text
SearchText = StrConv(SearchText, 1)
DescTextBox.Text = SearchText
TextLabel = SearchText
 
    If DescTextBox.Text = "" Then
        response = MsgBox("Please Enter A Description.", 0, "No Description")
    Else
        'Search for a match
        For row = 2 To Sheet.UsedRange.Rows.Count
        
            If Left(Sheet.Cells(row, 3), Len(TextLabel)) = SearchText Then
                ResultsListBox.AddItem (Sheet.Cells(row, 1) & " - " & Sheet.Cells(row, 2) & " - " & Sheet.Cells(row, 3) & " - " & row)
            End If
        Next
        End If
End If

Quote:

---

If Left(Sheet.Cells(row, 3), Len(TextLabel)) = SearchText Then

---

try replace this line with

VB Code:

if not instr(sheet.cells(row,3), searchtext) = 0  then

not tested, but i think it should be right

pete

AWSOME! Thanks pete. That worked perfectly. I just completed the final product and I think it will work quite nicely. So...once again thanks for all your help.