Rename sheet by removing any invalid character
 

I'm currently using macro below to remove / as invalid character and
keep the character limit to 31. However i would also like to add the
other invalid characters ? \ [ ] * so that i can provide for more
invalid characters - Pls help thxs

Sub SheetNameActivecell()

Application.ActiveSheet.Name = Left(Application.Substitute
(ActiveCell.Value, "/", ""), 31)

End Sub

Rename sheet by removing any invalid character
 

Sub SheetNameActivecell()
Dim BadSym(6)
BadSym(1) = "/"
BadSym(2) = "\"
BadSym(3) = "?"
BadSym(4) = "["
BadSym(5) = "]"
BadSym(6) = "*"
NewName = ActiveCell.Value
'MsgBox NewName
For k = 1 To 6
NewName = Replace(NewName, BadSym(k), "")
'MsgBox NewName
Next k
Application.ActiveSheet.Name = Left(NewName, 31)
End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"al" wrote in message
...
I'm currently using macro below to remove / as invalid character and
keep the character limit to 31. However i would also like to add the
other invalid characters ? \ [ ] * so that i can provide for more
invalid characters - Pls help thxs

Sub SheetNameActivecell()

Application.ActiveSheet.Name = Left(Application.Substitute
(ActiveCell.Value, "/", ""), 31)

End Sub

Rename sheet by removing any invalid character
 

Here is a function you can use to "fix" the proposed name by purging it of
the bad characters and truncating the proposed name to no more than 31
characters..

Function FixedName(ProposedName As String) As String
Dim V As Variant
FixedName = ProposedName
For Each V In Array("\", "/", "?", "*", "[", "]")
FixedName = Replace(FixedName, V, "")
Next
FixedName = Left(FixedName, 31)
End Function

So, after installing this function (probably best to put it into a Module...
click Insert/Module from the VB menu bar), just do this in your code...

ActiveSheet.Name = FixedName(ActiveCell.Value)

--
Rick (MVP - Excel)


"al" wrote in message
...
I'm currently using macro below to remove / as invalid character and
keep the character limit to 31. However i would also like to add the
other invalid characters ? \ [ ] * so that i can provide for more
invalid characters - Pls help thxs

Sub SheetNameActivecell()

Application.ActiveSheet.Name = Left(Application.Substitute
(ActiveCell.Value, "/", ""), 31)

End Sub

Rename sheet by removing any invalid character
 

On Jan 13, 9:30 pm, "Rick Rothstein"
wrote:
Here is a function you can use to "fix" the proposed name by purging it of
the bad characters and truncating the proposed name to no more than 31
characters..

Function FixedName(ProposedName As String) As String
Dim V As Variant
FixedName = ProposedName
For Each V In Array("\", "/", "?", "*", "[", "]")
FixedName = Replace(FixedName, V, "")
Next
FixedName = Left(FixedName, 31)
End Function

So, after installing this function (probably best to put it into a Module...
click Insert/Module from the VB menu bar), just do this in your code...

ActiveSheet.Name = FixedName(ActiveCell.Value)

--
Rick (MVP - Excel)

"al" wrote in message

...

I'm currently using macro below to remove / as invalid character and
keep the character limit to 31. However i would also like to add the
other invalid characters ? \ [ ] * so that i can provide for more
invalid characters - Pls help thxs

Sub SheetNameActivecell()

Application.ActiveSheet.Name = Left(Application.Substitute
(ActiveCell.Value, "/", ""), 31)

End Sub

thxs

Rename sheet by removing any invalid character
 

On Jan 13, 9:14 pm, "Bernard Liengme"
wrote:
Sub SheetNameActivecell()
Dim BadSym(6)
BadSym(1) = "/"
BadSym(2) = "\"
BadSym(3) = "?"
BadSym(4) = "["
BadSym(5) = "]"
BadSym(6) = "*"
NewName = ActiveCell.Value
'MsgBox NewName
For k = 1 To 6
NewName = Replace(NewName, BadSym(k), "")
'MsgBox NewName
Next k
Application.ActiveSheet.Name = Left(NewName, 31)
End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme

"al" wrote in message

...

I'm currently using macro below to remove / as invalid character and
keep the character limit to 31. However i would also like to add the
other invalid characters ? \ [ ] * so that i can provide for more
invalid characters - Pls help thxs

Sub SheetNameActivecell()

Application.ActiveSheet.Name = Left(Application.Substitute
(ActiveCell.Value, "/", ""), 31)

End



thxs