Is there a way to view all data validations for a sheet?

Hi there, I’m trying to view all the data validations/ drop-downs in use for a single sheet, so I can make sure I’m reimplementing everything correctly when trying to recreate this sheet elsewhere.

I know excel has a way to do this: Find cells that have data validation rules - Excel for Mac

But I can’t seem to find anything for LibreOffice Calc.

Any help would be greatly appreciated!


Note that any enhancement requests should be filed to the bug tracker. Without that, your wishes will even stay only wishes. E.g., look at the latest improvements in Navigator in Writer (in master, towards the future LO 7.2):

image description

The new Fields node allows to see fields in the Writer document. Without respective enhancement request tdf#137741, it would not happen. Calc could benefit from a similar enhancement filed by you.

As far as I know, there is no direct way. You can select cells with validation using the following SelectCellsWithValidation macro.

' Returns SheetCellRanges having cell validation
Function GetCellsWithValidation(ByVal oSheet) As Object
  Dim oResult As Object, oRanges, oRange
  For Each oRanges In oSheet.getUniqueCellFormatRanges()
    If oRanges.validation.Type<>0 Then
      If oResult Is Nothing Then
        For Each oRange In oRanges
          oResult.addRangeAddress oRange.RangeAddress, False
        Next oRange
      End If    
    End If
  Next oRanges  
End Function

' Select cells with Validation
Sub SelectCellsWithValidation
  Dim oSheet, oRanges
  With ThisComponent.CurrentController
    If Not (oRanges Is Nothing) Then
      Msgbox  oRanges.AbsoluteName
      .Select oRanges   
      Msgbox "No cells with validation"  
    End If
  End With  
End Sub