Validation VBA function

[b.vasilescu]

Hello all,
this is my first post here and i would appreciate any help or suggestions from you. ok then..

i have a validation function written in vba. the function does one thing it validates a fixed 13digit number. in case of a shorter or longer number it displays a message.

Function Validare_CNP(CNP As String) As String
Dim i As Integer, x As Integer
Dim cnp_array(13) As Integer
If Len(CNP) <> 13 Then
Validare_CNP = "CNP-ul not 13 digits"
Else
For i = 1 To 13
cnp_array(i) = Val(Mid(CNP, i, 1))
Next i
x = (cnp_array(1) * 2 + cnp_array(2) * 7 + cnp_array(3) * 9 + cnp_array(4) * 1 + cnp_array(5) * 4 + cnp_array(6) * 6 + _
cnp_array(7) * 3 + cnp_array(8) * 5 + cnp_array(9) * 8 + cnp_array(10) * 2 + cnp_array(11) * 7 + cnp_array(12) * 9) Mod 11
If x = 10 Then
x = 1
End If
If x = cnp_array(13) Then
Validare_CNP = "Valid"
Else
Validare_CNP = "Invalid"
End If
End If
End Function

the function is bug free, and it's working fine.
let's take 1800510170088 .The function returns a valid result
if you change a digit, the result will be invalid.
ok,

what is the problem...
the problem is i cannot figure out how can i use this function in a worksheet and when i press enter in a cell from column A, the excel to show me if i've entered an invalid number.
Thank you
p.s. excuse my poor english.

 

[Andrew Poulsom]

Welcome to MrExcel.

If your number is in A1, in B1 enter:

=Validare_CNP(A1)

 

[b.vasilescu]

i meant to say that after i enter that 13digits number in a cell, validation result should appear on screen in form of a message box.

what you said it's ok, but i have to enter 200-300 numbers on a column, and validation should be instant after i press enter.

i tried using a formula in data..custom validation.. formula (from menu), but it's too long.

 

[Andrew Poulsom]

Unfortunately you can't use a User Defined Function in Data Validation. You could use the Worksheet_Change event procedure to perform the validation for you.