Basic Macro for Validation with Cell Range

[Bal]

Calc documents allow for "Cell Range" on the Criteria page from Data > Validity. However, the com.sun.star.sheet.ValidationType does not include Cell Range. As far as I can tell, only ANY, WHOLE, DECIMAL, DATE, TIME, TEXT_LEN, LIST and CUSTOM are available.

I'd like to know how to set Cell Range as the type and check the Source to see if it is still valid and if not, replace it. Since Cell Range is an option for Data > Validity > Allow, I assume it must be accessible in Basic by some means.

CUSTOM allows for a formula for validation but if I set the com.sun.star.sheet.ConditionOperator to FORMULA and place the cell range reference I want into setFormula1() as a string like "$MySheet.$F$9:$F$189" it doesn't work.

Here's the example for setting the Validation that doesn't work. if I have already entered the Validation with Data > Validate, it changes Allow to All Values (ValidationType ANY) but does leave the Source (now Value) as the string inserted with SetFormula1().

Sub SetValidationRange
Dim oRange
Dim oValidation
Dim oDoc

oName = ThisComponent.CurrentController.getActiveSheet.Name
oDoc = ThisComponent.Sheets.getByName(oName)
oRange = oDoc.getCellRangeByName("I8:I40")
oValidation = oRange.Validation

oValidation.Type = com.sun.star.sheet.ValidationType.CUSTOM
oValidation.setOperator(com.sun.star.sheet.ConditionOperator.FORMULA)
oValidation.setFormula1("$Mining.$F$9:$F$189")
oRange.Validation = oValidation
End Sub

Thanks for any help you can provide.

[Villeroy]

This can not work as you can see in the conditional formatting dialog. Before you can do any such stunts with macros, you have to understand how conditional formatting works. Create a working c.f. in the GUI and then inspect the c.f. properties with XRay or MRI.

[Bal]

Actually, my problem was specifying .CUSTOM. When I changed that, it worked fine. In the example below, aSheet is the Sheet that has the cell range I want to validate; ValSheet checks to see if the current formula is valid. If it's not, I create a ValidationType of LIST, supply the formula I want (where iSheet is the sheet with the list used for validation in cells F9 to F189).

aRange = aSheet.getCellRangeByName("G8:G40")
aValidation = aRange.Validation
ValSheet = Mid(aValidation.getFormula1(), 1, Instr(1, aValidation.getFormula1(), ".")-1)
If ValSheet = "" Then
aValidation.Type = com.sun.star.sheet.ValidationType.LIST
aValidation.setOperator(com.sun.star.sheet.ConditionOperator.FORMULA)
aValidation.setFormula1("$" & iSheet.Name & ".$F$9:$F$189")
aRange.Validation = aValidation
End If

[Villeroy]

Sorry, I confused validation with conditional formatting. The validation formulas makes sense with list validation.