How to resolve run time error "1004" name more than 31 charaters long

Here's some Functions to check sheet names

Test if name contains too many characters

Private Function TooLong(s As String) As Boolean
    TooLong = False
    If Len(s) > 31 Then
        TooLong = True
        Exit Function
    End If
End Function

Test if name contains any illegal characters

Private Function BadName(s As String) As Boolean
    BadName = False
    Dim iBadCharsCount As Integer
    iBadCharsCount = InStr(1, s, ":") + InStr(1, s, "\") + InStr(1, s, "/") + _
                     InStr(1, s, "?") + InStr(1, s, "*") + InStr(1, s, "[") + InStr(1, s, "]")
    If iBadCharsCount > 0 Then
        BadName = True
    End If
End Function

Combined to check all possibilities

Private Function BadName(s As String) As Boolean
    BadName = False
    Dim iBadCharsCount As Integer
    iBadCharsCount = InStr(1, s, ":") + InStr(1, s, "\") + InStr(1, s, "/") + _
                     InStr(1, s, "?") + InStr(1, s, "*") + InStr(1, s, "[") + InStr(1, s, "]")
    
    If iBadCharsCount > 0 Or  Len(s) > 31 Then
        BadName = True
    End If
End Function

Select allOpen in new window

Incorporate it into your code something like this

Sub x()
If BadName("name you have here") Then
MsgBox "That name cannot be used", vbCritical, "Error"
End If
End Sub

Select allOpen in new window

I know some of the school names are too long so I need help in write a code to shorten the name of the schools if possible before I review the list of schools manually. Are you able to help?

I know some of the school names are too long so I need help in write a code to shorten the name of the schools if possible before I review the list of schools manually

since the school name could longer that the characters allowed for the worksheet name, you may try to use a shorter code name or other unique identifier to represent the worksheet name instead.

You can simply truncate name:
If Len(sheetName)>31 Then sheetName = Left$(sheetName, 31)
But IMHO it's better create one more sheet (placing it in the first place) with abbreviations and fullnames columns and name worksheets with abbreviated names.

just a note,... you may also want to check the uniqueness of worksheet name in case you decided to truncate the worksheet name, else it may generate another runtime error (same worksheet name).

Thank you I have resolved this issue by using left() and mid().

I would suggest that you reduce the names to comprehensible abbreviations. The advantages are:

Easy to pick up at first glance
Less chances of duplication
Have more tabs visible in the window

It would also be a good idea to have a separate sheet for sheet names and abbreviations for easy recollection in case you forget the abbreviation. This sheet can also be used to "Go to" any school sheet.

Also, there is a Windows API that can be used to shorten names.  It introduces an ellipsis into the shortened string.

>>there is a Windows API

Private Declare Function PathCompactPathEx Lib "shlwapi" Alias "PathCompactPathExA" _
        (ByVal pszOut As String, _
         ByVal pszSrc As String, _
         ByVal cchMax As Long, _
         ByVal dwFlags As Long) As Long
Private Sub Command1_Click()
    Dim sIn As String, sOut As String, max_length As Long
    sIn = "This is a very long string with length exceeding 31 char"
    max_length = 31
    sOut = String(max_length + 1, Chr(0)) '+1 for null char this API appends at the end
    PathCompactPathEx sOut, sIn, max_length + 1, 0
    sOut = Left$(sOut, max_length)
    Debug.Print sOut
End Sub

Select allOpen in new window

VB internal string functions can do this too. This API can be usefull with real path - it truncates middle part of string leaving last path separator and file name.

Thank you for your help. This code has helped my problem.

Pleased to help