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!
Cheers,
Dylan
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):
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
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 SubGives ‘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.
@sokol92
I’m creating the macro in the same document as running from.
Steps:
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.
SelectCellsWithValidation macro selects cells that have validationIsValidation function (see cell E1) returns 1 if the argument - the cell has validation.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.
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).
