Microsoft Office Tutorials and References
In Depth Information
“Worksheet “ & mySheetName & “ was added.”, , “Thank you.”
“A worksheet named “ & mySheetName & “ already exists.”, _
“Duplicate sheet names are not allowed.”
My preference for most situations where runtime errors can occur is to avoid the error handler
route because the GoTo statement makes the macro more difficult to follow. Using an error bypass
approach with the On Error Resume Next statement, you can test for the condition of the Error
object, and use an If structure to deal with either possibility.
When it comes to naming a sheet, several considerations need to be monitored:
Does the sheet name already exist in the workbook? Duplicate sheet names are not allowed.
Is the proposed sheet name more than the maximum allowable 31 characters in length?
Are any illegal sheet-naming characters included in the proposed name? Sheet tab names
cannot contain the characters /, \, [, ], *, ?, or :. If you try to type any of those characters into
your sheet tab, Excel will disallow the entry.
The Following macro takes these possibilities into consideration. If all conditions are met, a new
sheet is added. If any condition is not met, a new worksheet will not be created and a Message Box
will inform you of the reason why.
‘Declare String type variables for naming and testing the sheet.
Dim mySheetName As String, mySheetNameTest As String
‘Use an InputBox to ask the user to propose a new sheet name.
mySheetName = _
InputBox(“Enter the worksheet name:”, _
“Add and name a new worksheet”)
‘Exit if nothing was entered or the Cancel button was clicked.
If mySheetName = “” Then Exit Sub
‘Error bypass if the proposed sheet name already exists
‘in the workbook.
On Error Resume Next
mySheetNameTest = Worksheets(mySheetName).Name
If Err.Number = 0 Then
“The sheet named “ & mySheetName & “ already exists.”, _