restricting use of certain characters

[Rob]

Hi,

I'd like to know how to restrict entry of certain characters (those that
windows won't allow when naming a file, particularly the / sign)

EG, the person may want to enter 07/77147 into a cell and I want either a
message to appear saying the / is invalid or, with an option for the / to be
automatically changed to a -.

Is this possible?

Rob

 

[Zygan]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells.Replace What:="/", Replacement:="-", LookAt:=xlPart, SearchOrder
_
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

End Sub


place this in the workbook selection change macro it will continually
search the document and replace / with -
hope this helps

 

[Rob]

Thanks Zygan.

Works great!

Can you provide an amendment so that this works only for a cell or a range
of cells?

Rob

 

[Zygan]

sorry for the late reply

Just replace the word "cells." at the start of the macro to
RANGE("?1:?1000").

e.g for column D only write
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

range("D").Replace What:="/", Replacement:="-", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

End Sub


e.g for A1 : A 5
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

range("A1:A5").Replace What:="/", Replacement:="-", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

End Sub


cheers.

 

[Rob]

Thanks again Zyrgan. That was rather obvious, eh!

I'll put my thinking cap on before asking an obvious q next time.

Rob