validate phone number

[sarah]

hi,

how can i make sure the user enters a phone number.

i know u can go to format>cells>general and select
special>phone number

BUT.. this only formats it to a phone number. how do I
ensure the user enters in only 10 digits?? and then also
put it in the nice 'phone number' format (###) ###-####

Help!

 

[Tom Ogilvy]

if Len(ActiveCell) = 10 then
if isnumeric(activeCell) then
activecell.Numberformat:="(###) ###-####"
else
msgbox "Must be a number"
end if
msgbox "Must be 10 digits"
End if

 

[sarah]

thanks!

umm....but another probably question...

where do i put this code?? does it go in the macro or
something :S

 

[Guest]

Think you missed an Else out after first endif?

-----Original Message-----
if Len(ActiveCell) = 10 then
if isnumeric(activeCell) then
activecell.Numberformat:="(###) ###-####"
else
msgbox "Must be a number"
end if
msgbox "Must be 10 digits"
End if

--
Regards,
Tom Ogilvy




.

 

[Tom Ogilvy]

Your right

if Len(ActiveCell) = 10 then
if isnumeric(activeCell) then
activecell.Numberformat:="(###) ###-####"
else
msgbox "Must be a number"
end if
Else
msgbox "Must be 10 digits"
End if

 

[Tom Ogilvy]

It depends on when you want it to work and where you want it to work. I
have no way of knowing what you are doing.

Do you want it to run when a person enters a number in a cell in a specific
column. then you would use the change event
Right click on the sheet tab and select view code

in the module, at the top select worksheet and change

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count >1 then exit sub
if Target.column = 4 then
if Len(Target.Value) = 10 then
if isnumeric(Target.Value) then
activecell.Numberformat:="(###) ###-####"
else
msgbox "Must be a number"
end if
Else
msgbox "Must be 10 digits"
End if
End Sub