Worksheet Tabs.

[Nigel Martin]

I have a spreadsheet whereby the user is invited to create a new workshheet via macros which is given a name, but they do not have to at this stage. However on other worksheets there are buttons with macros attached that will take them to the created worksheet. So I need a line of code for a macro that tests to see if a particular worksheet exists. eg something like if worksheet "abcd" exists then avtivate it or if worksheet "abcd" does not exist then activated the worksheet that invites the user to create it. Any ideas ?

[sen_edp]

hello Nigel

The following came from a posting by Chip Pearson 23Oct1999 in programming.

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Len(Worksheets(WSName).Name) > 0
End Function

Then, in your code,

If WorksheetExists("Summary") = True Then
MainMacro
Else
CreateSummarySheet
End If

does it helps ?

[Nigel Martin]

Hello Andreas
I tried your suggestion in a macro but it threw up error messages. I need it to work in a macro because it only needs to be tested if the user requests that particular worksheet to be activated. Regards Nigel.

[sen_edp]

hello Nigel

i changed the code to fit your case

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Len(Worksheets(WSName).Name) > 0
End Function

Then, in your code,

If WorksheetExists("abcd") = True Then
Msgbox("the worksheet exists")
Else
sheets.add.name="abcd"
End If

first you have to create the udf Worksheetsexist and then run the macro that uses it.

I hope that helps, i tried it on my pc and it works , although i don't know what code you have, and where to put these checks .