Is there a command that quickly finds all the cells with comments (annotations) in a spreadsheet e displays their addresses? I know that the code “Range.queryContentCells( 8 ).Count” counts all the cells with comments in a range.
Do you mean something like this?
Function getCommentRng(incell As String) As Variant
Dim oSheets As Variant
Dim oCellRangesByName As Variant
Dim oRange As Variant
Dim oQueryResult As Variant
Dim oCell As Variant
Dim i As Long
Dim oAnnotation As Variant
On Error Resume Next
oSheets = ThisComponent.getSheets()
oCellRangesByName = oSheets.getCellRangesByName(incell)
Rem Of course, you can process not the first of the specified ranges, but all of them - this is just an example of use
oRange = oCellRangesByName(0)
oQueryResult = oRange.queryContentCells( 8 )
ReDim oAnnotation(0 To oQueryResult.getCount()+1, 0 To 1)
oAnnotation(UBound(oAnnotation,1)-1,0) = "---"
oAnnotation(UBound(oAnnotation,1)-1,1) = "---"
oAnnotation(UBound(oAnnotation,1),0) = ""
oAnnotation(UBound(oAnnotation,1),1) = ""
i = -1
For Each oCell In oQueryResult
i = i + 1
oAnnotation(i,0) = oCell.AbsoluteName
oAnnotation(i,1) = oCell.getAnnotation().getString()
Next oCell
getCommentRng = oAnnotation
End Function
Call it as `{=GETCOMMENTRNG(“A1:B8”)}’
navigating in iterators :
Can I export/print a list of the comments in a Calc spreadsheet?
Copy simple text from both values and comments (.ods file) an maintain pairing? - #6 by karolus
A sloppy variant:
Sub findDescribeSelect()
cCtrl = ThisComponent.CurrentController
cSheet = cCtrl.ActiveSheet
annoRgs = cSheet.queryContentCells(8)
out = annoRgs.RangeAddressesAsString
out = Join(Split(out, ";"), Chr(10))
MsgBox(out)
cCtrl.select(annoRgs)
End Sub
See attached example:_
disask121346AnnotationFinder.ods (12.9 KB)
(post deleted by author)
Simple, beautiful, and elegant…Thanks a million