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