Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I control the length of characters a code will return? | Excel Discussion (Misc queries) | |||
Formula Needed to Omit Characters | Excel Worksheet Functions | |||
Formula to replace invalid filename characters | Excel Worksheet Functions | |||
Help - Can't directly input Japanese characters into cell | Excel Discussion (Misc queries) | |||
HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions |