create function and apply it in datavalidation formula box

hamed_

New Member
Joined
Sep 7, 2020
Messages
9
Office Version
  1. 2007
Platform
  1. Windows
hi everybody ,,,
i have created a function named check_ punctuation like this :
VBA Code:
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
function_code.png


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.
use in cell.png

validationbox.png

example.png

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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0
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
 
Upvote 0
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 :
VBA 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 .

wg.png



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

2.png


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

3.png


i dont know what is the problem , as you see the function works correctly but the problem is here in datavalidation box...
thanks
 
Upvote 0
You're applying the validation from A2 downward, so the formula should refer to $A2 not $A1
 
Upvote 0
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
Excel Formula:
=NM_IS_ALPHABET=FALSE
 
Upvote 0
First post, second to last image. The selection starts in A2.
 
Upvote 0
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
Excel Formula:
=NM_IS_ALPHABET=FALSE

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


false.png



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

my error.png



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


my_dt_erer.png


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 :
VBA 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


thanks, dear jason
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top