Allowing Numeric Characters Only In Two Cells

[OAM]

I am using the formula shown below to format the user input number in D6 as ####-####-#### and to ensure only twelve digits (not letters) are entered. However, I need the same function on the same worksheet to happen to D3 but only to ensure nine digits (#########) are entered. This would prevent users from entering letters such as O for zeros or I for ones in either cell.

Is this possible and can anyone help me with this?

[/FONT]
[FONT=Calibri][COLOR=black][FONT=Verdana]Option Explicit<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private Sub Worksheet_Change(ByVal Target As Range)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim myRng As Range<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim myCell As Range<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim iCtr As Long<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set myRng = Me.Range("D6")<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If Intersect(Target, myRng) Is Nothing Then Exit Sub<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]On Error GoTo errHandler:<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Application.EnableEvents = False<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]For Each myCell In Intersect(Target, myRng).Cells<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]With myCell<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If IsEmpty(.Value) Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'do nothing<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]ElseIf Application.IsNumber(.Value) Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox "Please enter Text in: " & .Address(0, 0)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana].ClearContents<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]ElseIf Len(.Value) <> 12 Then 'Must be twelve digits<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox "Information numbers are twelve digits: " & .Address(0, 0)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana].ClearContents<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Else<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]For iCtr = 1 To Len(.Value)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If IsNumeric(Mid(.Value, iCtr, 1)) Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'keep looking<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Else<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox "Numbers Only for the Information Number: " & .Address(0, 0)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana].ClearContents<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Exit For<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Next iCtr<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana].Value = Format(.Value, "0000-0000-0000")<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End With<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Next myCell<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]errHandler:<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Application.EnableEvents = True<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR][/FONT]
[FONT=Calibri][/FONT] 
[FONT=Calibri]

 

[Haseeb Avarakkan]

Hello,

Try Data Validation.

D3,

Data Validation >> Allow, select Custom. Then enter this formula

=AND(LEN(D3)=9,ISNUMBER(D3),D3-INT(D3)=0)

D6,

Data Validation >> Allow, select Custom. Then enter this formula

=AND(LEN(D6)=12,ISNUMBER(D6),D6-INT(D6)=0)

Change the cell format to,

0000-0000-0000

 

[OAM]

Thank you Haseeb Avarakkan for the quick fix! Sometimes I try to make the solution more complicated that is should be.

 

[Peter_SSs]

Instead of a Custom DV, what about a built-in one?

Allow: Whole number
Data: Between
Minimum: 100000000000
Maximum: 999999999999

 

[T. Valko]

Hello,

Try Data Validation.

D3,

Data Validation >> Allow, select Custom. Then enter this formula

=AND(LEN(D3)=9,ISNUMBER(D3),D3-INT(D3)=0)

D6,

Data Validation >> Allow, select Custom. Then enter this formula

=AND(LEN(D6)=12,ISNUMBER(D6),D6-INT(D6)=0)

Change the cell format to,

0000-0000-0000

Just an observation...

If you're testing for INT(...) then also testing for ISNUMBER is redundant.

If the entry is not a number then INT(...) will throw an error and not allow the entry.

 

[Peter_SSs]

=AND(LEN(D3)=9,ISNUMBER(D3),D3-INT(D3)=0)

Another note:

This will allow -12345678 which I doubt fits the intention.

 

[Haseeb Avarakkan]

My bad!. Didn't think about the -ve numbers also using INT & ISNUMBER together. Thank you Peter & Biff.

OAM,

See the peter's suggestion post # 4 is better than mine. Or change the formula to;

=AND(LEN(D3)=9,D3>0,D3-INT(D3)=0)

=AND(LEN(D6)=12,D6>0,D6-INT(D6)=0)

 

[Peter_SSs]

Looking back over this thread I'm not sure we are actually addressing the original issue, though the OP seems to be happy. I guess that we can continue to "talk amongst ourselves" though.

I think the OP was trying to validate "information numbers" that needed to be 12 (or 9 etc) digits. From looking at the code I think those "information numbers" may be allowed to start with leading zeros. If that is the case then all our above attempts to use Data Validation would fail and a vba approach would probably be needed.

So, just for interest and assuming we were trying to validate a 9 digit number without leading zeros as we have been trying to do (ie between 100000000 and 999999999) I thought that I would see if I could 'defeat' Haseeb's latest custom DV formula:

Or change the formula to;

=AND(LEN(D3)=9,D3>0,D3-INT(D3)=0)

I know I am getting really picky now, but ...
It meets all the tests of the DV formula.

Excel Workbook
	D	E	F
3	000000004		TRUE
4			TRUE
5			TRUE
DV

#VALUE!

 

[Haseeb Avarakkan]

Yes. Peter. You are right. If the cell is formatted as text. this will fail. I think +0 to the cell would be fine

=AND(LEN(D3+0)=9,D3+0>0,D3+0-INT(D3+0)=0)

What is your opinion?

 

[Peter_SSs]

Yes. Peter. You are right. If the cell is formatted as text. this will fail. I think +0 to the cell would be fine

=AND(LEN(D3+0)=9,D3+0>0,D3+0-INT(D3+0)=0)

What is your opinion?

My cell was formatted as General, not Text, but I was entering text values.
In one test I entered the value directly in the cell as
'000000004
In another test I entered a formula in the cell
=TEXT(4,"000000000")

Adding a +0 in your DV formula I think works, but it would only be needed in the LEN() part. The others are redundant since the text number is already coerced into a real number during the evaluation of those tests.