Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i rename a sheet if the rename tab is inactive? | Excel Worksheet Functions | |||
Removing character in a string | Excel Discussion (Misc queries) | |||
Removing a character | Excel Worksheet Functions | |||
In excell, I put an invalid character on a worksheet name tab, no. | New Users to Excel | |||
Removing invalid characters from proposed sheet name | Excel Programming |