Find Worksheet Name without On Error Resume Next

  • Thread starter Thread starter Ryan H
  • Start date Start date
R

Ryan H

Is there a way to ensure a worksheet is in a workbook without using the On
Error Resume Next statement? I have the name of the worksheet, now I want to
search the workbook for that sheet, how do I do that?
 
Hi Ryan

It can be done like this:

Dim IsThere As Boolean
TargetSh = "Sheet1"
For Each sh In ThisWorkbook.Sheets
If sh.Name = TargetSh Then
IsThere = True
Exit For
End If
Next
If IsThere Then
MsgBox ("Is in the workbook")
Else
MsgBox ("Is not in the workbook")
End If

Regards,
Per
 
Hi,

Someone will inevitably prove me incorrect but without using ONERROR I think
you have to loop

Dim WorksheetExists As Boolean
For x = 1 To Worksheets.Count
If Sheets(x).Name = "Sheet1" Then
WorksheetExists = True
Exit For
End If
Next
If WorksheetExists Then
MsgBox "Sheet there"
Else
MsgBox "Sheet not there"
End If

Mike
 
There's nothing wrong with the "on error resume next" approach in VBA. And it's
quicker, too.

Why wouldn't you want to use it?
 
Back
Top