How to match worksheet names against global variable?

[drmingle]

Objective: I would like to ask the user if when the name the worksheet the same as an already existing spreadsheet tabe if they would like to overwrite it or unload the user form.

I am not sure of two things:
1. how to find the already existing tab?
2. Once I find out how do I programatically delete it, so the code can continue

I appreciate your help in advance.

The code below works with the exception of the last section (trying to achieve the questions stated above).

I am using Excel 2010.

Private Sub CommandButton1_Click()
    If TextBox1.Value = blank Then 'Need name for processing
        MsgBox ("Name must not be blank.")
        Exit Sub
    End If
    
    If Len(TextBox1.Value) > 12 Then
        MsgBox ("Name must be no more than 12 characters.")
        Exit Sub
    End If
    
    If TextBox1.Value Like "*:*" Then
        MsgBox ("Name must not usual special characters like : \ / ? [ ].")
        Exit Sub
    End If
    If TextBox1.Value Like "*\*" Then
        MsgBox ("Name must not usual special characters like : \ / ? [ ].")
        Exit Sub
    End If
    If TextBox1.Value Like "*/*" Then
        MsgBox ("Name must not usual special characters like : \ / ? [ ].")
        Exit Sub
    End If
    
    If TextBox1.Value Like "*?*" Then
        MsgBox ("Name must not usual special characters like : \ / ? [ ].")
        Exit Sub
    End If
    
    If TextBox1.Value Like "*[*" Then
        MsgBox ("Name must not usual special characters like : \ / ? [ ].")
        Exit Sub
    End If
    
    If TextBox1.Value Like "*]*" Then
        MsgBox ("Name must not usual special characters like : \ / ? [ ].")
        Exit Sub
    End If
    
    Columns(1).Insert
    For i = 1 To Sheets.Count
        Cells(i, 1) = Sheets(i).Name
    Next i
  'Here is where I need help
    If TextBox1 & " " & Range("P1") = Sheets(i).Name Then
    MsgBox ("Duplicate name cannot exist.")
    End If
Exit Sub

 

[ChrisM]

You have to loop through all the worksheets and test them name by name:

Dim wrk as Worksheet

For Each wrk in ThisWorkbook.Worksheets
    If wrk.Name = Textbox1.Value Then
               'same name found
               'your code here
               Exit For    
End If
Next wrk

Deleting is easy, just "wrk.Delete" or "Sheets(10).Delete" or Sheets("yoursheetname").Delete
You will get the pop up deletion warning, which you probably don't want the user to see. In that case, turn off alerts:

Application.DisplayAlerts = False
wrk.Delete
Application.DisplayAlerts = True

 

[drmingle]

You have to loop through all the worksheets and test them name by name:

Dim wrk as Worksheet

For Each wrk in ThisWorkbook.Worksheets
    If wrk.Name = Textbox1.Value Then
               'same name found
               'your code here
               Exit For    
End If
Next wrk

Deleting is easy, just "wrk.Delete" or "Sheets(10).Delete" or Sheets("yoursheetname").Delete
You will get the pop up deletion warning, which you probably don't want the user to see. In that case, turn off alerts:

Application.DisplayAlerts = False
wrk.Delete
Application.DisplayAlerts = True

ChrisM:

Thank you for the reply and code.

In odrder to make a true comparison I need to have the following:

Let's TextBox1 = WVM

I am trying to get the code below to comapre using this output: WVM Pricing 06-10-2013

rather than just WVM

For whatever reason

For Each wrk In ThisWorkbook.Worksheets
    If wrk.Name = TextBox1.Value & " Pricing " & [B][COLOR=#B22222]Text(Date(),"MM-DD-YYYY)[/COLOR][/B]Then
               MsgBox ("Name already exists.")
               'your code here
               Exit For
End If

If I simply use "Date" the code doesn't recognize the match and continues with the code...

Any ideas?

 

[dmt32]

Hi, I have done this very quickly as about to go out - but see if code below (untested) is doing what you want. If not, you should be able to adjust as required.

Hope helpful

Dave.

Private Sub CommandButton1_Click()
    Dim check
    Dim myarray() As Variant
    Dim ShName As String
    Dim x As Integer
    Dim ws As Worksheet
    ShName = TextBox1.Value
    'check for unwanted characters
    myarray = Array("[", "]", "/", "*", "\", "?", ":", ".")
    For x = LBound(myarray) To UBound(myarray)
        check = InStr(1, ShName, myarray(x), 1)
        If check > 0 Then Exit For
    Next x
    If check > 0 Or Len(ShName) = 0 Or Len(ShName) > 12 Then
        If Len(ShName) = 0 Then  'Need name for processing
            MsgBox "Name must not be blank.", 16, "Error"
        ElseIf Len(ShName) > 12 Then
            MsgBox "Name must be no more than 12 characters.", 16, "Error"
        ElseIf check Then
            MsgBox ("Name must not usual special characters like : \ / ? [ ].")
        End If
        With TextBox1
            .Value = ""
            .SetFocus
        End With
        Exit Sub
    End If
    ShName = ShName & " Pricing " & Format(Date, "MM-DD-YYYY")
    On Error Resume Next
    Set ws = Worksheets(ShName)
    On Error GoTo 0
    If Not ws Is Nothing Then
        msg = MsgBox(ShName & Chr(10) & _
                     "Duplicate name cannot exist." & Chr(10) & _
                     "Do You Want To Delete Existing Worksheet?", 36, "Sheet Exists")
        If msg = 7 Then Exit Sub
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
        GoTo addsheet
    Else
addsheet:
        Set ws = Worksheets.Add
        ws.Move After:=Worksheets(Worksheets.Count)
        ws.Name = ShName
        MsgBox ShName & Chr(10) & _
               "New Sheet Has Been Added", 64, "New Sheet"
    End If
End Sub

 

[ChrisM]

Try: TextBox1.Value & " Pricing " & Format(Now(),"MM-DD-YYYY")

 

[drmingle]

Thanks to both of you guys....

wonderful solutions.