Search for Work Sheet Names in Excel using Visual Basic Macro

While working with Visual Basic in Excel many times it is required to find a certain worksheet by its name and/or activate the worksheet using VB. Here is the solution for how to find a worksheet by its name using Visual Basic for Applications in Excel.
First make a User Form in Visual Basic Editor, and add a command button on this form.

user form in excel vba
Name the command button to SearchSheets.
Then double click on the form to open the code editor.
Add this function there

Function SheetExists(WorkSheetName As String) As Boolean
    Dim WSName As Worksheet
    On Error Resume Next
    Set WSName = Worksheets(WorkSheetName)
    If Not WSName Is Nothing Then SheetExists = True
End Function

After that assign this code to the button you just created.

Private Sub SearchSheets_Click()
    Dim WorkSheetName As String
    WorkSheetName = "Your Value"
    If SheetExists(WorkSheetName) Then
        MsgBox "Worksheet Found and activated"
        Responce = MsgBox("Worksheet with the name '" & WorkSheetName & "' not found", vbOKOnly)
    End If
End Sub

Now run the script and press the Search Worksheets button.
If there is a worksheet named “Your Value“(change it to your own) is available then the worksheet will bw activated and you will see this massage “Worksheet Found and activated”.
search worksheet using visual basic in excel
And if the worksheet is not Available then you will see this massage “Worksheet with the name ‘Your Value’ not found“.
worksheet not found visual basic macro in excel
That is it! Hope this helped you.

Add a Comment

Your email address will not be published. Required fields are marked *

Notify me of followup comments via e-mail. You can also subscribe without commenting.