[Solved] Problem with Custom Data Validation in Calc macro

[firepuppy]

Validity Test.ods

(12.22 KiB) Downloaded 219 times

I've created a small macro & Calc spreadsheet to demonstrate the issue I'm having (using LO 7.0.6.2):
The Data Validity settings for column C were set via the Calc UI: Data->Validity->Custom...
There are initially NO Data Validity settings for column D (and anything can be entered there).
After running the macro SetCustomValidity, the Data Validity settings for column D should be identical to column C; however, I couldn't enter ANYTHING in those cells at that point.

Interestingly, if you open the Validity settings dialog box for one of the new column D cells after running the macro, you will see the CUSTOM formula.
At this point if you click OK (without making ANY changes to the formula or type), the data validation works as expected.

Is this a bug?
If so, how to describe it (and more importantly, can anyone suggest a workaround)??
If not, what am I doing wrong??
The fact that simply opening the Validity dialog box and saving it (via "OK") changes the behavior suggests that I need to somehow "commit" the validation settings to the current sheet or doc.
However, the macro is obviously making SOME change to the validation behavior of those cells, since all input is rejected after running it...

Ideas?

[Zizi64]

The Data Validity settings for column C were set via the Calc UI: Data->Validity->Custom...

I can not see any Data validity Criteria in the column C in your sample file... Only the Error Alert TAB is set.

[Zizi64]

Similar topics:
viewtopic.php?f=20&t=100526
viewtopic.php?f=20&t=25345
https://www.mail-archive.com/dev@api.op ... 09640.html
viewtopic.php?t=38046&p=174688

[firepuppy]

Does your version of LO support "Custom" as a ValidationType?
I'm not sure when that type was added to LO, but I just tested downloading to a machine with LO5.2.6.2 and saw the same thing you did: Error Alert tab settings but only "All values" in Criteria tab; if I checked the "Allow" pulldown in that version, the last option is "Text Length", not "Custom"...

Also I had read the other topics you mentioned (except for the one in mail-archive), but none of them use the (relatively) new "Custom" ValidationType...

The most perplexing thing is that the column D validation works if you:
1) Run the macro (which sets the validation formula), and then
2) Open the Data-Validity dialog in the sheet and click OK.

[firepuppy]

I decided to post my macro code inline for those who just want a quick look...

Code: Select all

Sub SetCustomValidity		'REM Set data validity on Column 3 entries
	oDoc = ThisComponent
	oEntries = oDoc.Sheets(0)

	oRange = oEntries.getCellRangeByPosition(3,1,3,2)
	oProps = oRange.Validation
	oProps.Type = com.sun.star.sheet.ValidationType.CUSTOM
	oProps.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
	oProps.Formula1 = "OR($D1=""X"";AND($D1=""Y"";$B1=""True""))"
	oProps.ErrorMessage = "Enter ""X"" or (if column B is True) ""Y"""
	oProps.ShowErrorMessage = True
	oRange.Validation = oProps
End Sub

[Zizi64]

See the pages 473-475 of Andrew Pitonyak's free macro programming book:
https://www.pitonyak.org/OOME_4_0.odt

[firepuppy]

See the pages 473-475 of Andrew Pitonyak's free macro programming book:

I read that section carefully before starting to write the macro; not sure what information from there conflicts with my code?

BTW, it looks like ValidationType CUSTOM was introduced in LO 6.2...

I have banged on this some more and came up with the following based on: viewtopic.php?f=20&t=100526
I changed the ValidationType from "CUSTOM" to "LIST" (with a Formula, which corresponds to "Allow: Cell Range" in the Calc UI).
I added another column E which contains the valid values.
When I first run the new macro and then click on the List pulldown in column D I see "Err:508" instead of the expected list (which I see in column C).
HOWEVER, the same procedure "fixes" the problem: open the Data->Validity dialog on the entries in column D, don't change ANYTHING, simply click "OK" and the validation now works properly.

Can someone please verify if you see the same behavior that I am seeing with these examples?

Validity Test (LIST).ods

(12.33 KiB) Downloaded 210 times

...and here's the new code:

Code: Select all

Sub SetCustomValidity		'REM Set data validity on Column 3 entries
	oDoc = ThisComponent
	oEntries = oDoc.Sheets(0)

	oRange = oEntries.getCellRangeByPosition(3,1,3,2)
	oProps = oRange.Validation
	oProps.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
	oProps.SetOperator(com.sun.star.sheet.ConditionOperator.FORMULA)
	oProps.Type = com.sun.star.sheet.ValidationType.LIST
	oProps.Formula1 = "IF($B1=""True"",$E$2:$E$3,$E$2)"					' Type = List
	oProps.ErrorMessage = "Enter ""X"" or (if column B is True) ""Y"""
	oProps.ShowErrorMessage = True
	oRange.Validation = oProps
End Sub

More ideas?

[Villeroy]

Which problem are you trying to solve here? I can not see any purpose in that code. Are you sure you understood all aspects of the validation feature before you start writing Basic code?

[Lupp]

Code: Select all

Sub SetCustomValidity      
REM Set data validity on Column 3 entries  ??
REM You do it for the range D2:D3.
   oDoc = ThisComponent
   oEntries = oDoc.Sheets(0)

   oRange = oEntries.getCellRangeByPosition(3,1,3,2)
   oProps = oRange.Validation
   oProps.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
   oProps.ShowList = 1
   oProps.SetOperator(0)
REM Value 9 doesn't make sense, imo.
   oProps.Type = com.sun.star.sheet.ValidationType.LIST
   oProps.Formula1 = "IF($B1=1;$F$2:$F$3;$F$1)"
REM B1 adapts to B2 for the second cell (D3).
REM You can't enter the string "TRUE" into a cell without precautions.
REM If you try, you get the Boolena value.
REM Parameter delimiter is semicolon, not comma!
REM F in place of E: Validity cells should be accompanied by a column giving room for the DropDownTriangle.
   oProps.ErrorMessage = "My error message"
REM An example should be simple. THe "X" and "Y" stuff doesn't make sense, imo.
   oProps.ShowErrorMessage = True
   oRange.Validation = oProps
End Sub

Demo:

aoo106398validityByMacro_Why.ods

(10.03 KiB) Downloaded 229 times

The validation is not yet implemented. Call the hyperlinked Sub.

[Villeroy]

Select D1:D2 with D1 as active cell.
Data>Validity...
Allow: Cell Range
Source: IF($B1=1;$F$2:$F$3;$F$1)

[firepuppy]

Which problem are you trying to solve here? I can not see any purpose in that code. Are you sure you understood all aspects of the validation feature before you start writing Basic code?

The "purpose" of my code (and Calc sheet) is to demonstrate what may be a bug in the implementation of Custom Data Validation performed using a StarBasic macro.

Select D1:D2 with D1 as active cell.
Data>Validity...
Allow: Cell Range
Source: IF($B1=1;$F$2:$F$3;$F$1)

Very helpful: you've just told me how to do Validation via the Calc UI. I obviously have NO problem doing that: column C in the spreadsheet was produced in that manner. My problem is in the macro code.

Mr. Lupp at least read my 2nd macro (using ValidationType LIST instead of CUSTOM) and pointed out several issues with my code. The one which mattered was the use of "," as a separator in my formula instead of ";". I had copied the formula from the Calc UI after doing it manually and forgot to change the commas to semicolons. Once I made that simple change, my Validity Test (LIST) works fine. Here is the corrected version:

Validity Test (LIST).ods

(12.33 KiB) Downloaded 214 times

HOWEVER, my original problem still exists: using ValidationType CUSTOM with a formula which does NOT require a separate cell range to hold a LIST of valid values does NOT appear to work.
Note: the macro in post #1 did not have the separator problem (commas instead of semicolons)..

Can someone PLEASE just
1) Download the Validity Test.ODS file from post #1.
2) Enter something in cell D2 or D3 to prove that there is NO Data Validation prior to running the macro.
3) Run the SetCustomValidity macro (Tools->Macros->Run Macro...->Validity Test.ODS->Standard->Module1->SetCustomValidity.
4) Select cell D2 or D3 and check the Data->Validity... settings to confirm the Criteria tab now shows Allow: CUSTOM with the Formula "OR($D2="X",AND($D2="Y",$B2="True"))".
5) Click "Cancel" to close the dialog box.
6) Try to enter an "X" or "Y" or ANYTHING ELSE in that cell (I couldn't enter ANYTHING into D2 or D3 at this point).
7) Open the Data->Validity... settings dialog a 2nd time, make NO CHANGES and click "OK" instead of "CANCEL" to exit the dialog this time.
8) Finally try to enter data in the cell ("X" should be accepted in D2 and "X" or "Y" should be accepted in D3).

If someone can duplicate this behavior, can you explain why? Is there something wrong or missing from my code?
If you are successful in entering "X" or "Y" in step 6 BEFORE entering the Data->Validity dialog and clicking "OK", PLEASE tell me what environment you're running.

Thanks for the replies...

[ms777]

I went through your 1) to 8) . Validity Test.ods works like a charm on my Apache Office 4.1.6.

Good luck,

ms777

[firepuppy]

@ms777,
Just to be clear: you were successful entering "X" or "Y" in step 6 (BEFORE opening the Validity dialog and clicking "OK")?

[robleyd]

For the record - cross posted at https://ask.libreoffice.org/t/calc-cust ... ug/69782/3

If you cross post, please let us know that you have done so, otherwise it leads to several discussions and a waste of time because several identical answers may be posted by different users.

[firepuppy]

Sorry I didn't remember to update this thread when I posted over at ask.libreoffice.org.
I did mention in my initial post there https://ask.libreoffice.org/t/calc-cust ... ug/69782/1
that I had posted here, but I believed I wasn't receiving relevant answers here because OpenOffice (as of 4.1.11) does NOT have an "Allow: CUSTOM" option in the Validity dialog (AND the solution posted over there does NOT work in OO 4.1.11). The strangest part is that I first found the CUSTOM ValidationType in the OO API docs: https://www.openoffice.org/api/docs/com ... tml#CUSTOM

My problem WAS solved over at https://ask.libreoffice.org/t/calc-cust ... ug/69782/7 by sokol92, who pointed out a missing parameter in my macro validation setup.

One of the few differences I've seen (between OO & LO) that made me glad I went over to LO several years ago.