Macro crashes calc. How to get status of annotation "being edited"

[matzwaki]

My little macro is called by event of “selection changed”.
It sets annotations visible = true, when there cell is selected and sets all other annotations visible = false.

Sub SetAnotationVisible()

	On Error GoTo ErrorHandling
	
	doc = ThisComponent
	sheet = doc.currentController.activeSheet
	currentSelection = doc.CurrentSelection

    If Not currentSelection.supportsService("com.sun.star.sheet.SheetCell") Then
    	Exit Sub
    End If
    
    SetAllAnnotationsNotVisible(sheet)
	
	currentSelection.Annotation.IsVisible = True

    Exit Sub
    
ErrorHandling:
	StandardErrorMessage()

End Sub
'_________________________________________________________________________________

Sub SetAllAnnotationsNotVisible(sheet As Object)

	Dim allAnnotations As Object : allAnnotations = sheet.Annotations
	
	Dim index As Long
	For index = 0 To allAnnotations.count - 1
		allAnnotations(index).IsVisible = False
	Next index
	
End Sub

As far as you don’t touch the annotations it runs very well. But if you select an annotation window and edit the text, it runs unstable.
If you enlarge the window and click in a free area within the window, Calc often crashes.

I have worked out, that calc notices cell selections behind the annotation window. Maybe this gets an conflict of edit text and visibility of annotation at same time!?!

I would like to block the macro if an annotation is in edit mode. But how can I query this?

[sokol92]

Try this:

Option Explicit

Global lastCell as Object

Sub OnSelect(oSelection)
  Dim isCell as Boolean   
  isCell = HasUnoInterfaces(oSelection, "com.sun.star.table.XCell")
  If Not (lastCell Is Nothing) Then
    If isCell Then
      If lastCell.AbsoluteName = oSelection.AbsoluteName Then Exit Sub
    End If
    
    lastCell.Annotation.IsVisible = False
    lastCell = Nothing
  End If
     
  If IsCell Then    
    If oSelection.Annotation.String <> "" Then
      If oSelection.Annotation.IsVisible = False Then oSelection.Annotation.IsVisible = True
      lastCell = oSelection
    End If
  End If
End Sub      

Sub SetAllAnnotationsNotVisible(Optional Byval sheet As Object)
  Dim oAnnotation As Object 
  If IsMissing(sheet) Then sheet = ThisComponent.CurrentController.ActiveSheet
  For Each oAnnotation In sheet.Annotations
    If oAnnotation.isVisible = True Then oAnnotation.isVisible = False
  Next oAnnotation   
End Sub

AnnotationVisible.ods (9.8 KB)

[Villeroy]

No macros, no trouble.
ask129368.ods (14.6 KB)

[matzwaki]

To sokol92:
I got it to run and I think it is a good solution to avoid the crashes.
But (there is always a “But”) the customer (user) is used to click into the visible annotation to edit it. With this macro the annotation vanishes with a click into it.
I know that the annotation can be edited via the context menu. But please explain that to my customers.

[matzwaki]

To Villeroy:
Interesting, I wonder how you did that.
Obviously, I still have a lot to learn about LibreOffice.

Can you explain how that’s done?

[karolus]

@matzwaki

Can you explain how that’s done?

Its simply editing: ⇒right_click⇒DataValidity⇒Input-help for each of the »yellow cells«

[matzwaki]

@karolus

Its simply editing: ⇒right_click⇒DataValidity⇒Input-help for each of the »yellow cells«

Thank you for the hint.
It’s not the solution for this Problem but good to know and surely useful in other contexts

[Lupp]

@matzwaki

... but good to know and surely useful in other contexts ...

I would assume the poster showed the trick as a funny misuse of a feature.
I’m using LibO Calc for decades now, and don’t remember a situation where the idea would actually have served a reasonable purpose.

The “needed” macro is very simple. The only annoyance is that it must be assigned to the event.
See attached example:
disask_47622_TreatingAnnotationsOnSelection.ods (43.1 KB)

[matzwaki]

@Lupp

The only annoyance is that it must be assigned to the event.

In my tests in didn’t make difference if “assigned to the event” or not.
My very first version of the macro was this:

Sub SetAnotationVisible(selectionFromEvent As Object)
	
    If Not selectionFromEvent.supportsService("com.sun.star.sheet.SheetCell") Then Exit Sub
	
	Dim annotations As Object : annotations = selectionFromEvent.Spreadsheet.Annotations
	
	Dim index As Long
	For index = 0 To annotations.count - 1
		annotations(index).IsVisible = False
	Next index
	
	Dim currentAnnotation As Object
	currentAnnotation = selectionFromEvent.Annotation
	
	If currentAnnotation.AnnotationShape Is Nothing Then Exit Sub
	
	currentAnnotation.IsVisible = True

End Sub

And there where the mentioned crashes, although it was assigned to the event.
My latest version I developed with all your help is this:

Sub SetAnotationVisible(selectionFromEvent As Object)
	
    If Not selectionFromEvent.supportsService("com.sun.star.sheet.SheetCell") Then Exit Sub
	
	Dim annotation As Object 
  
	For Each annotation In selectionFromEvent.Spreadsheet.Annotations
		annotation.isVisible = False
	Next annotation 
	
	Dim currentAnnotation As Object
	currentAnnotation = selectionFromEvent.Annotation
	
	If currentAnnotation.AnnotationShape Is Nothing Then Exit Sub
	
	currentAnnotation.IsVisible = True

End Sub

There is not much difference but this version dose not crash!
I don’t know what the problem is with this “for loop”, but it must cause somehow the crash.
A bit annoying in this setup is, that after editing annotations, the cursor changes to an cross!?!

[Lupp]

@matzwaki

And there where the mentioned crashes, although it was assigned to the event.

To assign a handler to the event “Selection changed” isn’t a means to avoid crashes, but to get the needed work done after a click into a different cell or after doing a step using the arrow keys.

The example I attached to my previous post works perffectly for me in lots of versions I tested with - from my current LibO V25.8.2 back to V3.3 (“legacy”), and also with AOO V 4.1.7.

If there are crashes I can only think of a corrupted user profie.

[matzwaki]

@Lupp

If there are crashes I can only think of a corrupted user profile.

That is possible. I will install an 2nd LibO with an untouched user profile and we will see.

[erAck]

You don’t need to install another, just invoke with a different user profile path that is to be created, using the -env:UserInstallation=... option, see LibreOffice user profile - The Document Foundation Wiki.