CODEWITHSUNDEEP
excelvba
G. Koen
G. Koen

Reputation: 33

If sheet exist, don't add another one

If the sheet "Data" does not exist, the code works perfectly, if it does exist however I get the error "Name already exists, try a different name". I've simply fixed this with an On Error GoTo ErrorHandler, the problem however is that after the code runs and the macro triggers the errorhandler msgbox , it still creates a new sheet regardless (with the names 'Sheet1,2,3,...').

Snippet:

Sub AddWorkSheet()
    Dim wb As Workbook, shtDest As Worksheet
    On Error GoTo ErrorHandler
    Sheets.Add.Name = ("Data")
    Set shtDest = Sheets("Data")

ErrorHandler:
    MsgBox ("Something went wrong."), vbCritical

End Sub

I'd like the code to simply return the message box and not create any additional sheets if the sheet "Data" already exists.

Upvotes: 0

Views: 1354

Answers (3)

Egalth
Egalth

Reputation: 1000

(EDIT: A previous version of this answer produced the wrong result, details in comments.)

This should work. Note the use of On Error Resume Next combined with On Error GoTo 0 instead of On Error GoTo ErrorHandler. (GoTo statements are commonly associated with so-called "spaghetti code", so it could be a good habit to avoid them when possible.)

Sub AddWorkSheet()
    Dim wb As Workbook, ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets("Data")
    If Err.Number <> 9 Then ' 9 means subscript out of range
        MsgBox ("Sheet already exists."), vbCritical
    Else
        Sheets.Add After:=Worksheets(Worksheets.Count)
        Sheets(Worksheets.Count).Name = "data"
    End If
    On Error GoTo 0
End Sub

In your original code, you could add an Exit Sub statement before the error handler; without it ErrorHandler will always be called. (You might want to have a look at the documentation.).

Upvotes: 0

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11998

When you try to assign a non-exist worksheet to a variable, VBA will return error 9. From my POV, I think it's useful to trap that error and then create the worksheet if needed.

So I would use:

Sub AddWorkSheet()
Dim wb As Workbook, shtDest As Worksheet

On Error GoTo ErrorHandler
Set shtDest = Sheets("Data")


'<--rest of your code-->
'
'
'
'
'
'
''<--rest of your code-->

Set shtDest = Nothing
Set wb = Nothing

Exit Sub


ErrorHandler:
If Err.Number = 9 Then
    'must create worksheet DATA
    Sheets.Add.Name = ("Data")
    Set shtDest = Sheets("Data")
    Resume Next
Else
    'different error. MSgbox with new error
    MsgBox Err.Description, vbCritical, Err.Number
    Stop
End If
End Sub

Please, note that right before the ErrorHanlder part, I added Exit Sub to avoid this part of code being executed every time the sub runs.

Upvotes: 1

EvR
EvR

Reputation: 3498

Before adding a sheet, check if it exists:

Function ShtExist(ShtName As String) As Boolean
On Error Resume Next
    ShtExist = Len(ThisWorkbook.Sheets(ShtName).Name) > 0
On Error GoTo 0
End Function

Sub AddWorkSheet()

    Dim wb As Workbook, shtDest As Worksheet
    If ShtExist("Data") Then
    MsgBox ("Something went wrong."), vbCritical
    Else
    Sheets.Add.Name = ("Data")
    Set shtDest = Sheets("Data")
    End If

End Sub

Upvotes: 1

Related Questions