How can I tell if a worksheet exists?
 

Hi,

I want to determine if a worksheet named Sheets("Chart") exists. How can I
do this using VBA?

Thank you,

Robert Stober

How can I tell if a worksheet exists?
 

Hi Robert:

Function WorksheetExists(wsName As String, _
Optional wbName As String) As Boolean
If wbName = "" Then wbName = _
ActiveWorkbook.Name
On Error Resume Next
WorksheetExists = CBool(Len(Workbooks(wbName) _
.Worksheets(wsName).Name))
End Function

?WorksheetExists("Charts")
False 'True

Regards,

Vasant.

"Robert Stober" wrote in message
...
Hi,

I want to determine if a worksheet named Sheets("Chart") exists. How can I
do this using VBA?

Thank you,

Robert Stober

How can I tell if a worksheet exists?
 

Robert,

Here's some code to show the principle

Dim oWS As Worksheet

On Error Resume Next
Set oWS = Activeworkbook.Worksheets("Chart")
On Error GoTo 0
If oWS Is Nothing Then
MsgBox "Worksheet does not exist"
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robert Stober" wrote in message
...
Hi,

I want to determine if a worksheet named Sheets("Chart") exists. How can I
do this using VBA?

Thank you,

Robert Stober

How can I tell if a worksheet exists?
 

"Robert Stober" wrote in
:

Hi,

I want to determine if a worksheet named Sheets("Chart") exists. How
can I do this using VBA?

Thank you,

Robert Stober



An alternate way which does not "abuse" the error mechanism:
(function header shamelessly stolen from V. Nanavati)

Function WorksheetExists(wsName As String, _
Optional wbName As String) As Boolean
If wbName = "" Then wbName = _
ActiveWorkbook.Name
Dim x as Variant
WorksheetExists = false
For Each x In Workbooks(wbName).Worksheets
If x.Name = wsName Then
WorksheetExists = True
Exit Function
Next
End Function

(I know it's silly to call it abuse, but I always get caught by
forgetting to do On Error Goto 0, so I tend to avoid using it as an
exception mechanism)

How can I tell if a worksheet exists?
 

Sub Test()
MsgBox SheetExist("A")
End Sub

Function SheetExist(shName) As Boolean
Dim sh As Worksheet

On Error GoTo Err_SheetExist

Set sh = Worksheets(shName) 'if worksheet exist, object sh is set
SheetExist = True
Set sh = Nothing 'we don't need this object

Exit Function

Err_SheetExist:
SheetExist = False 'worksheet not existing
Set sh = Nothing 'free memory
End Function


--
Sorry for my language, I'm still learning
losmac


U¿ytkownik "Robert Stober" napisa³ w wiadomo¶ci
...
Hi,

I want to determine if a worksheet named Sheets("Chart") exists. How can I
do this using VBA?

Thank you,

Robert Stober

How can I tell if a worksheet exists?
 

Thank you all for your generous response. I thought of this one before I saw
your collective responses:

' Add the chart sheet if needed
On Error Resume Next
Sheets("Chart").Select
If Err.Number < 0 Then
Sheets.Add
ActiveSheet.Name = "Chart"
End If

I've never used functions before - looks like I need to expand my
vocabulary!

Thank you,

Robert

"losmac" wrote in message
...
Sub Test()
MsgBox SheetExist("A")
End Sub

Function SheetExist(shName) As Boolean
Dim sh As Worksheet

On Error GoTo Err_SheetExist

Set sh = Worksheets(shName) 'if worksheet exist, object sh is set
SheetExist = True
Set sh = Nothing 'we don't need this object

Exit Function

Err_SheetExist:
SheetExist = False 'worksheet not existing
Set sh = Nothing 'free memory
End Function


--
Sorry for my language, I'm still learning
losmac


U¿ytkownik "Robert Stober" napisa³ w wiadomo¶ci
...
Hi,

I want to determine if a worksheet named Sheets("Chart") exists. How can
I
do this using VBA?

Thank you,

Robert Stober