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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Zygan
> ------------------------------------------------------------------------
> Zygan's Profile:
> http://www.excelforum.com/member.php...o&userid=34423
> View this thread: http://www.excelforum.com/showthread...hreadid=548822
>

[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: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

"Zygan" <[email protected]> wrote in
message news:[email protected]...
>
> 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: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.
>
>
> --
> Zygan
> ------------------------------------------------------------------------
> Zygan's Profile:
> http://www.excelforum.com/member.php...o&userid=34423
> View this thread: http://www.excelforum.com/showthread...hreadid=548822
>