Ask Your Question
0

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

asked 2021-05-04 09:55:10 +0200

dylanmorroll gravatar image

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: https://support.microsoft.com/en-us/o...

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

Any help would be greatly appreciated!

Cheers, Dylan

edit retag flag offensive close merge delete

Comments

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.

Mike Kaganski gravatar imageMike Kaganski ( 2021-05-05 09:12:30 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2021-05-04 18:17:03 +0200

sokol92 gravatar image

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
        oResult=oRanges
      Else
        For Each oRange In oRanges
          oResult.addRangeAddress oRange.RangeAddress, False
        Next oRange
      End If    
    End If
  Next oRanges  

  GetCellsWithValidation=oResult
End Function

' Select cells with Validation
Sub SelectCellsWithValidation
  Dim oSheet, oRanges
  With ThisComponent.CurrentController
    oSheet=.ActiveSheet 
    oRanges=GetCellsWithValidation(oSheet)

    If Not (oRanges Is Nothing) Then
      Msgbox  oRanges.AbsoluteName
      .Select oRanges   
    Else
      Msgbox "No cells with validation"  
    End If
  End With  
End Sub
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2021-05-04 09:55:10 +0200

Seen: 25 times

Last updated: May 04