Confirm Worksheet does not exist

Dim cel As String
cel = Range("A1") ' name of the cell
        If Not Evaluate("ISREF('" & cel & "'!A1)") Then

           Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = cel
        Else
        
          MsgBox "The sheet has alredy existed"
        End If

Hi, Dave,

If Not Evaluate("ISREF('" & Sheets("Sheet1").Range("A1").Value & "'!A1)") Then
  Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Sheets("Sheet1").Range("A1").Value
Else
  MsgBox "The sheet already exists"
End If

Ciao,
olger

RIght im totally lost,

my code below, as it has been given by someone working with me for review, i simply added a sheet name part as they recorded the whole thing and i did a quick tidy and added a quick enter date check at the start.

If Range("B2:C2").Text = "" Then 'B2:C2 is passed to A50 and and this cell is used to name the sheet.
    MsgBox "Please enter Date in B2"
    Exit Sub
           
    Else
        
    ActiveSheet.Unprotect
    Sheets(1).Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Format(Range("A50"), "dd-mm-yyyy")
    
    Sheets(Sheets.Count).Protect
    Sheets(1).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
    False
    End If
    
    ActiveWorkbook.Save

I thought the check would have been something similar to

Dim wkshtnme as string
wkshtnme = Format(Range("A50"), "dd-mm-yyyy")

if worksheets.name = wkshtnme then 'here is where i thought there might be a straight forward line to solve this.
msgbox "this name exists, reenter name"
exit sub
else
'perform the bulk of the code above...

should i be using an error check or a do while loop?

i relatively new to vba and spend a good bit of time with vb.net but im a total novice...

so im happy for all advice as i like to try and get there myself in the end

In a new workbook, Sheet1 will exist and Sheet4 will not exist.

Option Explicit

Function fSheetExists(shName As String)
Dim wsSheet As Worksheet
On Error Resume Next
Set wsSheet = Sheets(shName)
On Error GoTo 0
If Not wsSheet Is Nothing Then
    fSheetExists = True
Else
    fSheetExists = False
End If

End Function

Sub Test_fSheetExists()

MsgBox "Sheet1 " & IIf(fSheetExists("Sheet1"), "exists", "does not exist")
MsgBox "Sheet4 " & IIf(fSheetExists("Sheet4"), "exists", "does not exist")

End Sub

Regards, TMS

Option Explicit
Sub test1()

Dim wkshtnme As String
wkshtnme = Format(Range("A50"), "dd-mm-yyyy")
        If Not Evaluate("ISREF('" & wkshtnme & "'!A1)") Then

           Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = wkshtnme
             MsgBox "this name exists, reenter name"
            Exit Sub
        Else
          MsgBox "The sheet has alredy existed"
       
        End If
End Sub

With the function that I provided, your code would look like:

Sub Test()

Dim wkshtnme As String
wkshtnme = Format(Range("A50"), "dd-mm-yyyy")

If fSheetExists(wkshtnme) Then 'here is where i thought there might be a straight forward line to solve this.
    MsgBox "this name exists, reenter name"
    Exit Sub
Else
    'perform the bulk of the code above...
End If
End Sub

Regards, TMS

have it working now, many thanks for all your input.

I have a question if anyone wants to give me a quick explanation. the code in the tags is the working code.

but i want to know in basic english what this is telling me.....

If Not Evaluate("ISREF('" & wkshtnme & "'!A1)") Then

am i right in thinking, if the sheet name does not exist then.......

could i be so rude to ask for this line to be broken down better than that?

    If Range("B2:C2").Text = "" Then
    MsgBox "Please enter Date in B2"
    Exit Sub
           
    Else
           
    Dim wkshtnme As String
    
    wkshtnme = Format(Range("A50"), "dd-mm-yyyy")
    
    If Not Evaluate("ISREF('" & wkshtnme & "'!A1)") Then

    ActiveSheet.Unprotect
    Sheets(1).Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = wkshtnme
    Sheets(Sheets.Count).Protect
    Sheets(1).Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
    False
    Else
    MsgBox "The sheet Name Already exists" & vbNewLine & "Please re-enter a newdate"
    Exit Sub
    
    
    End If
    End If
    ActiveWorkbook.Save

You're welcome. Thanks for the rep.

when creating a function, do i create a new module, save the function in there and call it back like any other macro by name when i want to use it?

Essentially, yes. Doesn't have to be in its own module but I consider it best practice. If you rename the module, it's easier to find things.

Regards, TMS