I’m trying to use the (relatively) new “Custom” ValidationType in a Basic macro to limit user input in a specific Calc spreadsheet column. The attached .ODS file (including the macro) demonstrates the problem:
Custom Validity Test.ods (11.3 KB)
NOTE: I realize I could use the “List” ValidationType to achieve this result (and it WORKS from the macro), but I specifically want to demonstrate the problem with “Custom” from a macro.
If you view Data->Validity in cell B2 you’ll see I’m limiting input to “x” or “X” and it works properly.
The macro “SetCustomValidity” attempts to do the same thing for cell C2. Before running the macro, the validity settings for C2 are defaulted to Allow: All values (and anything can be entered into the cell). AFTER running the SetCustomValidity macro, the validity settings for C2 show Allow: Custom with Formula: UPPER($C$2=“X”) (which is the same as the manually assigned validity for B2). However, if I try to enter “x” or “X” or ANYTHING else into C2, I get the error message “Invalid value.”
Interestingly, if I open the Data->Validity… dialog box for cell C2 and then click OK to exit the box (without making ANY changes to the entries), the validation works as expected (“x”'s are accepted and all other values are rejected).
I posted this over at forum.openoffice.org but didn’t receive many relevant answers. After more research I discovered there isn’t a “Custom” option under Data->Validity…->Criteria->Allow in Apache OpenOffice (as of v4.1.11), so looks like a LO-specific issue (currently tested on 7.0.6 & 7.2.2)…
Please test this and let me know if your results match mine; NOTE: to demonstrate the problem, you must try to enter something in the cell immediately after running the macro.