Custom Message instead of error msg

[Shaido]

Hi

I've created a macro within excel 2010 that adds a new tab called 'Data' into my worksheet. The main analysis of the worksheet is now run from this 'Data' worksheet.

My problem is if the 'Data' worksheeet has been created already, and the Macro that creates it is run again, by accident, I get the 'End' or 'Debug' msg box, and am also thrown into the VBA screen.

Is there a way I can bring up a Msg that says "Data sheet already created" and also then ends the Macro? And without being thrown into the VBA screen?

Thanks.

 

[JackDanIce]

Welcome to the board.

Assuming the sheet is ALWAYS called "Data" try:

Sub TestForDataSheet ()
Dim i as Long
For i = 1 to Worksheets.Count
     If sheets(i).Name = "Data" Then
        MsgBox "Data sheet already created, macro exiting"
        Exit Sub
     End If
Next i
End Sub

 

[VoG]

Try adding this function

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

You can then use

If WorksheetExists("Data") Then
    MsgBox "Already exists", vbInformation
    Exit Sub
End If
'
'code to add sheet

 

[Boller]

Dim wsName$
On Error Resume Next
wsName = Sheets("Data").Name
On Error GoTo 0
If wsName <> "" Then
    MsgBox "Data sheet already created."
    Exit Sub
End If

 

[Shaido]

Brilliant! Thanks for your input, I have a few solutions now to try.