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?
--
Cheers,
Ryan

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

"Ryan H" skrev i meddelelsen
...
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?
--
Cheers,
Ryan

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

"Ryan H" wrote:

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?
--
Cheers,
Ryan

Thats what I figured I had to do. I wasn't sure if there was a quicker way.
--
Cheers,
Ryan


"Per Jessen" wrote:

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

"Ryan H" skrev i meddelelsen
...
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?
--
Cheers,
Ryan

.

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?

Ryan H wrote:

Thats what I figured I had to do. I wasn't sure if there was a quicker way.
--
Cheers,
Ryan

"Per Jessen" wrote:

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

"Ryan H" skrev i meddelelsen
...
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?
--
Cheers,
Ryan

.


--

Dave Peterson