Jet Fusion
Member
Hi
Please can someone help, somewhere I've changed something now I keep getting an error code - Compile error - Sub or Function not defined
Thanks in advance
Jet
Please can someone help, somewhere I've changed something now I keep getting an error code - Compile error - Sub or Function not defined
Code:
Sub AddNewWorksheet()
Const cstrTitle As String = "Add new worksheet"
Const cstrPrompt As String = "Give the name for the new worksheet." & vbCrLf & "Not allowed are the characters: : \ / ? * [ and ]"
Dim strInput As String
Dim strDefault As String: strDefault = ""
Dim strInputErrorMessage As String
Dim booValidatedOk As Boolean: booValidatedOk = False
On Error GoTo HandleError
Do
strInput = InputBox(Prompt:=cstrPrompt, Title:=cstrTitle, Default:=strDefault)
If Len(strInput) = 0 Then GoTo HandleExit
GoSub ValidateInput
If Not booValidatedOk Then
If vbCancel = MsgBox(strInputErrorMessage & "Retry?", vbExclamation + vbOKCancel) Then GoTo HandleExit
End If
Loop While Not booValidatedOk
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim shts As Sheets: Set shts = wb.Sheets
Dim obj As Object
Set obj = shts.Add(Before:=ActiveSheet, Count:=1, Type:=XlSheetType.xlWorksheet)
obj.Name = strInput
HandleExit:
Exit Sub
HandleError:
MsgBox Err.Description
Resume HandleExit
ValidateInput:
If SheetExists(strSheetName:=strInput) Then
strInputErrorMessage = "Sheet already exists. "
ElseIf Not IsValidSheetName(strSheetName:=strInput) Then
strInputErrorMessage = "Sheetname not allowed. "
Else
booValidatedOk = True
End If
Return
End Sub
Thanks in advance
Jet