Greetings to the Excel community.
I am trying to develop my workbook design for my employers. I am advanced with formulas but have just started VBA last week and have a book arriving tmrw to help me.
So far i have already added a search function using a command button and text box to each worksheet and a toggle button to find and hide/show rows users dont need automatically with great success
I am currently adapting code from the internet and only writing small bits here and there as my knowledge expands.
Here is the code i have adapted so far. Its not working correctly and i cant seem to figure it out. The command button and textbox will sit on the first worksheet and needs to search for a name (example: John) in the workbook and either display which sheet the name is found on or jump in turn to each name until you reach the correct sheet.
Do i need to set a range for the variable (ws)?
Many Thanks for any input.
Private Sub CommandButton1_Click()
Dim search As String
Dim ws As Worksheet
Dim Answer As String
search = TextBox1.Text
On Error GoTo ErrorMessage
Application.EnableEvents = True
For Each ws In ActiveWorkbook.Worksheets
Cells.Find(What:=search, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select
If ActiveCell.Row Then
Answer = MsgBox("Found at " & ActiveSheet.Name & " Continue searching ? ", vbYesNo)
If Answer = vbNo Then Exit Sub
ErrorMessage:
MsgBox ("Not Found")
End If
Next ws
End Sub
I am trying to develop my workbook design for my employers. I am advanced with formulas but have just started VBA last week and have a book arriving tmrw to help me.
So far i have already added a search function using a command button and text box to each worksheet and a toggle button to find and hide/show rows users dont need automatically with great success
I am currently adapting code from the internet and only writing small bits here and there as my knowledge expands.
Here is the code i have adapted so far. Its not working correctly and i cant seem to figure it out. The command button and textbox will sit on the first worksheet and needs to search for a name (example: John) in the workbook and either display which sheet the name is found on or jump in turn to each name until you reach the correct sheet.
Do i need to set a range for the variable (ws)?
Many Thanks for any input.
Private Sub CommandButton1_Click()
Dim search As String
Dim ws As Worksheet
Dim Answer As String
search = TextBox1.Text
On Error GoTo ErrorMessage
Application.EnableEvents = True
For Each ws In ActiveWorkbook.Worksheets
Cells.Find(What:=search, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select
If ActiveCell.Row Then
Answer = MsgBox("Found at " & ActiveSheet.Name & " Continue searching ? ", vbYesNo)
If Answer = vbNo Then Exit Sub
ErrorMessage:
MsgBox ("Not Found")
End If
Next ws
End Sub