number length with a id number only

[rob]

Hello everyone,
I've been working on a project and I can't seem to get the formula or
worksheet change correct.. In cell D3 (which is where the employee will
enter his/her ID #), I need to limit the number length to 8 digits no less no
more and it must be only numbers, no letters or characters and it needs to
allow a zero in the front or the 8 digits. Can someone help me with this
problem? I'm learning visual basic but I am still very 'green' and confused
with the format needed.

thank you
rob

 

[Bernie Deitrick]

Rob,

Format D3 for Text.

You can use the Custom Data Validation formula

=AND(LEN(D3)=8,NOT(ISERROR(VALUE(D3))))

In VBA, that would be

If Len(Range("D3").Value) = 8 And _
Not (IsError(CLng(Range("D3").Value))) Then
MsgBox "It's good"
Else
MsgBox "It's bad"
End If

HTH,
Bernie
MS Excel MVP