Calc "Custom" Data Validation via Basic macro possible bug?

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 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.

Please see an example here.

@sokol92 I think you misunderstood the problem: I don’t have an issue with the Error Alert message that is being generated, but the fact the it is occurring when VALID values (“x” or “X”) are entered into cell C2. ONLY after opening the Validity dialog box from the spreadsheet and clicking “OK” does the Custom validation work properly…

Let’s take a look together.
Custom Validity Test 2.ods (11.8 KB)

Also you can use formula:


So the macro you’ve written to handle the Custom Data Validation works quite well ONCE the Validity options have been set, but it doesn’t address my problem. I’m starting with a spreadsheet with NO Data Validity settings; I need to add those settings inside my macro. That’s what my SetCustomValidity attempts to do.
See Listing 416 on pg 475 of Andrew Pitonyak’s book for another example of what I’m trying to do:
His example macro works fine, but it does not use ValidationType=Custom which I think is the reason mine is not working…
Please download my spreadsheet and run my macro to see if you can duplicate the problem…

Cross posted at Problem with Custom Data Validation in Calc Basic macro (View topic) • Apache OpenOffice Community Forum where several responses have been made.

Done. You need to add a line in your macro.
1 Like

@sokol92: THANK YOU so much for sticking with me on this!!!
I had assumed the Operator for Custom would default to FORMULA, since that’s the only valid setting. Also there is no DATA: option (which corresponds to ConditionOperator) in the Validity dialog when Allow:CUSTOM is selected.
Thanks again for pointing out the missing line of code.

Good luck!