DATA VALIDATION
 

DATA VALIDATION:

col heading = RENHB
valid value = "R,E,N,H,B"

Data/Validation/Custom/Formula = ( function dvRENHB )
(that's what I want, anyway)

(simple version)
Function dvRENHB()
dvRENHB = 0
Dim cVAL
cVAL = ActiveCell.Value
cVAL = UCase(cVAL)
If InStr(1, "RENHB", cVAL, 1) Then
ActiveCell.Value = cVAL
dvRENHB = 1
End If
End Function

Cannot use custom funtion for data validation, so...

I set Data/Validation/Custom/Formula = "=A1" (and "=$A$1")
I set A1 = "=dvRENHB()"; doesn't work
I set A1 = "dvRENHB()"; doesn't work
I set A1 = (...many more...)

How can I get this to work?

Thanks

DATA VALIDATION
 

Data--Validation--
Allow: List
Source: R,E,N,H,B
OK

bdm wrote:
DATA VALIDATION:

col heading = RENHB
valid value = "R,E,N,H,B"

Data/Validation/Custom/Formula = ( function dvRENHB )
(that's what I want, anyway)

(simple version)
Function dvRENHB()
dvRENHB = 0
Dim cVAL
cVAL = ActiveCell.Value
cVAL = UCase(cVAL)
If InStr(1, "RENHB", cVAL, 1) Then
ActiveCell.Value = cVAL
dvRENHB = 1
End If
End Function

Cannot use custom funtion for data validation, so...

I set Data/Validation/Custom/Formula = "=A1" (and "=$A$1")
I set A1 = "=dvRENHB()"; doesn't work
I set A1 = "dvRENHB()"; doesn't work
I set A1 = (...many more...)

How can I get this to work?

Thanks

DATA VALIDATION
 

But...
I want to do more than validate data in the function
(and more than the UCase() noted below).
And more functions for more fields...
So I want to know how to run a custom function here.
And...I want to do it here, through the validation
process, not through a more elaborate separate VB
code/form/controls etc (I want it as simple as possible
and as closely tied to the basic worksheet as possible).

In other words...how do I get it to work this way?
(I've also tried saving my code as an Add-in: doesn't work
(still doesn't see function); tried renaming my function
to a built-in function name, hoping it might
override/replace the built-in: doesn't work).

Thanks again

-----Original Message-----
Data--Validation--
Allow: List
Source: R,E,N,H,B
OK

bdm wrote:
DATA VALIDATION:

col heading = RENHB
valid value = "R,E,N,H,B"

Data/Validation/Custom/Formula = ( function dvRENHB )
(that's what I want, anyway)

(simple version)
Function dvRENHB()
dvRENHB = 0
Dim cVAL
cVAL = ActiveCell.Value
cVAL = UCase(cVAL)
If InStr(1, "RENHB", cVAL, 1) Then
ActiveCell.Value = cVAL
dvRENHB = 1
End If
End Function

Cannot use custom funtion for data validation, so...

I set Data/Validation/Custom/Formula = "=A1"
(and "=$A$1")
I set A1 = "=dvRENHB()"; doesn't work
I set A1 = "dvRENHB()"; doesn't work
I set A1 = (...many more...)

How can I get this to work?

Thanks



.

DATA VALIDATION
 

With the custom function:
'============================
Function dvRENHB(rng As Range)
Dim str As String
str = "RENHB"
dvRENHB = 0
dvRENHB = InStr(1, str, UCase(rng.Value), 1)
End Function
'=====================

In cell B2, enter the formula:
=dvRENHB(A2)

In cell A2, set data validation to Allow: Custom, and the formula:
=AND(LEN(A2)=1,B20)

bdm wrote:
But...
I want to do more than validate data in the function
(and more than the UCase() noted below).
And more functions for more fields...
So I want to know how to run a custom function here.
And...I want to do it here, through the validation
process, not through a more elaborate separate VB
code/form/controls etc (I want it as simple as possible
and as closely tied to the basic worksheet as possible).

In other words...how do I get it to work this way?
(I've also tried saving my code as an Add-in: doesn't work
(still doesn't see function); tried renaming my function
to a built-in function name, hoping it might
override/replace the built-in: doesn't work).

Thanks again


-----Original Message-----
Data--Validation--
Allow: List
Source: R,E,N,H,B
OK

bdm wrote:

DATA VALIDATION:

col heading = RENHB
valid value = "R,E,N,H,B"

Data/Validation/Custom/Formula = ( function dvRENHB )
(that's what I want, anyway)

(simple version)
Function dvRENHB()
dvRENHB = 0
Dim cVAL
cVAL = ActiveCell.Value
cVAL = UCase(cVAL)
If InStr(1, "RENHB", cVAL, 1) Then
ActiveCell.Value = cVAL
dvRENHB = 1
End If
End Function

Cannot use custom funtion for data validation, so...

I set Data/Validation/Custom/Formula = "=A1"

(and "=$A$1")

I set A1 = "=dvRENHB()"; doesn't work
I set A1 = "dvRENHB()"; doesn't work
I set A1 = (...many more...)

How can I get this to work?

Thanks




.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html