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.

Welcome dylamorroll,

this extension might help: TheCAT » Extensions

You’ll find validations data for your document sheets.

FYI, the report queries the document XML contents (see GetXMLValidityListsCollection() in XML module, L757). Perhaps @sokol92 code might replace that, I’ll have to check.

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
1 Like

Gives ‘Run Time Error- 424. Object Required’.
When I go for debug, it highlights almost 12th line from bottom, this one:
With ThisComponent.CurrentController

I’m WIndows Office power user, but novice for VB.

Have you placed the macro in the document?
If not, then you need to make the Calc document active and run the application macro from it.

1 Like

I’m creating the macro in the same document as running from.
I click ‘Create’ button in the Macro Dialouge (Alt+F8).
Then I paste the given contents within the 1st and last lines which appear automatically in VB macro creation time. And exit the window.
Then I invoke the Alt+F8 dialogue again, click on the sole macro name that I had created, and ‘Run’.

Then comes the said error.

Anyway thanks to you. But I was more into looking for some way to find a formula which could find if the current cell has any data validation or not. Something similar to: IsFormula function.

Please look at the attached file.

  1. The SelectCellsWithValidation macro selects cells that have validation
  2. The IsValidation function (see cell E1) returns 1 if the argument - the cell has validation.
    TestValidatiion.ods (11.6 KB)
1 Like

But there isn’t any macro in the attached file:

But you’ve understood my purpose 100% well. I really would appreciate if I could take (i.e. understand) what you’re offering me.

The screenshot you showed is not a LibreOffice screenshot.

1 Like

But can’t I use it in Ms Excel somehow? (Though I noted just now that this is specially LibreOffice help website).

thanks for trying to help.

P.S.: Does Libre Office has all the functionality that MS Excel offers? Should I switch over, wouldn’t I lose my work hours? (Right now I’m on to installing Libre Office 7.6.x)

The MS Excel object model is fundamentally different from the LibreOffice Calc object model.
LIbreOffice is a powerful program and its object model certainly takes time to learn (if you are familiar with the Excel object model, analogies can speed up the learning process).

1 Like

Though the other Macro: ‘Select Cells with Validation’ is working ok, nice.