Need a userform to do a search and....

bazofio,

Welcome to the forum!
How does the code know which cells to search (for example, I'm guessing you want to look in more than just A10:F10)? And is it only searching those cells for exact matches? Will the average always be 1 cell above the found cell and will the record always be 1 cell below the found cell? Will there ever be a time when there is more than one found cell containing the searched number?

bazofio,

Attached is an example workbook based on the criteria you described.
Here is the full userform code:

Private Sub btnCancel_Click()
    
    Unload Me
    
End Sub

Private Sub btnFind_Click()
    
    Dim rngFound As Range
    
    Me.txtFind.SetFocus
    If Len(Me.txtFind.Text) = 0 Then
        MsgBox "Must provide search criteria", , "Find Error"
        Exit Sub
    End If
    
    Set rngFound = Sheets("Sheet1").Range("A10:F10").Find(Me.txtFind.Text, , xlValues, xlWhole)
    If Not rngFound Is Nothing Then
        MsgBox Title:="Match Found", _
               Prompt:="Searched for:" & vbTab & Me.txtFind.Text & Chr(10) & _
                       "Average:" & vbTab & vbTab & rngFound.Offset(-1).Text & Chr(10) & _
                       "Record:" & vbTab & vbTab & rngFound.Offset(1).Text
    Else
        MsgBox "No matches found for [" & Me.txtFind.Text & "]", , "No Matches"
    End If
    
    Set rngFound = Nothing
    
End Sub

You're very welcome

bazofio,

It is possible, just make sure you have a label for the message text, and an OK button that the user can click on to close the form (really they'll be hiding it).
Assuming your messaging userform is named "frmMessage", you would use this code during your main userform's initialize event:

Private Sub UserForm_Initialize()
    
    Load frmMessage
    
End Sub

Then, in place of the MsgBox code, you would use this:

    frmMessage.Caption = "Title"
    frmMessage.label1.Caption = "This is the main message"
    frmMessage.Show

When the user clicks the OK button on frmMessage, its code should be Me.Hide instead of Unload Me.

Lastly, you should also add a terminate event to your main finding userform that will unload the frmMessage userform:

Private Sub UserForm_Terminate()
    
    Unload frmMessage
    Unload Me
    
End Sub

Yeah, it would basically be the same code as the msgbox text

Ok im here again with the last question, i hope.
As i go on with work i see that there were same things that will work great in my worksheet, but i just cant do it. Tiger can you please help with a code, the same one you write to me, but with a option of a listbox or a dropbox in the userform that when i select different names in that listbox/dropbox the userform looks for those values that are in the original code but in different places, i mean in a different sheet with different range. Because maybe i have 14 to 16 sheets where i could use this userform to search for those values (record, average) and it wasnt necessary to make the original code for each sheet.

Something like this should work for you...

    Dim ws As Worksheet
    
    If Me.ListBox1.ListIndex = -1 Then Exit Sub 'Nothing selected in listbox
    Select Case Me.ListBox1.ListIndex
        Case 0: Set ws = Sheets("Sheet1")   '1st item in listbox is selected
        Case 1: Set ws = Sheets("Sheet2")   '2nd item in listbox is selected
        Case 2: Set ws = Sheets("Sheet3")   '3rd item in listbox is selected
        'and so on
    End Select
    
    Set rngFound = ws.Cells.Find....

And where do i put that code? Attached to the dropdown list or with the userform created?

No, it goes in with the OK/Find button's code

Sorry tiger but im completely lost here, cant figure it out how to do this. Im really noob at this things :-(

Bazofio,

Attached is a new version of the sample workbook.
I have added a listbox to the userform that is populated with the names of the worksheets.
Each worksheet needs to search a different range, and the designated range to search is determined by the listbox's selection.
Here is the full userform code:

Private Sub btnCancel_Click()
    
    Unload Me
    
End Sub

Private Sub btnFind_Click()
    
    Dim rngFound As Range
    Dim rngSearch As Range
    
    Me.txtFind.SetFocus
    If Len(Me.txtFind.Text) = 0 Then
        MsgBox "Must provide search criteria", , "Find Error"
        Exit Sub
    End If
    
    If Me.listSheets.ListIndex = -1 Then
        Me.listSheets.SetFocus
        MsgBox "Must select a sheet to search", , "Find Error"
        Exit Sub
    End If
    
    Select Case Me.listSheets.ListIndex
        Case 0: Set rngSearch = Sheets("Sheet1").Range("A10:F10")
        Case 1: Set rngSearch = Sheets("Sheet2").Range("A12:F12")
        Case 2: Set rngSearch = Sheets("Sheet3").Range("A13:J13")
        Case 3: Set rngSearch = Sheets("Sheet4").Range("B20:G20")
        Case 4: Set rngSearch = Sheets("Sheet5").Range("C15:I15")
    End Select
    
    Set rngFound = rngSearch.Find(Me.txtFind.Text, , xlValues, xlWhole)
    If Not rngFound Is Nothing Then
        MsgBox Title:="Match Found", _
               Prompt:="Searched for:" & vbTab & Me.txtFind.Text & Chr(10) & _
                       "Average:" & vbTab & vbTab & rngFound.Offset(-1).Text & Chr(10) & _
                       "Record:" & vbTab & vbTab & rngFound.Offset(1).Text
    Else
        MsgBox "No matches found for [" & Me.txtFind.Text & "] on sheet '" & rngSearch.Parent.Name & "'", , "No Matches"
    End If
    
    Set rngFound = Nothing
    
End Sub

Private Sub UserForm_Initialize()
    
    Dim i As Long
    Dim strSheets As String
    
    For i = 1 To Sheets.Count
        strSheets = strSheets & ":" & Sheets(i).Name
    Next i
    
    Me.listSheets.List = Split(Mid(strSheets, 2), ":")
    
End Sub

Sorry giving you so much trouble. I adapt your code to my worksheet and itīs working, but i dont know how to change the sheets that i want to use for search. There are all of them in the list box but i want to select the ones that should display. Where do i change that tiger?

Edit: Forget it, i found it. Its in the sheets count, change the number to where it starts counting ;-)

Many thanks tigerland for helping a big noob.