Hello,
I have a userform1 that allows users to search :
Data on sheet2
and results on sheet1
userform1.PNGData.PNGResults.PNG
End user inputs 142 and search, this should display results in listbox that have all matching title : 142. with the total of 7 columns
The end user would be able to select the correct one and add the results with a click of a button to sheet1 for print.
Curently my code consist of msgbox but the clicking is not helpful.
Sub Button1_Click()
Dim titleSearch As String
Dim returnValue As String
Dim rowVal As Long
tryagain:
titleSearch = InputBox("Please Enter a Title")
If Len(titleSearch) = 0 Then
MsgBox ("Please Enter a Company Name"), vbCritical, ("Company Required")
Exit Sub
End If
With Sheets("Data")
For x = 1 To .Cells(Rows.count, "A").End(xlUp).Row Step 1
If .Cells(x, 1) = titleSearch Then
returnValue = _
"Title: " & vbTab & .Cells(x, 1) & vbNewLine & _
"Co: " & vbTab & .Cells(x, 2) & vbNewLine & _
"Name: " & vbTab & .Cells(x, 3) & vbNewLine & _
"Address: " & vbTab & .Cells(x, 4) & vbNewLine & _
"City: " & vbTab & .Cells(x, 5) & vbNewLine & _
"State: " & vbTab & .Cells(x, 6) & vbNewLine & _
"Zip: " & vbTab & .Cells(x, 7)
Select Case MsgBox("Match Found! Is this the correct address?" & _
vbNewLine & returnValue, vbYesNo)
Case vbYes
rowVal = x
GoTo matchFound
Case vbNo
End Select
End If
Next x
RecordResponse = MsgBox("End of Title Search. Would you like to add?", vbYesNo + vbCritical)
If RecordResponse = vbYes Then
UserForm2.Show
Exit Sub
Else
Exit Sub
End If
matchFound:
Sheets("Output").Range("B8") = .Cells(rowVal, 2).Value
Sheets("Output").Range("B9") = .Cells(rowVal, 3).Value
Sheets("Output").Range("B10") = .Cells(rowVal, 4).Value
Sheets("Output").Range("B11") = .Cells(rowVal, 5).Value & " " & .Cells(rowVal, 6) & _
", " & .Cells(rowVal, 7)
End With
End Sub
Bookmarks