[RESOLVED] Simple search form

[mavt14]

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.

[westconn1]

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

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

[mavt14]

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.

[westconn1]

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

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

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

[mavt14]

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:

'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

[westconn1]

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

try replace this line with

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

not tested, but i think it should be right

pete

[mavt14]

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.