Macro (or data Vlaidation) to force a six digit number into a cell

[tonywatsonhelp]

Hi Everyone.

Ok so I've used Data Validation to set a cell value but its not quite right.
please help with a macro or advice.

What I want is simple.

Cell R14 must contain 6 numbers or they can not enter it.

So with data validation I did Text length 6 and set the cell to Text.
this works great as some numbers have zeros for example "009876" is fine

however this does not stop other character so "SS9876" or even ".--987" is getting through.

So I only want them to be able to type in numbers and it must be 6 numbers.
please help if you can

Tony

 

[Joe4]

You can do it using the method I show here: https://www.mrexcel.com/forum/excel-questions/854858-data-validation-number-leading-zero.html

So the Custom Data Validation formula would look like:
=AND(ISNUMBER(R14+0),LEN(R14)=6)

 

[tonywatsonhelp]

Thank you joe this is perfect
Tony

 

[Joe4]

You are welcome!

 

[Rick Rothstein]

You can do it using the method I show here: https://www.mrexcel.com/forum/excel-questions/854858-data-validation-number-leading-zero.html

So the Custom Data Validation formula would look like:
=AND(ISNUMBER(R14+0),LEN(R14)=6)

This formula is not fully robust for requiring the entry of a 6-digit number as it will allow the user to enter values like 12.345 or -12345 or (12345) or 1.23E5 or 1234E2 or 56 7/8 and so on. And, of course, Data Validation will not stop a user from copy/pasting in anything they want into the cell.

The following event procedure should work correctly in allowing only a 6-digit number to be entered into cell R14...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "R14" Then
    If Not Target.Value Like "######" and Target.Value <> "" Then
      Range("R14").Select
      MsgBox "The only valid entry into cell R14 is a 6-digit number!", vbCritical
      Application.Undo
    End If
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.