So this is a fairly involved process. Not difficult, but requires a few steps. First you'll need to design your Userform; everything starts from there. So once you've opened VBA Explorer, you'll locate the workbook you wish to have the UserForm in via the Project Window then Insert your Userform. This will insert a completely blank form that is then up to you to Customize using the Toolbox. This will bring us to your 1st question; searching for a value using VBA. From the Toolbox you can insert a button on to the form. Once you have your first button on the form you can then start attaching code to that button an label it (I labeled is Search). So for example, you may have something like this:
Code:
Private Sub CommandButton1_Click()
Dim SearchString As Variant
SearchString = Application.InputBox("Find Value", "Find")
If SearchString = False Then
MsgBox ("You Have Pressed Cancel or Not Inserted a Value")
Exit Sub
End If
End Sub
This would result in the Input Box being opened when you clicked this button on the Userfrom. Then whatever you input would be saved as the variable SearchString. If no value is input then it will exit the Userform and display a message informing you that you've cancelled the input box.
On a side note. I would also insert a button on your document that actually calls the UserForm. This will be handy when you may want to test that something is working correctly as you design your Userform and implement the code to control the various buttons and outcomes. Later once you've finished the Userform and confident it works as you'd like you can attach the Userform to a Worksheet_Open Event and have the Userform called each time the document is opened or you could leave it as a button on the Sheet to use when needed; choice is yours based on the functionality you need.
Now you've prompted the InputBox, managed to type in the word, phrase or value you're looking for, now how do you find it? So VBA has a Find command that will allow you to search a given Range. This code would look something like this. I wrote it as a loop assuming you have multiple sheets and that the value you're searching for is located in Column A. This will search all sheets for the Value you place in the Input Box. If you didn't want to search all the sheets then there are ways of specifying which sheet to search only. Also the Find function has many optional parameters so it can be customized. As it sits now it will return values that have partial matches to the value you input. (I.E. If you input 14 it could potentially return 140, or HAT could return THAT). This can be adjusted though. For the sake of ease I'll leave it as is and we can cross that bridge as the time comes.
Code:
For Sheet = 1 To Sheets.Count Step 1
Sheets(Sheet).Range("A:A").Find (SearchString)
Next Sheet
So this will located the value you're looking for. This is where I took a few liberties with you were looking for. I added a few more TextBoxes on your Userform to provide more information. So here are the next few lines of code I added to your Search Button. In addition to these lines I added 5 TextBoxes to your Userform.
Code:
FoundRow = Range("A:A").Find(SearchString).Row
UserForm1.TextBox1.Value = SearchString
UserForm1.TextBox2.Value = Range("A:A").Find(SearchString).Address
UserForm1.TextBox3.Value = Range("F" & FoundRow).Value
UserForm1.TextBox4.Value = Range("E" & FoundRow).Value
UserForm1.TextBox5.Value = Range("D" & FoundRow).Value
So The first line sets the Row Number of the found phrase you were searching for, I'll use this row number later to find the additional information from the other Columns on that row. For Now, the first TextBox I added displays the phrase I original searched for, so I can remind myself and see if I made a error. The next TextBox displays the Cell Address of the cell that was found to match the phrase I was searching for. The following 3 TextBoxes return values for the Row where we found the value we were looking for. So for example if we searched a phrase and found it in Column A on Row 47, the FoundRow = 47, and thus the remaing 3 Textboxes would return the Values found in F47, E47 and D47. This would then be seen on your Userform in each TextBox. After that I added a line of code to display those same results as a Message Box. It is as Follows:
Code:
MsgBox "Column D Value is " & Range("D" & FoundRow).Value _
& vbNewLine & "Column E Value is " & Range("E" & FoundRow).Value _
& vbNewLine & "Column F Value is " & Range("F" & FoundRow).Value
This will display 3 sentences on separate lines within the same Message Box. Now I realize this isn't exactly what you were looking for but it's a rough example of the tools you would need to complete your task. You can do calculations directly in your VBA code and have those values returned as needed or populated into a TextBox. The last piece of code I wrote was the exit Userform Button. Place another button on your userform with the attached code and this will close the Userform when you've finished using it, it will also clear any values from prior usage.
Code:
Private Sub CommandButton2_Click()
UserForm1.TextBox1.Value = ""
UserForm1.TextBox2.Value = ""
UserForm1.TextBox3.Value = ""
UserForm1.TextBox4.Value = ""
UserForm1.TextBox5.Value = ""
UserForm1.Hide
End Sub
Hopefully this is a decent start at what you're looking to accomplish or is a starting guideline that you can use to get you to your end results. If you have any questions please feel free to ask I will try to answer them as I can and to the best of my ability.
