How to establish a validation rule on Calc using Basic?

[mipial]

Hello.

I have a spreadsheet for timetables.
There is a range in one column with names. That’s the source of validation data for the big range with days and hours that makes up the timetable.
I have a sub that iterates through the names and reads the coincidences between name, date, and hour, to put the result on a Writer file.

The file is duplicated every week, and the names are updated before editing the timetables if needed.

I noticed that some conditions would allow the users (not always me) to insert wrong data into the timetable bypassing the validation.

So, I’d like to know how can I force the Basic routine to re-create the validation and verify it before processing the data.

I’ve tried to use the sheet.TableValidation service, but can’t find how to use it properly.

TL:DR Let’s say I want to create an instruction to force validation on the range B3:Q66 using the data from A75:A100

I asked ChatGPT, but it asks me to CreateInstance “com.sun.star.sheet.TableValidations” on the object sheet, but sheet does not support CreateInstance.

I’d apppreciate any help.

[Zizi64]

@mipial

I have a spreadsheet for timetables.
There is a range in one column with names. That’s the source of validation data for the big range with days and hours that makes up the timetable.
I have a sub that iterates through the names and reads the coincidences between name, date, and hour, to put the result on a Writer file.

Please upload your ODF type sample file here.

[mipial]

OK. I’ll have to create a version with non personal data first.

[mipial]

@Zizi64 @elmau

I managed to activate validation for the range. I found out was trying to use a different service from a different module. (Programming is straightforward, but knowing the intricacies of every component and being able to tell apart one method from another that sounds very similar is the real challenge).

What I still don’t know is how to force it to validate again. I erased validation in the spreadsheet and wrote random data. The validation was reinstated, but it didn’t warn me about the random data.

There’s an option: Tools-Detective-Mark Invalid Data. Is there a way to call it from Basic?
Found it: ShowInvalid()

I am automating it because it will be other people filling in, and I can’t tell them to go through all the other loops.

Thanks.

Sub Validacion

Dim oDoc As Object
oDoc = ThisComponent 
'oDoc es la hoja de cálculo

Dim oSheet As Object
oSheet = oDoc.Sheets.getByName("HORARIO")
'oSheet es la hoja en particular

Dim oFuenteDeValidacion As String
oFuenteDeValidacion = "$A$75:$A$126" 
'Origen de datos

Dim oHorarioLunes As String
Dim oHorarioMartes As String
Dim oHorarioMiercoles As String
Dim oHorarioJueves As String
Dim oHorarioViernes As String

oHorarioLunes = "$B$3:$Q$14"
oHorarioMartes = "$B$16:$Q$27"
oHorarioMiercoles = "$B$29:$Q$40"
oHorarioJueves = "$B$42:$Q$53"
oHorarioViernes = "$B$55:$Q$66"
'Datos de horarios

Dim oRangoNombres as Object
Dim oRangoDiana as Object

oRangoNombres = oSheet.getCellRangeByName(oFuenteDeValidacion)

oRangoDiana = oSheet.getCellRangeByName(oHorarioLunes)

Dim oValidations As Object
oValidations = oRangoDiana.Validation

oValidations.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
oValidations.Type = com.sun.star.sheet.ValidationType.LIST
oValidations.Formula1 = oFuenteDeValidacion
oValidations.ErrorMessage = "Datos inválidos en Lunes"   
oValidations.ShowErrorMessage = True

oRangoDiana.Validation = oValidations

End Sub

[elmau]

A simple example to assign a validation in the above ranges.

def main():

    from com.sun.star.sheet.ValidationType import LIST

    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet

    rango = sheet['B3:Q66']
    validation = rango.Validation

    validation.Type = LIST
    validation.Formula1 = "$A$75:$A$100"
    validation.ShowErrorMessage = True

    rango.Validation = validation

    return

[mipial]

Thanks. I’ll try it.