Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |