[Solved] Data Validation checks cell below when applied to range

[i0nar]

Hello,
I wrote a LibreOffice Basic macro to set number format and a custom data validation on the range A2:A1000. The validation formula references each cell itself:

Code: Select all

Sub RepariereSpalteA()
    Dim oSheet As Object
    Dim oRange As Object
    Dim validation As Object
    Dim docLocale As Object
    Dim numberFormatKey As Long

    oSheet = ThisComponent.CurrentController.ActiveSheet
    oRange = oSheet.getCellRangeByPosition(0, 1, 0, 999) ' A2:A1000

    ' Zahlenformat setzen
    docLocale = ThisComponent.CharLocale
    numberFormatKey = ThisComponent.NumberFormats.queryKey("0", docLocale, False)
    If numberFormatKey = -1 Then
        numberFormatKey = ThisComponent.NumberFormats.addNew("0", docLocale)
    End If
    oRange.NumberFormat = numberFormatKey

    ' Validation des Bereichs lesen (gibt eine Struktur zurück)
    validation = oRange.Validation
    validation.Type = com.sun.star.sheet.ValidationType.CUSTOM
    validation.ShowInputMessage = True
    validation.InputTitle = "Number)"
    validation.InputMessage = "Zahl aus 15 Ziffern"
    validation.ShowErrorMessage = True
    validation.ErrorTitle = "Ungültige Eingabe"
    validation.ErrorMessage = "Bitte genau 15 Ziffern eingeben (nur 0-9, keine Leerzeichen oder Buchstaben)."
    validation.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
    validation.Formula1 = "=AND(ISNUMBER(A2);A2>=100000000000000;A2<=999999999999999)"

    oRange.Validation = validation

    MsgBox "Spalte A (A2:A1000) wurde erfolgreich repariert.", 64, "Fertig"
End Sub

But when I assign this validation to the entire range at once, the formula in each cell’s validation shifts down by one row:

Cell A2’s validation formula references A3

Cell A3 references A4

And so forth.

Why does the validation formula shift by one row when applied to the range? How can I write or apply the formula correctly so each cell’s validation always references the cell itself (e.g., A2 references A2, A3 references A3, etc.)?

Thank you for your help!

[karolus]

Hallo
you missed:

Code: Select all

validation.SourcePosition = oRange.getCellByPosition(0,0).CellAddress

[i0nar]

Fast response and on point. Perfect! Thank you @karolus