create function and apply it in datavalidation formula box

[hamed_]

hi everybody ,,,
i have created a function named check_ punctuation like this :

Function check_punctuation(my_str As String) As Boolean
Dim asci_code
Dim str_temp
For i = 1 To Len(my_str)
asci_code = Asc(Mid(my_str, i, 1))
Select Case asci_code
Case 32 To 47
check_punctuation = True
Case 123 To 126
check_punctuation = True
Case 91 To 96
check_punctuation = True
Case 61
check_punctuation = True
Case 64
check_punctuation = True
Case Else
check_punctuation = False

End Select

Next

End Function

this function work fine and return true or false , but whenever i use it in DataValidation , Custom box excel message alert for every data entry.

plz helpppppppp
with best wishes for all my friends ...

 

[jasonb75]

As far as I'm aware, you can't use a udf for validation. There should be an = sign in front of the validation formula, but if you try to enter one with a udf then it will be rejected.

Try creating a named range that refers to =check_punctuation($A1) then use =myname=FALSE as the validation rule, where myname refers to the named range that you created.

 

[hamed_]

thanks for your time....

Try creating a named range that refers to =check_punctuation($A1)

do you mean from range? you mean range function?

 

[jasonb75]

Go to the Formulas tab on the excel ribbon, then Name Manager. Click the 'New' button and enter the details as below. (If your sheet is not named Sheet1 then edit as necessary).

Name:- rng_check_punctuation
Scope:- Sheet1
Refers to:- =check_punctuation(Sheet1!$A1)

Then set your conditional formatting formula as

=rng_check_punctuation

 

[hamed_]

hi thanks for your answer , i did this but i created a Name manager as you told for another example function , its my function code :

Option Compare Text

Function IS_Alphabet(mystr) As Boolean
Dim i As Integer
Dim my_ascicode
Dim temp_boolean As Boolean
For i = 1 To Len(mystr)
my_ascicode = Asc(Mid(mystr, i, 1))
Select Case my_ascicode
Case 32 To 47
IS_Alphabet = False
Exit Function
Case 58 To 59
IS_Alphabet = False
Exit Function
Case 91 To 96
IS_Alphabet = False
Exit Function
Case 123 To 126
IS_Alphabet = False
Exit Function
IS_Alphabet = False
Exit Function
Case 64
IS_Alphabet = False
Exit Function
Case 61
IS_Alphabet = False
Exit Function
Case Else
IS_Alphabet = True
End Select
Next
End Function

i test my function which worked correctly .

next , i created a Name manager for my function as you told :

next i applied it in DataValidation Custom box but it appears an error :

i dont know what is the problem , as you see the function works correctly but the problem is here in datavalidation box...
thanks

 

[RoryA]

You're applying the validation from A2 downward, so the formula should refer to $A2 not $A1

 

[jasonb75]

You're applying the validation from A2 downward, so the formula should refer to $A2 not $A1

Where are you seeing it start in A2, Rory? All the screen captures appear to start in A1.

The range definition is correct, the validation formula should be

=NM_IS_ALPHABET=FALSE

 

[RoryA]

First post, second to last image. The selection starts in A2.

 

[jasonb75]

I looked at that several times and still missed it.

 

[hamed_]

Where are you seeing it start in A2, Rory? All the screen captures appear to start in A1.

The range definition is correct, the validation formula should be

=NM_IS_ALPHABET=FALSE

thanks dear jasonb75
my friend , i did as you said i change the formula in datavalidation as you said

so the problem of error in datavalidation window was solved i enter my error alert and then press ok,

but this datavalidation dont allow me to type anything in my excel .

as you see i type your Name Jason but datavalidation catch it and trigger my error.
i think everything back to my function code . i guess . the code of function again :

Option Compare Text

Function IS_Alphabet(mystr) As Boolean
Dim i As Integer
Dim my_ascicode
Dim temp_boolean As Boolean
For i = 1 To Len(mystr)
my_ascicode = Asc(Mid(mystr, i, 1))
Select Case my_ascicode
Case 32 To 47
IS_Alphabet = False
Exit Function
Case 58 To 59
IS_Alphabet = False
Exit Function
Case 91 To 96
IS_Alphabet = False
Exit Function
Case 123 To 126
IS_Alphabet = False
Exit Function
IS_Alphabet = False
Exit Function
Case 64
IS_Alphabet = False
Exit Function
Case 61
IS_Alphabet = False
Exit Function
Case Else
IS_Alphabet = True
End Select
Next
End Function

thanks, dear jason