Hi,

I need to test for the existence of a worksheet with a given name.
Presently I'm using some code I found on the net like:

On Error Resume Next
Set xlobj = Workbooks(WorkBookName).Worksheets(SheetName)
If Err = 0 Then
do something
else
do another thing

End If

I find that this is an awkward way to perform the test because my
If..Then..Else is False no matter which error occurs.

Is there a more definitive way to perform the test?

TIA
--
Damien

Hi Damien,

Try this.

Sub Test_For_Sheet_Name()

Dim Sht
Dim GivenShtName

GivenShtName = "Sheet1"

For Each Sht In Sheets
If Sht.Name = GivenShtName Then
MsgBox "The sheet name " & GivenShtName & " found"
End If
Next Sht

End Sub

Regards,

OssieMac

"Damien McBain" wrote:

Hi,

I need to test for the existence of a worksheet with a given name.
Presently I'm using some code I found on the net like:

On Error Resume Next
Set xlobj = Workbooks(WorkBookName).Worksheets(SheetName)
If Err = 0 Then
do something
else
do another thing

End If

I find that this is an awkward way to perform the test because my
If..Then..Else is False no matter which error occurs.

Is there a more definitive way to perform the test?

TIA
--
Damien

One way

Sub isitthere()
Dim wSheet As Worksheet
On Error Resume Next
Set wSheet = Sheets("Sheet1") 'change as required
If wSheet Is Nothing Then
MsgBox "Worksheet does not exist", vbCritical
Set wSheet = Nothing
On Error GoTo 0
Else
MsgBox "Sheet 1 does exist", vbInformation
Set wSheet = Nothing
On Error GoTo 0
End If
End Sub

Mike

"Damien McBain" wrote:

Hi,

I need to test for the existence of a worksheet with a given name.
Presently I'm using some code I found on the net like:

On Error Resume Next
Set xlobj = Workbooks(WorkBookName).Worksheets(SheetName)
If Err = 0 Then
do something
else
do another thing

End If

I find that this is an awkward way to perform the test because my
If..Then..Else is False no matter which error occurs.

Is there a more definitive way to perform the test?

TIA
--
Damien

Not my invention, I found this function in this forum, but I forgot the
author's name, sorry!

Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function

Regards,
Stefi

€žDamien McBain€� ezt Ã*rta:

Hi,

I need to test for the existence of a worksheet with a given name.
Presently I'm using some code I found on the net like:

On Error Resume Next
Set xlobj = Workbooks(WorkBookName).Worksheets(SheetName)
If Err = 0 Then
do something
else
do another thing

End If

I find that this is an awkward way to perform the test because my
If..Then..Else is False no matter which error occurs.

Is there a more definitive way to perform the test?

TIA
--
Damien

It looks like Chip Pearson's.

Stefi wrote:

Not my invention, I found this function in this forum, but I forgot the
author's name, sorry!

Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function

Regards,
Stefi

€žDamien McBain€� ezt Ã*rta:

Hi,

I need to test for the existence of a worksheet with a given name.
Presently I'm using some code I found on the net like:

On Error Resume Next
Set xlobj = Workbooks(WorkBookName).Worksheets(SheetName)
If Err = 0 Then
do something
else
do another thing

End If

I find that this is an awkward way to perform the test because my
If..Then..Else is False no matter which error occurs.

Is there a more definitive way to perform the test?

TIA
--
Damien


--

Dave Peterson

Dave Peterson wrote:

It looks like Chip Pearson's.

Stefi wrote:

Not my invention, I found this function in this forum, but I forgot the
author's name, sorry!

Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function

Thanks y'all :)