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]

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

No, you didn't!

I said

=NM_IS_ALPHABET=FALSE

Not

NM_IS_ALPHABET(A1)=FALSE

 

[jasonb75]

There is one thing that I missed from my suggestion. The checkbox to ignore blanks must be unchecked.

 

[hamed_]

No, you didn't!

I said

=NM_IS_ALPHABET=FALSE

Not

NM_IS_ALPHABET(A1)=FALSE

hi again... i did as you see

this time , i typed your name with punctuation , but datavalidation didnt understand

again , i think the problem is in the the type of define the FUNCTION . maybe if we want define a Function for the purpose of using in Datavalidation , it make different.
thanks again for your note.

 

[jasonb75]

Select a single cell in the validation range, then go to the name manager and check that the definition (refers to formula) is correct. If you have selected A4 then is should say A4 in the named range formula.

 

[hamed_]

Select a single cell in the validation range, then go to the name manager and check that the definition (refers to formula) is correct. If you have selected A4 then is should say A4 in the named range formula.

is it possible you do a example step by step , create a boolean Function and use it in DataValidation for an example .

 

[jasonb75]

Step 1:- Follow the instructions below precisely. The only thing that you should change is the sheet name if necessary. Do not change anything else.
Step 1:- Select cell A1 (Do not select a different cell).
Step 2:- Go to the Name Manager.

Note if your actual sheet is not named Sheet1 then use your actual sheet name where it says Sheet1 below.

Step 3:- Change the Scope of the named range to Sheet1
Step 4:- Change the Applies to formula of NM_IS_ALPHABET as below. Only change the sheet name if yours is not Sheet1. Do not change A1 to another cell.

=IS_ALPHABET(Sheet1!A1)

Step 5:- Select the range of cells to be validated.
Step 6:- Set the validation rule to

=NM_IS_ALPHABET=FALSE

Step 7:- Test validation.

 

[hamed_]

Step 1:- Follow the instructions below precisely. The only thing that you should change is the sheet name if necessary. Do not change anything else.
Step 1:- Select cell A1 (Do not select a different cell).
Step 2:- Go to the Name Manager.

Note if your actual sheet is not named Sheet1 then use your actual sheet name where it says Sheet1 below.

Step 3:- Change the Scope of the named range to Sheet1
Step 4:- Change the Applies to formula of NM_IS_ALPHABET as below. Only change the sheet name if yours is not Sheet1. Do not change A1 to another cell.

=IS_ALPHABET(Sheet1!A1)

Step 5:- Select the range of cells to be validated.
Step 6:- Set the validation rule to

=NM_IS_ALPHABET=FALSE

Step 7:- Test validation.

No it doesn't work for me.
Have you ever done yet as a practice?

 

[hamed_]

Step 1:- Follow the instructions below precisely. The only thing that you should change is the sheet name if necessary. Do not change anything else.
Step 1:- Select cell A1 (Do not select a different cell).
Step 2:- Go to the Name Manager.

Note if your actual sheet is not named Sheet1 then use your actual sheet name where it says Sheet1 below.

Step 3:- Change the Scope of the named range to Sheet1
Step 4:- Change the Applies to formula of NM_IS_ALPHABET as below. Only change the sheet name if yours is not Sheet1. Do not change A1 to another cell.

=IS_ALPHABET(Sheet1!A1)

Step 5:- Select the range of cells to be validated.
Step 6:- Set the validation rule to

=NM_IS_ALPHABET=FALSE

Step 7:- Test validation.

It works in Conditional Formatting,
but not in datavalidation ,

 

[jasonb75]

Have you ever done yet as a practice?

It worked last time I used it but testing it with your code is causing excel to crash, so the problem might be with that. I'll investigate in more detail to see if I can find the cause.

 

[jasonb75]

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.