Const mExcelWorkSheetNameLen_Lim As Long = 29 'Error 1004 says 31, but
experimentation suggests 29 is the actual limit

Public Function LegalWorksheetName(ByVal theWorkSheetName As String) As
String
debugStackPush mModuleName & ": LegalWorksheetName"
On Error GoTo LegalWorksheetName_Err

' PURPOSE: To make sure that a proposed worksheet name is legal per Excel's
standards
' ACCEPTS: The string to be converted
' RETURNS: A string with any illegal characters replaced with underscores
'
' NOTES: 1) We originally chose which chars to replace from the following
' error thrown by Excel when we tried to feed it
' an unacceptable name and then added "^" and dropped the length
limit
' from experience...
' -----------------------------------------------------------------
' Error 1004: While renaming a sheet or chart, you entered an
invalid name.
' Try one of the following:
' ? Make sure the name you entered does not exceed
31 characters.
' ? Make sure the name does not contain any of the
following
' characters: : \ / ? * [ or ]
' ? Make sure you did not leave the name blank.

Dim myBadBoyz() As Variant

Dim i As Integer
Dim myWorkSheetName As String
Dim myArraySize As Long
Dim myRemainder As Long
Dim fGood As Boolean
Dim sh As Worksheet
Dim iSheet As Long

Const myBenignChar As String = "x"
Const myTrimLen_Left As Long = 15
Const myTrimLen_Rite As Long = mExcelWorkSheetNameLen_Lim - myTrimLen_Left

myBadBoyz = Array(":", "\", "/", "?", "*", "[", "]", "^")
myArraySize = UBound(myBadBoyz)

myWorkSheetName = theWorkSheetName
For i = 0 To myArraySize
myWorkSheetName = Replace(myWorkSheetName, myBadBoyz(i), myBenignChar)
Next i

If Len(myWorkSheetName) <= mExcelWorkSheetNameLen_Lim Then
LegalWorksheetName = myWorkSheetName
Else
LegalWorksheetName = Left$(myWorkSheetName, myTrimLen_Left) & _
Right$(myWorkSheetName, myTrimLen_Rite)
End If

On Error Resume Next
iSheet = 0
Do
Set sh = Nothing
Set sh = Worksheets(LegalWorksheetName)
fGood = sh Is Nothing
If Not fGood Then
iSheet = iSheet + 1
LegalWorksheetName = Left(LegalWorksheetName, _
Application.Min(mExcelWorkSheetNameLen_Lim -
Len(CStr(iSheet)), _
Len(LegalWorksheetName))) & iSheet
End If
Loop Until fGood

LegalWorksheetName_xit:
DebugStackPop
On Error Resume Next
Exit Function

LegalWorksheetName_Err:
BugAlert True, ""
Resume LegalWorksheetName_xit
End Function



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"(PeteCresswell)" wrote in message
...
I'm almost done writing a little "LegalWorkSheet()" function that accepts
the
proposed worksheet name and returns a version of it that's guaranteed not
to
trap out when assigned to a worksheet.

One thing I haven't done yet is to iterate through all existing sheets to
see if
any are the same as the one I just concocted and modify the new one if
needed...and then check again.

Can anybody point me to some other code that does this - perhaps in a more
elegant and correct way?

Here's what I have so far:
---------------------------------------

Const mExcelWorkSheetNameLen_Lim As Long = 29 'Error 1004 says 31,
but
experimentation suggests 29 is the actual limit

Public Function LegalWorksheetName(ByVal theWorkSheetName As String) As
String
debugStackPush mModuleName & ": LegalWorksheetName"
On Error GoTo LegalWorksheetName_Err

' PURPOSE: To make sure that a proposed worksheet name is legal per
Excel's
standards
' ACCEPTS: The string to be converted
' RETURNS: A string with any illegal characters replaced with underscores
'
' NOTES: 1) We originally chose which chars to replace from the
following
' error thrown by Excel when we tried to feed it
' an unacceptable name and then added "^" and dropped the
length
limit
' from experience...

-----------------------------------------------------------------
' Error 1004: While renaming a sheet or chart, you entered an
invalid name.
' Try one of the following:
' ? Make sure the name you entered does not
exceed 31
characters.
' ? Make sure the name does not contain any of
the
following characters: : \ / ? * [ or ]
' ? Make sure you did not leave the name blank.

Dim myBadBoyz() As Variant

Dim i As Integer
Dim myWorkSheetName As String
Dim myArraySize As Long
Dim myRemainder As Long

Const myBenignChar As String = "x"
Const myTrimLen_Left As Long = 15
Const myTrimLen_Rite As Long = mExcelWorkSheetNameLen_Lim - myTrimLen_Left

myBadBoyz = Array(":", "\", "/", "?", "*", "[", "]", "^")
myArraySize = UBound(myBadBoyz)

myWorkSheetName = theWorkSheetName
For i = 0 To myArraySize
myWorkSheetName = Replace(myWorkSheetName, myBadBoyz(i), myBenignChar)
Next i

If Len(myWorkSheetName) <= mExcelWorkSheetNameLen_Lim Then
LegalWorksheetName = myWorkSheetName
Else
LegalWorksheetName = Left$(myWorkSheetName, myTrimLen_Left) &
Right$(myWorkSheetName, myTrimLen_Rite)
End If

LegalWorksheetName_xit:
DebugStackPop
On Error Resume Next
Exit Function

LegalWorksheetName_Err:
BugAlert True, ""
Resume LegalWorksheetName_xit
End Function
---------------------------------------
--
PeteCresswell