ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

Determine if a sheet exists in a workbook

The function below can be used to determine if a sheet exists in a workbook:

Function SheetExists(strSheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error Resume Next
    SheetExists = Len(Sheets(strSheetName).Name) > 0
    On Error GoTo 0
End Function

Example:

If Not SheetExists("MySheetName") Then
    MsgBox "MySheetName doesn't exist!"
Else
    Sheets("MySheetName").Activate
End If

The function below can be used to determine if a sheet exists in a specific workbook:

Function SheetExists(wb As Workbook, strSheetName As String) As Boolean
' returns TRUE if the sheet exists in wb
    SheetExists = False
    On Error Resume Next
    SheetExists = Len(wb.Sheets(strSheetName).Name) > 0
    On Error GoTo 0
End Function

Example:

If Not SheetExists(ThisWorkbook, "MySheetName") Then
    MsgBox "MySheetName doesn't exist in " & ThisWorkbook.Name
Else
    MsgBox "MySheetName exist in " & ThisWorkbook.Name
End If

If Not SheetExists(Workbooks("WorkbookName.xls"), "MySheetName") Then
    MsgBox "MySheetName doesn't exist!"
Else
    MsgBox "MySheetName exist!"
End If

 

Document last updated 1999-12-20 12:51:27      Printerfriendly version

User comments:
Liekens J from Sint-Katelijne-Waver, Belgium wrote (2006-05-18 19:36:33 CET):
RE (Sheet")
Ole P.
Thanks. It works
Ole P. from Norway wrote (2006-05-18 00:09:57 CET):
Re: SHEET(
Something like this might be what you are looking for:

strName = "Liekens Jan" ' populate the name variable
If SheetExists(strName) Then
Worksheets(strName).Visible = True
Else
MsgBox "No worksheet for this name exists!"
End If

Jan Liekens from Sint-Katelijne-Waver, Belgium wrote (2006-05-17 22:15:13 CET):
SHEET("variable name").visible=true
How can I make a sheet visible when I select from a database a name with the same name as the sheet.
Example : I select my name Liekens Jan and I want to open the sheet with the same name. The name of the person is variable.
Thanks
Ole P. from Norway wrote (2005-06-13 09:55:13 CET):
Re: can't get this work with a chart sheet ?
The function works just fine with any type of sheet in a workbook, including chart sheets.
This will return True if a sheet named "Chart1" exists in the active workbook: If SheetExists("Chart1") Then ...
You can't use the function to determine if a chart object (a chart in a worksheet) exists.
Ted from Austin,Texas wrote (2005-06-13 06:48:39 CET):
can't get this work with a chart sheet ?
this works fine with normal sheets, but i can't get this to work with chart sheets.
Ole P. from Norway wrote (2005-04-07 22:15:04 CET):
Re: Chart problem again...
If the function can't find the name you send to the function, the active workbook doesn't contain a sheet with this name.
I have added a new function that let you specify the workbook where you want the function to look for the sheet name.
Nicolas from Zürich (Switzerland) wrote (2005-04-07 16:21:17 CET):
Chart problem again...
Hi Ole,

I tried to apply this function with a chart but it had not happened. Actually, the function works well with one chart of the workbook, but if I want to test a another chart of the workbook, the function can't find it!!
All charts are similar(unless that the "successful" chart has no ChartType); the function code is correct; the arguments are valid... I really have no idea anymore about what to do.

Thanks a lot in advance for your help.
Ole P. from Norway wrote (2005-03-17 22:03:30 CET):
Re: Does this function work with a chart too ?
No idea why this might happen, that is why you in some cases need to move the chart:
Charts(1).Move After:=Sheets(2)
Nicolas from Zürich (Switzerland) wrote (2005-03-17 10:27:48 CET):
Does this function work with a chart too ?
Hi Ole,

Thanks for the tip. It works really better now.
But your trick for placing the new created chart doesn't work very well: despite of the command line ".Add After:=Sheets(...)", the new chart is placed before (on the left of) the only one sheet.
It's not so important, but have you any idea of what happens ?

Thanks again for the tip for the existence test!
Ole P. from Norway wrote (2005-03-16 15:57:17 CET):
Re: Does this function work with a chart too ?
Yes, this function will work on all types of sheets in a workbook.
No modification is necessary.

This will add a chart after the active worksheet:
Charts.Add After:=Sheets(ActiveSheet.Name)

You might in some cases need to move the chart:
Charts(1).Move After:=Sheets(2)

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2024    Ole P. Erlandsen   All rights reserved
E-mail Contact Address