By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Searching Worksheets in Excel by Name

P: 72
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearchName_Click()
  2. Dim ChemName As String
  3.     'assigns user's entry to the variable "ChemName"
  4.     ChemName = txtName.Value
  5.  
  6.     If ChemName = "" Then  'if user has not entered a name then display message
  7.         MsgBox ("Please enter a compound name")
  8.     Else  'display sheet with matching name
  9.         'Here is where I have problems
  10.         For Each ActiveWorkbook.name In Workbooks("BOOK1.XLS")
  11.             If ActiveWorkbook.name = ChemName Then
  12.                 Sheets(ChemName).Activate
  13.                 MsgBox ("" & ChemName & " has been found")
  14.                 frmNameSearch.Hide
  15.                 Exit For
  16.             End If
  17.         Next
  18.     End If
  19. 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!
May 28 '08 #1
Share this Question
Share on Google+
3 Replies


P: 72
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearchName_Click()
  2. Dim ChemName As String
  3.     'assigns user's entry to the variable "ChemName"
  4.     ChemName = txtName.Value
  5.  
  6.     If ChemName = "" Then  'if user has not entered a name then display message
  7.         MsgBox ("Please enter a compound name")
  8.     Else  'display sheet with matching name
  9.         'Here is where I have problems
  10.         For Each ActiveWorkbook.name In Workbooks("BOOK1.XLS")
  11.             If ActiveWorkbook.name = ChemName Then
  12.                 Sheets(ChemName).Activate
  13.                 MsgBox ("" & ChemName & " has been found")
  14.                 frmNameSearch.Hide
  15.                 Exit For
  16.             End If
  17.         Next
  18.     End If
  19. 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!
May 28 '08 #2

Expert Mod 2.5K+
P: 2,545
Hi. One way to do so, where you provide the name of the worksheet to search for as a string:

Expand|Select|Wrap|Line Numbers
  1. Public Sub MatchWorkSheetName(nametofind As String)
  2.     Dim objWorksheet As Worksheet
  3.     For Each objWorksheet In ActiveWorkbook.Worksheets
  4.         If objWorksheet.Name = nametofind Then
  5.             Worksheets(nametofind).Select
  6.             Exit Sub
  7.         End If
  8.     Next objWorksheet
  9.     ' if we get to here there has been no match
  10.     MsgBox "There are no sheets named " & nametofind & " in this workbook", vbExclamation
  11. End Sub
Even simpler if you want:

Expand|Select|Wrap|Line Numbers
  1. Public Sub MatchWorkSheetName(nametofind As String)
  2.     On Error GoTo NoMatch
  3.     Worksheets(nametofind).Select
  4.     Exit Sub
  5. NoMatch:    ' if we get to this error handler there has been no match
  6.     MsgBox "There are no sheets named " & nametofind & " in this workbook", vbExclamation
  7. End Sub
-Stewart
May 29 '08 #3

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

Expand|Select|Wrap|Line Numbers
  1. Public Sub MatchWorkSheetName(nametofind As String)
  2.     Dim objWorksheet As Worksheet
  3.     For Each objWorksheet In ActiveWorkbook.Worksheets
  4.         If objWorksheet.Name = nametofind Then
  5.             Worksheets(nametofind).Select
  6.             Exit Sub
  7.         End If
  8.     Next objWorksheet
  9.     ' if we get to here there has been no match
  10.     MsgBox "There are no sheets named " & nametofind & " in this workbook", vbExclamation
  11. End Sub
Even simpler if you want:

Expand|Select|Wrap|Line Numbers
  1. Public Sub MatchWorkSheetName(nametofind As String)
  2.     On Error GoTo NoMatch
  3.     Worksheets(nametofind).Select
  4.     Exit Sub
  5. NoMatch:    ' if we get to this error handler there has been no match
  6.     MsgBox "There are no sheets named " & nametofind & " in this workbook", vbExclamation
  7. End Sub
-Stewart
It worked!
Thanks Stewart!
May 29 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.