Searching Worksheets in Excel by Name

[mforema]

Hello,
I have a userform in Excel with a textbox and cmd button. I want to give the user the ability to search for a specific worksheet name within an Excel Workbook.

My code for the cmd button is as follows:

Private Sub cmdSearchName_Click()
Dim ChemName As String
    'assigns user's entry to the variable "ChemName"
    ChemName = txtName.Value
 
    If ChemName = "" Then  'if user has not entered a name then display message
        MsgBox ("Please enter a compound name")
    Else  'display sheet with matching name
        'Here is where I have problems
        For Each ActiveWorkbook.name In Workbooks("BOOK1.XLS")
            If ActiveWorkbook.name = ChemName Then
                Sheets(ChemName).Activate
                MsgBox ("" & ChemName & " has been found")
                frmNameSearch.Hide
                Exit For
            End If
        Next
    End If
End Sub

My logic is as follows:

[PHP]For Each worksheet In Workbook
If worksheet's name = ChemName Then
Show the requested worksheet
Exit For
End if
Next[/PHP]
But I don't know how to write that in VBA. I attempted it, but I keep getting errors.

Can anyone help?

Thanks!

[mforema]

Hello,
I have a userform in Excel with a textbox and cmd button. I want to give the user the ability to search for a specific worksheet name within an Excel Workbook.

My code for the cmd button is as follows:

Private Sub cmdSearchName_Click()
Dim ChemName As String
    'assigns user's entry to the variable "ChemName"
    ChemName = txtName.Value
 
    If ChemName = "" Then  'if user has not entered a name then display message
        MsgBox ("Please enter a compound name")
    Else  'display sheet with matching name
        'Here is where I have problems
        For Each ActiveWorkbook.name In Workbooks("BOOK1.XLS")
            If ActiveWorkbook.name = ChemName Then
                Sheets(ChemName).Activate
                MsgBox ("" & ChemName & " has been found")
                frmNameSearch.Hide
                Exit For
            End If
        Next
    End If
End Sub

My logic is as follows:

[PHP]For Each worksheet In Workbook
If worksheet's name = ChemName Then
Show the requested worksheet
Exit For
End if
Next[/PHP]
But I don't know how to write that in VBA. I attempted it, but I keep getting errors.

Can anyone help?

Thanks!

I should also note that there will never be sheets that have the same name.

Thanks!

[Stewart Ross]

Hi. One way to do so, where you provide the name of the worksheet to search for as a string:

Public Sub MatchWorkSheetName(nametofind As String)
    Dim objWorksheet As Worksheet
    For Each objWorksheet In ActiveWorkbook.Worksheets
        If objWorksheet.Name = nametofind Then
            Worksheets(nametofind).Select
            Exit Sub
        End If
    Next objWorksheet
    ' if we get to here there has been no match
    MsgBox "There are no sheets named " & nametofind & " in this workbook", vbExclamation
End Sub

Even simpler if you want:

Public Sub MatchWorkSheetName(nametofind As String)
    On Error GoTo NoMatch
    Worksheets(nametofind).Select
    Exit Sub
NoMatch:    ' if we get to this error handler there has been no match
    MsgBox "There are no sheets named " & nametofind & " in this workbook", vbExclamation
End Sub

-Stewart

[mforema]

Hi. One way to do so, where you provide the name of the worksheet to search for as a string:

Public Sub MatchWorkSheetName(nametofind As String)
    Dim objWorksheet As Worksheet
    For Each objWorksheet In ActiveWorkbook.Worksheets
        If objWorksheet.Name = nametofind Then
            Worksheets(nametofind).Select
            Exit Sub
        End If
    Next objWorksheet
    ' if we get to here there has been no match
    MsgBox "There are no sheets named " & nametofind & " in this workbook", vbExclamation
End Sub

Even simpler if you want:

Public Sub MatchWorkSheetName(nametofind As String)
    On Error GoTo NoMatch
    Worksheets(nametofind).Select
    Exit Sub
NoMatch:    ' if we get to this error handler there has been no match
    MsgBox "There are no sheets named " & nametofind & " in this workbook", vbExclamation
End Sub

-Stewart

It worked!
Thanks Stewart!