Well, you can use the same approach to find the invalid character,
however inefficient it may be....
For cnt = 1 to Len(Name)
ws.name = Mid(Name,cnt,1)
if err.Number = 1004 then Exit For
Next
Name = Left(Name,cnt - 1) & "_" & Right(Name,Len(Name) - cnt)

Maybe one of the MVP's will have a better solution but that should get
you going.

Charles

Drew Lettington wrote:
Thanks for the reply but just knowing the proposed worksheet name is invalid
isn't helpful. The code needs to create a valid name. For example, the user
provides a name of abc/def and my code converts it to abc_def. My code needs
to know which characters are invalid so they can be replaced.

- Drew

"Die_Another_Day" wrote:

Try setting an object to the worksheet that you want to name, then use
error checking to test if the name was valid or not. Something like
this:
Sub TestName()
Dim Name As String
Dim ws As Object
Name = InputBox("Test Name")
On Error Resume Next
Set ws = Sheets(2)
ws.Name = Name
If Err.Number = 1004 Then '1004 is the error number returned for
invalid charactor
MsgBox "Invalid Name"
End If
On Error GoTo 0
End Sub

HTH

Charles
Drew Lettington wrote:
I have some code that creates a new worksheet from a name provided by a user.
The code checks the user input for invalid characters before creating the
name, eliminating : @ \ / ? * [ ]. The code worked fine running English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained the yen
(￥) character. My code checked for \ which is the directory path separator
in Japanese and displays as the yen symbol. But since the user's name had
the actual yen character my code failed to create a valid worksheet name.

I could modify my code to explicitly check for the yen character and it
would fix the problem. However, yen character is valid in English Excel so I
don't want to replace it in that environment. I also can't just check thread
locale or regional settings or something similar as English Excel could be
running on a Japanese system.

My question then, is it possible to programatically determine at runtime
which characters are invalid in worksheet names? For example, is there an
Excel call that would return a list of invalid characters which I could then
removed from any proposed worksheet names?

- Drew