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.

 

[dinunan]

Thanks a lot Fluff

Code is working fine but the newly created sheet is also getting protected. As per your code from sheet 2 onwards it should protect but sheet 1 is also protected. Any guess.

Also is there a way to hide the password written in the code? One way is to protect the module from viewing altogether.

 

[Fluff]

My best guess as to why it's getting protected is that Sheets(1) is a hidden sheet.
AFAIK there is no way of hiding the password in the code.
This should resolve the protection issue

Sub FinalizeReport()
Dim ws1 As Worksheet, Ws As Worksheet
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 Each Ws In ThisWorkbook.Worksheets
   If Not Ws.name = Nme Then Ws.Protect Password:="password"
Next
End Sub

 

[dinunan]

I noticed that both of your codes working if Master sheet is unprotected. After first run, when Master gets protected, newly created sheet also getting protected.
Looks like we need to keep Master unprotected always.

 

[Fluff]

In that case try

For Each Ws In ThisWorkbook.Worksheets
   If Not Ws.name = Nme And Not Ws.name = "Master" Then Ws.Protect Password:="password"
Next

 

[dinunan]

Thanks! Working good.

 

[Fluff]

Glad we could help & thanks for the feedback