you could change the else statement to test the length for 6 or 16 if you
want to know the length of what they input.

If (Len(Target.Value) < 6) And (Len(Target.Value) < 16) Then _
MsgBox "Invalid Length"

Encapsulate the entire pattern matching test with the first If statement as
follows and you won't get the message box for every change event.

If Not Intersect([Ccard], Target) Is Nothing Then
If [Ccard].Formula Like "######" Then
[Ccard].Value = Format([Ccard].Value, "##xx xxxx xxxx ####")
ElseIf [Ccard].Formula Like "################" Then
[Ccard].Value = Format([Ccard].Value, "#### #### #### ####")
Else: Msgbox "Invalid Data. Please enter a 6 or 16 digit number."
End If
End If

As long as you preformat the cell to text (Format/Cells/Text), you wouldn't
have to change the numberformat to text via code. You could not rely on the
Change event to set the format to text as it would be too late.


"stefan via OfficeKB.com" wrote:


Hi JMB,
I like that. I applied this to my worksheet_change vs. the
Worksheet_SelectionChange event.
i could not figure out how to incorporate it into the
Worksheet_SelectionChange and function properly. (all my columns of the
worksheet expanded to full screen or larger - strange.) when i add it as
shown below to worksheet change it works, as far as i can tell, without any
problems. i am happy with that. thank you for the help.

i was wondering if there might be an option to add a msg - if the value
entered in the range 'ccard' is less then 6 digits, more then 16 or between 6
and 16 digits. when i leave the code
Else
MsgBox "Invalid Number"
in, it prompts at any worksheet_change (obviously).

Thank you
Stefan

....
If Not Intersect([Ccard], Target) Is Nothing Then _
Target.NumberFormat = "@"
If [Ccard].Formula Like "######" Then
[Ccard].Value = Format([Ccard].Value, "##xx xxxx xxxx ####")
ElseIf [Ccard].Formula Like "################" Then
[Ccard].Value = Format([Ccard].Value, "#### #### #### ####")
End If
....



JMB wrote:
I think I see what you mean. The cell will need to be formatted as text
before you key anything in as Excel will convert it right away. You could
ensure this by pasting this into your worksheet module (right click on sheet
tab and paste into the module)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect([Ccard], Target) Is Nothing Then _
Target.NumberFormat = "@"
End Sub

Then try some small changes to Pauls macro:

Sub FormatCcard()
If [Ccard].Formula Like "######" Then
[Ccard].Value = Format([Ccard].Value, "##xx xxxx xxxx ####")
ElseIf [Ccard].Formula Like "################" Then
[Ccard].Value = Format([Ccard].Value, "#### #### #### ####")
Else
MsgBox "Invalid Number"
End If

End Sub

Hi JMB,
Thanks for the suggestion.
[quoted text clipped - 12 lines]

Paul D


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200507/1