Microsoft Office Tutorials and References
In Depth Information
Bypassing Errors
“A new sheet was not added.”
Exit Sub
End If
‘If the length of the proposed sheet name exceeds 31 characters,
‘disallow the attempt.
If Len(mySheetName) > 31 Then
MsgBox _
“Worksheet tab names cannot exceed 31 characters.” & vbCrLf & _
“You entered “ & mySheetName & “, which has “ & vbCrLf & _
Len(mySheetName) & “ characters.”, vbInformation, _
“Please use no more than 31 characters.”
Exit Sub
End If
‘Sheet tab names cannot contain
‘the characters /, \, [, ], *, ?, or :.
‘Verify that none of these characters
‘are present in the cell’s entry.
Dim IllegalCharacter(1 To 7) As String, i As Integer
IllegalCharacter(1) = “/”
IllegalCharacter(2) = “\”
IllegalCharacter(3) = “[“
IllegalCharacter(4) = “]”
IllegalCharacter(5) = “*”
IllegalCharacter(6) = “?”
IllegalCharacter(7) = “:”
‘Loop through each character in the proposed sheet name.
For i = 1 To 7
If InStr(mySheetName, (IllegalCharacter(i))) > 0 Then
MsgBox _
“You included a character that Excel does not allow” & vbCrLf & _
“when naming a sheet. Please re-enter a sheet name” & vbCrLf & _
“without the ‘’” & IllegalCharacter(i) & “‘’ character.”, _
vbCritical, _
“Sheet not added.”
Exit Sub
End If
Next i
‘History is a reserved word, so a sheet cannot be named History.
If UCase(mySheetName) = “HISTORY” Then
MsgBox “A sheet cannot be named “ & mySheetName & vbCrLf & _
“because it is a reserved word in Excel.”, vbInformation, _
“History is a reserved word.”
Exit Sub
End If
‘Inform the user that a new sheet has been added.
Worksheets.Add.Name = mySheetName
MsgBox “A new sheet named “ & mySheetName & “ has been added!”, _
vbInformation, _
“Thank you !”
End Sub
Search JabSto ::




Custom Search