Check if sheet name already exist

**Tpleme** · 10-13-2018, 06:57 AM

Hello, i have this segment of the code that creates a sheet named with the textbox1.value.

I want it to check first if that sheet already exists, if so dont create the sheet and continue the code.

Dim Sheetname As String
Dim ws As Worksheet

Sheetname = TextBox1.value
If Sheetname <> "" Then
Set ws = ThisWorkbook.Sheets.Add(after:=Sheets(Sheets.Count))
ws.Name = Sheetname
End If

hope u can help me

**YasserKhalil** · 10-13-2018, 07:03 AM

Try this code

Sub Test()
    Dim ws          As Worksheet
    Dim SheetName   As String

    SheetName = TextBox1.Value
    
    If SheetName <> "" And Not Evaluate("ISREF('" & SheetName & "'!A1)") Then
        Set ws = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
        ws.Name = SheetName
    Else
        MsgBox "Exists", vbExclamation
    End If
End Sub

**Tpleme** · 10-13-2018, 09:24 AM

thank you, works like a charm, bu t i still dont get that part.

can you explain that section?

 If SheetName <> "" And Not Evaluate("ISREF('" & SheetName & "'!A1)") Then

i realy like to understand the code and what it does insted of just copyu paste :D

**YasserKhalil** · 10-13-2018, 09:34 AM

You're welcome
ISREF is a worksheet function which can be used in worksheet

=ISREF(Sheet1!A1)

It returns True if Sheet1 exists .. and if you change "Sheet1" to non-existing sheet, a window of "Update Values" will popup as an evidence that the sheet is not existing so this returns True or False
And using Not reverse the boolean value ..
so if the sheet doesn't exist so this part would return 'False' and Not False =True then the lines after the condition line will be executed
Regards

**Tpleme** · 10-13-2018, 09:38 AM

got it :D

thanks for your help

Best Regards

**YasserKhalil** · 10-13-2018, 09:39 AM

You're welcome. Glad I can offer some help
please mark the thread as solved

Check if sheet name already exist

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Check if sheet name already exist

Re: Check if sheet name already exist

Re: Check if sheet name already exist

Re: Check if sheet name already exist

Re: Check if sheet name already exist

Re: Check if sheet name already exist

Thread Information

Users Browsing this Thread

Similar Threads

Before run code Check first if sheet name exist or not

Check if sheet exist based on drop down list

[SOLVED] Check First IF Sheet Is Exist With The Name Of Cell K7 If exist Then run the code

[SOLVED] check column headers against sheet names if sheet not exist create it

Function to check the sheet name exist or not based on sheet values

macro to check if a value exist in sheet and add it if it doesnt

macro to check if a value exist in sheet and add it +other parts of row if it doesnt

Bookmarks

Bookmarks

Posting Permissions