Rename sheet by removing any invalid character

[al]

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

 

[Bernard Liengme]

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

 

[Rick Rothstein]

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)

 

[al]

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)

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

 

[al]

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

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