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 ...

 

[hamed_]

Refined code that doesn't crash.

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

Follow steps in post #16, but change the validation formula in step 6 to

=NM_IS_ALPHABET

Make sure that the data validation 'Ignore blank' checkbox is not checked.

I have tested this and it does work.

Refined code that doesn't crash.

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

Follow steps in post #16, but change the validation formula in step 6 to

=NM_IS_ALPHABET

Make sure that the data validation 'Ignore blank' checkbox is not checked.

I have tested this and it does work.

Hoooooera.
Finally , it's Done...
thanks a lot brother
Your refined function worked.
As I said in previous posts, the problem is in how we define function.
So , I must define other functions base on your function.

 

[jasonb75]

I'm not entirely sure why Mine worked and yours didn't, I've removed some lines that were not needed but not made any significant changes.
The only thing that I can see might have been the cause was using 'Option Compare Text' which I removed. I didn't try your code without it so it it could be worth trying that before re-writing everything else.