Error when I double-click on image

[Jonjozz]

I wrote this code on Sheet Events–>Double Click

Function onDblClick(oEvent As Variant) As Boolean
Const RANGE_TO_COLORIZE = "B2:U37"	' Set a range address that will respond to double clicks '
onDblClick = False
If oEvent.getSpreadsheet().getCellRangeByName(RANGE_TO_COLORIZE).queryIntersection(oEvent.getRangeAddress()).getCount() Then 
   If oEvent.getSpreadsheet().getCellRangeByName("J7:U7").queryIntersection(oEvent.getRangeAddress()).getCount() Then 
      Exit Function 
   Elseif oEvent.getSpreadsheet().getCellRangeByName("k18:u18").queryIntersection(oEvent.getRangeAddress()).getCount() Then
      Exit Function
    Elseif oEvent.getSpreadsheet().getCellRangeByName("c2:u2").queryIntersection(oEvent.getRangeAddress()).getCount() Then
      Exit Function   
   End if        
   onDblClick = True
   If oEvent.CellBackColor = -1 then
        oEvent.CellBackColor = rgb(255,255,0)
	  Exit Function
   Elseif oEvent.CellBackColor = rgb(255,255,0) then
      oEvent.CellBackColor = -1      
      Exit Function   
   EndIf 
EndIf 
End Function

My current code changes background cell color to yellow when double clicked and then changes it back to white when double clicked again.
A error occurs when a figure (not a cell) is double clicked.

Does anyone have a idea why it happens?

this is my calc (a sticker album)

[mikekaganski]

If I wrote such a macro, and in some case, it would misbehave, perhaps I’d try to put a breakpoint before the failing line, initiate the problematic scenario, and inspect the oEvent parameter in the Watch window. Then I maybe would try to use HasUnoInterfaces to check if the event implements XSheetCellRange, or consider adding an On Error code that would do some fallback in this case, if needed (or just do nothing).

[Jonjozz]

Do you know how check if oEvent is a range or a image?

[mikekaganski]

@mikekaganski

try to use HasUnoInterfaces to check if the event implements XSheetCellRange

[sokol92]

Illustration for @mikekaganski’s answer:

Function onDblClick(oEvent) As Boolean
  If HasUnoInterfaces(oEvent, "com.sun.star.sheet.XSheetCellRange") Then
    Msgbox "Double click on a cell."
  Else
    Msgbox "Double clicking on a non-cell."  
  End If   
End Function

[Jonjozz]

I had to write those extra code lines (Error Goto and Simulate a Esc Key Stroke)
It´s working fine…until now.

THANKS EVERYBODY…YOU ALL ARE WONDERFUL
.
.
.
.
On Error GoTo ErrorHandler
Dim oDoc, oSelection as object
oDoc = thisComponent
oSelection = oDoc.getCurrentSelection
.
.
.
.

if not oSelection.supportsService(“com.sun.star.sheet.SheetCell”) then Exit Function
.
.
.
.
.
Exit Function
ErrorHandler:
if Err = 423 then
'MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & “)”
'msgbox "Erro: " & Error
Call SimulateESC
End if
End Function

'=========================================================================================================
Sub SimulateESC()
Rem Solution by librebel
REM Simulate a RETURN Key press ( and -release ) in the current Window.
REM NB. This can cause the triggering of window elements.
Dim oKeyEvent As New com.sun.star.awt.KeyEvent
oKeyEvent.Modifiers = 0 REM A combination of com.sun.star.awt.KeyModifier.
oKeyEvent.KeyCode = com.sun.star.awt.Key.ESCAPE REM 1281
oKeyEvent.KeyChar = chr( 27 )
simulate_KeyPress( oKeyEvent )
End Sub
'=========================================================================================================
Sub simulate_KeyPress( oKeyEvent As com.sun.star.awt.KeyEvent )
Rem Solution by librebel
REM Simulate a Key press ( and -release ) in the current Window.
REM NB. This can cause the triggering of window elements.
REM For example if there is a button currently selected in your form, and you call this method
REM while passing the KeyEvent for RETURN, then that button will be activated.
If Not IsNull( oKeyEvent ) Then
Dim oWindow As Object, oToolkit As Object
oWindow = ThisComponent.CurrentController.Frame.getContainerWindow()
oKeyEvent.Source = oWindow
oToolkit = oWindow.getToolkit() REM com.sun.star.awt.Toolkit
oToolkit.keyPress( oKeyEvent ) REM methods of XToolkitRobot.
oToolkit.keyRelease( oKeyEvent )
End If
End Sub

[karolus]

Hallo
In short, readable and of course python:

def toggle_backcolor(event):
    try:
        sheet = event.Spreadsheet
    except AttributeError:
        return False
    ev_address = event.RangeAddress
    excludes = ("C2:U2","J7:U7","K18:U18")
    for name in excludes:
        if sheet[name].queryIntersection(ev_address):
            return False
    if sheet["B2:U37"].queryIntersection(ev_address):
        if event.CellBackColor == -1:
            event.CellBackColor = int("ffff00",16)
        else:
            event.CellBackColor = -1
        return True

[Jonjozz]

The problem seems to be:
If I double-click on image the error occurs then I need check if oEvent is a image (not a range)
How do I do that?

[Villeroy]

@Jonjozz

I need check if oEvent

https://help.libreoffice.org/7.2/en-US/text/sbasic/shared/01030300.html?DbPAR=BASIC#bm_id3153344
There is a variable watch window. And there is MRI. Apache OpenOffice Community Forum - [Tutorial] Introduction into object inspection with MRI - (View topic)

EDIT:
Try oEvent.Source.getSpreadsheet() (edit: no, thats rubbish)

Install the MRI extension and assign a sheet’s double–click event to MyMacros>MriLib>Module1>Mri

[karolus]

maybe the Extension apso.oxt will be of some help to “install” the ready to use python-code provided by me?

[Jonjozz]

Thanks for your attention, but I know nothing…zero about Python (for me, python is a giant snake only)

[Jonjozz]

Another thing I noticed…if I declare the spreadsheet and current cell like this:
(instead of using oEvent)
.
.
.
Dim oSheet, oCell as Object
oSheet = ThisComponent.Sheets.getByName(“anything”)
oCell = ThisComponet.getCurrentSelection()
If oSheet.getCellRangeByName(“J7:U7”).queryIntersection(oCell.getRangeAddress()).getCount() Then
.
.
.
.
the error doesn’t occur.