CODEWITHSUNDEEP
excelvba
Nguyen Van Hiep
Nguyen Van Hiep

Reputation: 11

Avoid duplicate sheet name error

I have a custom ribbon that attach to a module "New Day"

what I want is to avoid duplicate sheetname error, exit sub if sheetname is aready created and adding msg "name aready exist" .

My code:

 Sub NewDay(control As IRibbonControl)

    Dim CopySheet As Long

    CopySheet = MsgBox("New Sheet", vbYesNo, "92x4-4xx9 xx INC")
    If CopySheet = vbNo Then Exit Sub
        ActiveSheet.Copy before:=ActiveSheet
        With ActiveSheet.Range("C1")
        .Parent.Name = Format(.Value, "mmm-dd-yyyy")
        Worksheets("Productions").Range("G6:G56").ClearContents
        Worksheets("Productions").Range("J6:J56").ClearContents
        Worksheets("Productions").Range("M6:O56").ClearContents
        Worksheets("Productions").Range("M63:N63").ClearContents
        Worksheets("Productions").Range("E59:Q59").ClearContents
        Range("C1") = Format(Date - 1)
        Sheets("Productions").Activate
        Productions.Range("G6").Select
        Range("C1") = Format(Date)
        End With

End Sub

Upvotes: 0

Views: 2316

Answers (3)

Nguyen Van Hiep
Nguyen Van Hiep

Reputation: 11

Thx guys I found what I need.

 Sub NewDay(control As IRibbonControl)

    Dim CopySheet As Long, ws As Workbook

    CopySheet = MsgBox("New Sheet", vbYesNo, "xxxxxxxxxx")
    If CopySheet = vbNo Then Exit Sub
        ActiveSheet.Copy before:=ActiveSheet
        With ActiveSheet.Range("C1")
            Dim WS_Sheet As Worksheet, intNumber As Integer
            On Error Resume Next
            Set WS_Sheet = Sheets(.Parent.Name = Format(.Value, "mmm-dd-yyyy"))
            On Error GoTo errHandler

            intNumber = 0 / 3

            If WS_Sheet Is Nothing Then ' Worksheet did not exist
                .Parent.Name = Format(.Value, "mmm-dd-yyyy")
                Worksheets("Productions").Range("G6:G56").ClearContents
                Worksheets("Productions").Range("J6:J56").ClearContents
                Worksheets("Productions").Range("M6:O56").ClearContents
                Worksheets("Productions").Range("M63:N63").ClearContents
                Worksheets("Productions").Range("E59:Q59").ClearContents
                Range("C1") = Format(Date - 1)
                Sheets("Productions").Activate
                Productions.Range("G6").Select
                Range("C1") = Format(Date)
            Else ' Worksheet exists
                ' Handle the problem here
                Exit Sub
errHandler:
                MsgBox Err.Number & Err.Description
                SendKeys "~"
                ActiveWindow.SelectedSheets.Delete

            End If
        End With
End Sub

Upvotes: 0

Dan Donoghue
Dan Donoghue

Reputation: 6216

Build a routine to trap and deal with errors. Here is an example of how to do it:

Sub SheetError()
    Dim MySheet As String
    On Error GoTo ErrorCheck
    MySheet = ActiveSheet.Name
    Sheets.Add
    ActiveSheet.Name = MySheet
    MsgBox "I continued the code"
    Activsheet.Name = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
    MsgBox "I will never get to here in the code"
    End
ErrorCheck:
    If Err.Description = "Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic." Then
        Resume Next
    Else
        MsgBox "Error I am not designed to deal with"
    End If
End Sub

Upvotes: 0

n0v1c3
n0v1c3

Reputation: 116

Sub NewDay()

    Dim CopySheet As Long

    CopySheet = MsgBox("New Sheet", vbYesNo, "92x4-4xx9 xx INC")
    If CopySheet = vbNo Then Exit Sub
        ActiveSheet.Copy before:=ActiveSheet
        With ActiveSheet.Range("C1")
            Dim WS_Sheet As Worksheet
            On Error Resume Next
            Set WS_Sheet = Sheets(.Parent.Name = Format(.Value, "mmm-dd-yyyy"))
            On Error GoTo 0

            If WS_Sheet Is Nothing Then ' Worksheet did not exist
                .Parent.Name = Format(.Value, "mmm-dd-yyyy")
                Worksheets("Productions").Range("G6:G56").ClearContents
                Worksheets("Productions").Range("J6:J56").ClearContents
                Worksheets("Productions").Range("M6:O56").ClearContents
                Worksheets("Productions").Range("M63:N63").ClearContents
                Worksheets("Productions").Range("E59:Q59").ClearContents
                Range("C1") = Format(Date - 1)
                Sheets("Productions").Activate
                Productions.Range("G6").Select
                Range("C1") = Format(Date)
            Else ' Worksheet exists
                ' Handle the problem here
            End If
        End With
End Sub

Upvotes: 1

Related Questions