Removing invalid characters from proposed sheet name

[Guest]

I am wanting to enter data on a sheet and then rename the sheet as the cell contents of a particular cell on the sheet. by programmatically pasting the cell contents as the sheetname. Trouble is the contents of the sheet are set by users and they have used chracters such as / or \ within the cell. Is there a simple way of removing such characters?

TIA

 

[Bob Flanagan]

newName = Application.Substitute(activecell.value, "/", "")

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

I am wanting to enter data on a sheet and then rename the sheet as the

cell contents of a particular cell on the sheet. by programmatically
pasting the cell contents as the sheetname. Trouble is the contents of the
sheet are set by users and they have used chracters such as / or \ within
the cell. Is there a simple way of removing such characters?

 

[Guest]

that is pretty limited

Try something like this: In the Worksheets Change event put in this code

Private Sub Worksheet_Change(ByVal Target As Range
Set isect = Application.Intersect(Target, Range("a1")
If Not isect Is Nothing and Target.Value <> "" The
for x = 1 to Len(Target.Value
If Mid(Target, x,1) Not Like "[A-Za-z0-9]" the
Msgbox "Use Letters and Numbers Only!
Target.Selec
Target.Value=""
End I
End i
End Sub
----- Bob Flanagan wrote: ----


newName = Application.Substitute(activecell.value, "/", ""

Bob Flanaga
Macro System
http://www.add-ins.co
Productivity add-ins and downloadable books on VB macros for Exce

I am wanting to enter data on a sheet and then rename the sheet as th

cell contents of a particular cell on the sheet. by programmaticall
pasting the cell contents as the sheetname. Trouble is the contents of th
sheet are set by users and they have used chracters such as / or \ withi
the cell. Is there a simple way of removing such characters