Using VBA functions in Worksheet cell data validation
 

I wish to validate the data in cell B3. I can readily set up a custom
validation using a formula such as =AND(B3=0, B3<=100)

I would like to use a VBA function that I have written called IsValid().
However, if I attempt to enter a custom validation formula of =IsValid(B3)
I receive the message "a named range you specified cannot be found".

Where am I going wrong? Can't I reference a VBA function in this way?

The function appears to work if I use it in a normal Worksheet formula. For
example, if I enter =IsValid(A3) in cell A4 then A4 correctly displays
either TRUE or FALSE depending on the value in A3.

Using VBA functions in Worksheet cell data validation
 

Andrew,
You don't show your code but I knocked up this little function which is
similar to what you describe

Function IsValid(rng As Range)
If rng.Count 1 Then
IsValid = CVErr(xlValue)
Else
IsValid = (rng.Value = 0 And rng.Value <= 100 And Not
IsEmpty(rng.Value))
End If

End Function

If you put =IsValid(B3) in A3 it reruns TRUE or FALSE depending upon the
value.

How does it compare to yours?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andrew" wrote in message
...
I wish to validate the data in cell B3. I can readily set up a custom
validation using a formula such as =AND(B3=0, B3<=100)

I would like to use a VBA function that I have written called IsValid().
However, if I attempt to enter a custom validation formula of
=IsValid(B3)
I receive the message "a named range you specified cannot be found".

Where am I going wrong? Can't I reference a VBA function in this way?

The function appears to work if I use it in a normal Worksheet formula.
For
example, if I enter =IsValid(A3) in cell A4 then A4 correctly displays
either TRUE or FALSE depending on the value in A3.

Using VBA functions in Worksheet cell data validation
 

Bob Phillips wrote:
Andrew,
You don't show your code but I knocked up this little function which
is similar to what you describe

Function IsValid(rng As Range)
If rng.Count 1 Then
IsValid = CVErr(xlValue)
Else
IsValid = (rng.Value = 0 And rng.Value <= 100 And Not
IsEmpty(rng.Value))
End If

End Function

If you put =IsValid(B3) in A3 it reruns TRUE or FALSE depending upon
the value.

How does it compare to yours?


My function is:

Function IsValid(Num As Variant) As Boolean
IsValid = (Num = 0 And Num <= 100)
End Function
(the final version will check for various text values as alternatives to
0..100, hence use of Variant)

I have no problem using the function in a formula in a cell (eg.
=IsValid(B3) in cell A3). My problem occurs when I attempt to use the same
formula for Data Validation in as cell. eg in Cell B3 I select:

Data | Validation | Settings | Allow Custom | Formula =IsValid(B3)

This ought(?) to do the same as the worksheet function:

Data | Validation | Settings | Allow Custom | Formula =AND(B3=0,
B3<=100)

Using VBA functions in Worksheet cell data validation
 

Can you post your IsValid function cod

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Using VBA functions in Worksheet cell data validation
 

mudraker wrote:
Can you post your IsValid function code


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

My function is:

Function IsValid(Num As Variant) As Boolean
IsValid = (Num = 0 And Num <= 100)
End Function
(the final version will check for various text values as alternatives to
0..100, hence use of Variant)

I have no problem using the function in a formula in a cell (eg.
=IsValid(B3) in cell A3). My problem occurs when I attempt to use the same
formula for Data Validation in as cell. eg in Cell B3 I select:

Data | Validation | Settings | Allow Custom | Formula =IsValid(B3)

This ought(?) to do the same as the worksheet function:

Data | Validation | Settings | Allow Custom | Formula =AND(B3=0,
B3<=100)

Using VBA functions in Worksheet cell data validation
 

Function IsValid(Num As Variant) As Boolean
select case num
case 0 to 100
isValid = true
case else
isValid =false
end select
End Function


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Using VBA functions in Worksheet cell data validation
 

Andrew

You guessed it. You can't use a UDF in Data Validation (at least not that
I've ever seen). You can use theWorksheet_Change event to validate the data
using your UDF and pretty much emulate what you can do with DV.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Andrew" wrote in message
...
mudraker wrote:
Can you post your IsValid function code


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

My function is:

Function IsValid(Num As Variant) As Boolean
IsValid = (Num = 0 And Num <= 100)
End Function
(the final version will check for various text values as alternatives to
0..100, hence use of Variant)

I have no problem using the function in a formula in a cell (eg.
=IsValid(B3) in cell A3). My problem occurs when I attempt to use the
same
formula for Data Validation in as cell. eg in Cell B3 I select:

Data | Validation | Settings | Allow Custom | Formula =IsValid(B3)

This ought(?) to do the same as the worksheet function:

Data | Validation | Settings | Allow Custom | Formula =AND(B3=0,
B3<=100)

Using VBA functions in Worksheet cell data validation
 

mudraker wrote:
Function IsValid(Num As Variant) As Boolean
select case num
case 0 to 100
isValid = true
case else
isValid =false
end select
End Function


Thanks. However, it's not writing the function that's my problem. It's the
fact that you appear to not be able to use a user define VBA function in
Data Validation.