View Full Version : Find a worksheet by name
sukumar.vb
08-12-2011, 04:33 PM
I was looking to find a worksheet by name.
I did this program: -
Option Base 1
Private Sub test()
Dim n, c As Integer
c = Worksheets.Count
For n = 1 To c
If Worksheets(n).Name = "Work" Then
Worksheets("Work").Activate
Else
MsgBox "Not found", vbInformation
End If
Next n
End Sub
I guess that there could be a better one. Please suggest. :help
Above program will go to first sheet and then perform MsgBox. Then it will go to second sheet and perform Msgbox.
So, total n Msgbox will be displayed, if there are n sheets in a Workbook.
I am looking to display only one Msgbox, if sheet "Work" in not found in n sheets.
If you want to loop through all the sheets to find one, you would want to set a flag to True if it was found. Then the MsgBox would be called after the looping and if the flag was not True.
Rather than looping however, maybe try setting a reference to the sheet. If the Set fails, we know the sheet doesn't exist.
Option Explicit
Sub TestIt()
Dim mySheet As Worksheet
Dim strShName As String
strShName = "Work"
If SheetExists(strShName, mySheet) Then
mySheet.Activate
Else
MsgBox "The worksheet named: " & strShName & " does not exist.", vbInformation, vbNullString
End If
End Sub
Function SheetExists(ByVal ShName As String, ByRef wks As Worksheet, Optional ByVal WB As Workbook) As Boolean
'// If the arg is not passed, assume we are checking ThisWorkbook //
If WB Is Nothing Then Set WB = ThisWorkbook
'// In case wks was already set by the calling procedure. //
Set wks = Nothing
'// Allow an error to be passed by, just for the one line. //
On Error Resume Next
'// If the sheet (as referred to by its tab) doesn't exist, wkk will//
'// continue to be Nothing. //
Set wks = WB.Worksheets(ShName)
On Error GoTo 0
If Not wks Is Nothing Then SheetExists = True
End Function
Hope that helps,
Mark
svarma
08-12-2011, 06:43 PM
Here is a slight variation on your original code that accomplishes the task:
Private Sub test()
Dim n As Integer, c As Integer
c = Worksheets.Count
Dim found As Boolean
found = False
For n = 1 To c
If Worksheets(n).Name = "Work" Then
found = True
Worksheets("Work").Activate
End If
Next n
If found = False Then MsgBox "Not found", vbInformation
End Sub
Paul_Hossler
08-13-2011, 07:40 AM
More along the same lines. Test 3 will take some corrective actions (as example)
Sub Test_2()
Dim iDummy As Long
iDummy = 0
On Error Resume Next
iDummy = Worksheets("Work").Index
On Error GoTo 0
If iDummy > 0 Then
Worksheets("Work").Activate
Else
msgbox "No worksheet"
End If
End Sub
Sub Test_3()
On Error GoTo NoWorksheet
Worksheets("Work").Activate
msgbox "Sub continues"
Exit Sub
NoWorksheet:
If Err.Number = 9 Then
msgbox "No worksheet, adding one for you"
Worksheets.Add.Name = "Work"
Else
msgbox "Some other error = " & Err.Number & " (" & Err.Description & ")"
End If
Resume Next
End Sub
Paul
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.