VBA Macro to check if sheet name exists

Question

I'm currently running a macro that copies a template sheet, takes user's inputs, renames the sheet, and places the user inputs in the sheet.

Everything seems to be working except for the function that checks if the Sheet name already exists. The actual "Sheet already exists" works, however, before prompting me with this error it duplicates the TEMPLATE worksheet for some reason.

Here's the code for the actual macro (affected area is: Sheets("TEMPLATE").Copy After:=Sheets("TEMPLATE"))

'============================================================================
'Button to load new GSA Form
'============================================================================
Private Sub CommandButton1_Click()

Const cstrTitle As String = "Create a new GSA worksheet"
Const cstrPrompt As String = "Enter the GSA number for the new worksheet"
Dim projName As String
Dim projAddress As String
Dim projDate As Date
Dim strInput As Variant                                     'Input value from user (GSA Number)
Dim strDefault As String: strDefault = ""                   'Sets default value for inputbox
Dim strInputErrorMessage As String                          'Error message
Dim booValidateOK As Boolean: booValidateOK = False
On Error GoTo HandleError

Do
    strInput = ActiveSheet.Range("C9").Value
    projName = ActiveSheet.Range("C6").Value
    projAddress = ActiveSheet.Range("C7").Value
    projDate = ActiveSheet.Range("C8").Value

    If Len(strInput) = 0 Then GoTo HandleExit
    GoSub ValidateInput
    If Not booValidateOK Then
        If vbCancel = MsgBox(strInputErrorMessage & "Retry?", vbExclamation + vbOKCancel) Then GoTo HandleExit
    End If
Loop While Not booValidateOK

Sheets("TEMPLATE").Copy After:=Sheets("TEMPLATE")                          'Copy Template Sheet, places the copy after the template sheet
ActiveSheet.Name = strInput                                                 'Renames the new sheet to the user's input

ActiveSheet.Range("C5").Value = projName                                    'Inputs Project Name to new sheet
ActiveSheet.Range("C6").Value = projAddress                                 'Inputs Project Address to new sheet
ActiveSheet.Range("C7").Value = projDate                                    'Inputs Project Date to new sheet
ActiveSheet.Range("C8").Value = strInput                                    'Inputs GSA # to new sheet

ThisWorkbook.Worksheets("MASTER").Range("C6").Value = ""   'name'           'Deletes inputs from MASTER sheet
ThisWorkbook.Worksheets("MASTER").Range("C7").Value = ""   'address'
ThisWorkbook.Worksheets("MASTER").Range("C8").Value = ""   'date'
ThisWorkbook.Worksheets("MASTER").Range("C9").Value = ""   'gsa #'



HandleExit:
Exit Sub
HandleError:
MsgBox Err.Description
Resume HandleExit

ValidateInput:
If SheetExists(strSheetName:=strInput) Then
    strInputErrorMessage = "Sheet already exists. "
Else
    booValidateOK = True
End If
Return
End Sub

Here's the function that checks to see if the sheet already exists

Public Function SheetExists(strSheetName As Variant, Optional wbWorkbook As Workbook) As Boolean
If wbWorkbook Is Nothing Then Set wbWorkbook = ActiveWorkbook           'or ThisWorkbook - whichever appropriate
Dim obj As Object
On Error GoTo HandleError
Set obj = wbWorkbook.Sheets(strSheetName)
SheetExists = True
Exit Function
HandleError:
 SheetExists = False
End Function

I'm assuming the plan is to get strInput from an InputBox and you haven't added that functionality yet? As is the user has no way to really retry... — BigBen
Sorry, I didn't specify. The strInput is taken from a cell in the worksheet — Nikolajs
I saw that. My point being that you never give the user the option to change that value if the sheet exists, or if the cell is blank - so why loop? — BigBen

JC Guidicelli JC Guidicelli · Accepted Answer · 2018-06-26T19:16:38

Try this :

Function sheetExists(sheetToFind As String) As Boolean
    sheetExists = False
    For Each sheet In Worksheets
        If sheetToFind = sheet.name Then
            sheetExists = True
            Exit Function
        End If
    Next sheet
End Function

And use like this :

if sheetExists("TEMPLATE") = true then
  'your code
else
  'code
end if

Excel VBA If WorkSheet("wsName") Exists

VBA Macro to check if sheet name exists

1 Answers