VBA Check if sheet exists

[Klturi421]

I am building a form to create new employees profile, when I do so I would like to check to see if the sheet exists first and give a warning so changes could be made as necessary. All I am looking for at the moment is a simple if it exists msgbox else msgbox.

Any assistance would be appreciated.

 

[Anonymously]

Maybe not the most elegant but:

dim x as integer
dim y as integer
y = sheets.count

for x = 1 to y

if sheets(x).name = "Whatever you want" Then
msgbox "Sheet Exists"
Exit Sub
end if

next x

msgbox "Sheet doesn't exist."

 

[mikerickson]

Try

Sub test()
    If SheetExists("Smith") Then
        MsgBox "Sheet Smith exists"
    Else
        MsgBox "no sheet named Smith"
    End If
End Sub


Function SheetExists(sheetName as String, optional Wb as workbook) As Boolean
    If Wb Is Nothing then Set Wb = ThisWorkbook
    On Error Resume Next
    SheetExists = (LCase(wb.Sheets(sheetName).Name) = LCase(sheetName))
    On Error Goto 0
Exit Function

 

[Klturi421]

Both worked perfectly for what I need it for! Thanks!!

 

[VBABEGINER]

Hi Mike Sir,

On this, I do have exactly same query. And I really need your assistance to get it solve.

Can you please revert..

Try

Sub test()
    If SheetExists("Smith") Then
        MsgBox "Sheet Smith exists"
    Else
        MsgBox "no sheet named Smith"
    End If
End Sub


Function SheetExists(sheetName as String, optional Wb as workbook) As Boolean
    If Wb Is Nothing then Set Wb = ThisWorkbook
    On Error Resume Next
    SheetExists = (LCase(wb.Sheets(sheetName).Name) = LCase(sheetName))
    On Error Goto 0
Exit Function

 

[mikerickson]

What about that code doesn't work for you?

 

[Fluff]

@VBABEGINER
I have removed you last 3 posts as they are a continuation of this thread. https://www.mrexcel.com/forum/excel-questions/1063146-need-code-edit-2-a.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.

 

[VBABEGINER]

Ok, Many Thanks..

 

[dinunan]

I am also looking for the same code. I've a workbook containing one Master blank sheet. Everyday I want to create a copy of this Master sheet and rename it to today's date (dd-mm-yyyy). Then all previous sheets to be protected except newly created. I've already written a code and it is working fine. Now I want an error handler - say if the sheet is already created for today - the sub should exit else continue. Please suggest.

Sub FinalizeReport()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Master")


ws1.Copy Before:=ThisWorkbook.Sheets(1)
ActiveSheet.Name = _
WorksheetFunction.Text(Now(), "dd-mm-yyyy")

For Each ws In ThisWorkbook.Worksheets


ws.Protect Password:="password"


Next


Sheets(1).Unprotect Password:="password"


End Sub

 

[Fluff]

How about

Sub FinalizeReport()
Dim ws1 As Worksheet
Dim i As Long
Dim Nme As String
Set ws1 = ThisWorkbook.Worksheets("Master")

Nme = format(Date, "dd-mm-yyyy")
If Evaluate("isref('" & Nme & "'!A1)") Then Exit Sub

ws1.Copy Before:=ThisWorkbook.Sheets(1)
ActiveSheet.name = Nme

For i = 2 To ThisWorkbook.Worksheets.Count
   Sheets(i).Protect Password:="password"
Next
End Sub