restricting use of certain characters
 

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

restricting use of certain characters
 

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

restricting use of certain characters
 

Thanks Zygan.

Works great!

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

Rob

"Zygan" wrote in
message ...

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

restricting use of certain characters
 

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

restricting use of certain characters
 

Thanks again Zyrgan. That was rather obvious, eh!

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

Rob

"Zygan" wrote in
message ...

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