[Solved] Pop up menu in a Calc document

Creating a macro - Writing a Script - Using the API

[Solved] Pop up menu in a Calc document

Postby Flitec » Thu Sep 15, 2011 7:38 pm

Hello everyone,
Using Open Office ver. 3.3.0 with Windows XP

We are looking to move from Excel to OpenOffice the problem is large number of "Excel macro" we have to translate. I'm not a programmer so maybe someone can help me

In Excel we have a particular macro that allows the user to activate a "pop up" menu when he clicks on each cell of the "A" column of the spreadsheet and allow to put the selected menu/submenu item in the selected cell.

After some research I managed to write some code (attached) that seems to work to 90% in fact when the user clicks the cell in the "A" column the "pop-up menu" is activated correctly, but it seems that control is not transferred to the "pop-up menu" but remains in the selected cell.
By pressing the "esc" key on the keyboard control is passed to the "pop-up menu" and everything seems to work properly.
How can I directly transfer control to the "pop-up menu"??

In the attached code, there are 2 routines, the first creates the tree of "pop-up menu" and the second activates the "handler" to track the click on the cell in "A" column

Thank you all for the help
Paul

Code: Select all   Expand viewCollapse view
Global FL_prvCalcSheet As Object
Global FL_prvCalcSheet_MouseClickHandler As Object
Global oFLPopupMenu As Object
Global oFLMenuListener As Object
Dim oCell as Object
Sub Main



Dim oSheet As Object
Dim oRange As Object
Dim FrameSheet As Object
Dim oCell As Object

Dim Riga() As Variant
Dim Cella As Variant
'Dim oFLPopUpMenu As Object
Dim oFLPopUpSubMenu0 As Object
Dim oFLPopUpSubMenu1 As Object
Dim oFLPopUpSubMenu2 As Object
Dim oFLPopUpSubMenu3 As Object
Dim oFLPopUpSubMenu4 As Object
Dim oFLPopUpSubMenu5 As Object
Dim oFLPopUpSubMenu6 As Object


  oFLMenuListener = CreateUnoListener( _
   "PopupMenuListener_", "com.sun.star.awt.XMenuListener")

      oFLPopupMenu = CreateUnoService("com.sun.star.awt.PopupMenu")
        oFLPopupSubMenu0= CreateUnoService("com.sun.star.awt.PopupMenu")
      oFLPopupSubMenu1= CreateUnoService("com.sun.star.awt.PopupMenu")
      oFLPopupSubMenu2= CreateUnoService("com.sun.star.awt.PopupMenu")
      oFLPopupSubMenu3= CreateUnoService("com.sun.star.awt.PopupMenu")
      oFLPopupSubMenu4= CreateUnoService("com.sun.star.awt.PopupMenu")
      oFLPopupSubMenu5= CreateUnoService("com.sun.star.awt.PopupMenu")
      oFLPopupSubMenu6= CreateUnoService("com.sun.star.awt.PopupMenu")
     
     



  ' set listener and insert menu items
              With oFLPopupMenu
                   '.addMenuListener(oFLMenuListener)
                   .insertItem(1,"All",0,0) 'id, label, type, position
                   .setPopUpmenu(1, oFLPopUpSubMenu0)
                 End With


           With oFLPopupSubMenu0
                       '.addMenuListener(oFLMenuListener)
                      .insertItem(1,"Sub menu1",0,0) 'id, label, type, position
                      .insertItem(2,"Sub menu2",0,1)
                       .insertItem(3,"Sub menu3",0,2)
                       .insertItem(4,"Sub menu4",0,3)
                       .insertItem(5,"Sub menu5",0,4)
                       .insertItem(6,"Sub menu6",0,5)
                     
                       .setPopUpmenu(1, oFLPopUpSubMenu1)
                       .setPopUpmenu(2, oFLPopUpSubMenu2)
                       .setPopUpmenu(3, oFLPopUpSubMenu3)
                       .setPopUpmenu(4, oFLPopUpSubMenu4)
                       .setPopUpmenu(5, oFLPopUpSubMenu5)
                       .setPopUpmenu(6, oFLPopUpSubMenu6)
                 End With

                        for j=1 to 10

                                                  With oFLPopupSubMenu1

                                              .insertItem(j,"1-Item " & j,0,j) 'id, label, type, position
                                                .setCommand(j,"1-Item " & j)
                                             End With
     
                        next j


                        for j=1 to 10

                                                  With oFLPopupSubMenu2
                             
                                              .insertItem(j,"2-Item " & j,0,j) 'id, label, type, position
                                                .setCommand(j,"2-Item " & j)
                                             End With
     
                        next j

                        for j=1 to 10

                                                  With oFLPopupSubMenu3
                                       
                                              .insertItem(j,"3-Item " & j,0,j) 'id, label, type, position
                                                .setCommand(j,"3-Item " & j)
                                             End With
     
                        next j

                        for j=1 to 10

                                                  With oFLPopupSubMenu4
                                       
                                              .insertItem(j,"4-Item " & j,0,j) 'id, label, type, position
                                                .setCommand(j,"4-Item " & j)
                                             End With
     
                        next j

                        for j=1 to 10

                                                  With oFLPopupSubMenu5
                                       
                                              .insertItem(j,"5-Item " & j,0,j) 'id, label, type, position
                                                .setCommand(j,"5-Item " & j)
                                             End With
     
                        next j

                        for j=1 to 10

                                                  With oFLPopupSubMenu6
                                             
                                              .insertItem(j,"6-Item " & j,0,j) 'id, label, type, position
                                                .setCommand(j,"6-Item " & j)
                                             End With
     
                        next j





                           
                              oFLPopupMenu.addMenuListener(oFLMenuListener)
                              oFLPopupSubMenu1.addMenuListener(oFLMenuListener)
                              oFLPopupSubMenu2.addMenuListener(oFLMenuListener)
                              oFLPopupSubMenu3.addMenuListener(oFLMenuListener)
                              oFLPopupSubMenu4.addMenuListener(oFLMenuListener)
                              oFLPopupSubMenu5.addMenuListener(oFLMenuListener)
                              oFLPopupSubMenu6.addMenuListener(oFLMenuListener)


call FL_attivazione_handler_Mouse_Stdprv

End Sub



Sub FL_attivazione_handler_Mouse_Stdprv
  If NOT ThisComponent.supportsService( "com.sun.star.sheet.SpreadsheetDocument" ) Then Exit Sub
  FL_prvCalcSheet = ThisComponent.currentController
  FL_prvCalcSheet_MouseClickHandler =createUnoListener("FLStdprvClick_", "com.sun.star.awt.XEnhancedMouseClickHandler")
  FL_prvCalcSheet.addEnhancedMouseClickHandler(FL_prvCalcSheet_MouseClickHandler)
End Sub


Sub FL_disattivazione_handler_Mouse_Stdprv
  on error resume next
  FL_prvCalcSheet.removeEnhancedMouseClickHandler(FL_prvCalcSheet_MouseClickHandler)
  on error goto 0
End Sub

Function FLStdprvClick_disposing(oEvt)
End Function

Function FLStdprvClick_mousePressed(oEvt) As Boolean
FLStdprvClick_mousePressed =TRUE
End Function


Function FLStdprvClick_mouseReleased(oEvt) As Boolean

   
     oCell = oEvt.Target

          If NOT oCell.supportsService( "com.sun.star.table.Cell" ) Then Exit Function

            If oEvt.popuptrigger=false then

     
                  Flcolonna=ThisComponent.CurrentController.getSelection.CellAddress.Column

                     If  Flcolonna= 0 Then

                     
                           Rem Remove local Items previouslly created
                           n = oFLPopupMenu.getItemCount
                           
                           For t= 1 to n-1
                           oFLPopupMenu.removeItem(0,1)
                           next t
   '-----------------------------------------------------------------------
                     REM procedure to create popup menu local items depending on the active sheet
                                 '..
                                 '..
                                 '..
                                 'Example
                 
                                     With oFLPopupMenu
                                       .insertItem(100,"Local item 1",0,0)
                                       .setCommand(100,"Local item 1")
                                    End With
                                 

                  Dim aRect As New com.sun.star.awt.Rectangle
                     With aRect
                        .X = oEvt.X
                        .Y = oEvt.Y +35'+ oEvt.Height
                        .Width = 0'100
                        .Height = 0'100
                     End With
               
               oDialog1=ThisComponent.CurrentController.Frame.ComponentWindow
               oFLPopupMenu.execute(oDialog1,aRect,com.sun.star.awt.PopupMenuDirection.EXECUTE_DEFAULT)
               

            End if
         End If
FLStdprvClick_mouseReleased = FALSE

End Function


Sub PopupMenuListener_select ( oEv )
  sCmd = oEv.Source.getCommand(oEv.MenuId)
  ocell.setvalue(val(sCmd))
End Sub
Sub PopupMenuListener_highlight ( oEv )
End Sub
Sub PopupMenuListener_activate ( oEv )
End Sub
Sub PopupMenuListener_deactivate ( oEv )
End Sub
Sub PopupMenuListener_disposing( oEv )
End Sub
Last edited by Flitec on Sat Sep 17, 2011 7:22 am, edited 2 times in total.
Libre Office 5.0.4.2 Windows 7
Flitec
 
Posts: 8
Joined: Wed Sep 14, 2011 11:46 am
Location: Italy

Re: Pop up menu in a calc document

Postby Villeroy » Thu Sep 15, 2011 7:57 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28674
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pop up menu in a calc document

Postby hanya » Thu Sep 15, 2011 8:12 pm

You have to return value of mouseReleased method before to show your popup menu. The execute method of it locks application mechanism. Try Villeroy's proposal.
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
Apache OpenOffice 4-dev on Xubuntu 14.04
hanya
Volunteer
 
Posts: 885
Joined: Fri Nov 23, 2007 9:27 am
Location: Japan

Re: Pop up menu in a calc document

Postby Flitec » Fri Sep 16, 2011 8:17 am

hanya wrote:You have to return value of mouseReleased method before to show your popup menu. The execute method of it locks application mechanism. Try Villeroy's proposal.

Thanks for your answer Hanya, I tried to move the value of mouseReleased method

Code: Select all   Expand viewCollapse view
'
'
'
'
                   End With

               FLStdprvClick_mouseReleased = FALSE
               oDialog1=ThisComponent.CurrentController.Frame.ComponentWindow
               oFLPopupMenu.execute(oDialog1,aRect,com.sun.star.awt.PopupMenuDirection.EXECUTE_DEFAULT)
               

            End if
         End If


End Function


but nothing seems to change.
Did I undertand correctly your suggestion ?

About Villeroy proposal, we need to have a popup menu with submenu items, so if I understood correctly this couldn't be a solution
Thanks again
Paul
Libre Office 5.0.4.2 Windows 7
Flitec
 
Posts: 8
Joined: Wed Sep 14, 2011 11:46 am
Location: Italy

Re: Pop up menu in a Calc document

Postby hanya » Fri Sep 16, 2011 9:37 am

I meant, in Python:
Code: Select all   Expand viewCollapse view
import unohelper

import threading

from com.sun.star.awt import XMouseClickHandler
from com.sun.star.awt import Rectangle
from com.sun.star.awt.MouseButton import LEFT as MB_LEFT

class MouseClickHandler(unohelper.Base, XMouseClickHandler, object):
   """ Handling mouse click on the document. """
   def __init__(self, ctx, doc):
      self.ctx = ctx
      self.doc = doc
      self.popup = None
      self._register()
   
   def _register(self):
      self.doc.getCurrentController().addMouseClickHandler(self)
   def unregister(self):
      """ Remove myself from broadcaster. """
      self.doc.getCurrentController().removeMouseClickHandler(self)
   
   def disposing(self, ev):
      global handler
      handler = None
   def mousePressed(self, ev):
      return False
   def mouseReleased(self, ev):
      if ev.Buttons == MB_LEFT:
         threading.Thread(target=self._show_popup).start()
      return False
   
   def _show_popup(self):
      if self.popup is None:
         self._create_popup_menu()
      if not self.popup:
         return
      
      comp = self.doc.getCurrentController().getFrame().getComponentWindow()
      # position of the popupmenu is not considered
      n = self.popup.execute(comp, Rectangle(), 0)
      if n > 0:
         print("selected: %s" % n)
   
   def _create_popup_menu(self):
      smgr = self.ctx.getServiceManager()
      popup = smgr.createInstanceWithContext(
         "com.sun.star.awt.PopupMenu", self.ctx)
      popup.insertItem(1, "item1", 0, 0)
      popup.insertItem(2, "item2", 0, 1)
      
      self.popup = popup

# this is only for a document, just an example
handler = None

def set_mouse_handler(*args):
   """ starting to listen about mouse click on the document. """
   ctx = XSCRIPTCONTEXT.getComponentContext()
   doc = XSCRIPTCONTEXT.getDocument()
   global handler
   handler = MouseClickHandler(ctx, doc)

def remove_mouse_handler(*args):
   """ remove the handler from the document. """
   global handler
   if handler:
      handler.unregister()
      handler = None
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
Apache OpenOffice 4-dev on Xubuntu 14.04
hanya
Volunteer
 
Posts: 885
Joined: Fri Nov 23, 2007 9:27 am
Location: Japan

Re: Pop up menu in a Calc document

Postby Flitec » Fri Sep 16, 2011 8:06 pm

hanya wrote:I meant, in Python:

Thanks Hanya for your answer and support, i have to thank you also because much of the code that i wrote was copied from your website....anyway since i stated i'm not a programmer, so.... it's difficult in basic, almost impossible with other languages...
Thanks again
Paul
Libre Office 5.0.4.2 Windows 7
Flitec
 
Posts: 8
Joined: Wed Sep 14, 2011 11:46 am
Location: Italy

Re: Pop up menu in a Calc document

Postby Villeroy » Fri Sep 16, 2011 8:12 pm

About Villeroy proposal, we need to have a popup menu with submenu items, so if I understood correctly this couldn't be a solution

Did you really have a closer look?
Did you pick a country and then a city which belongs to the selected country?
No code, no sorrow, save as xls and it will work in Excel.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28674
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pop up menu in a Calc document

Postby hanya » Sat Sep 17, 2011 4:33 am

almost impossible with other languages...
Python has some functionality which you have to write yourself in OOo Basic.

Here you are:
Code: Select all   Expand viewCollapse view
Sub RegisterClickHandler()
  doc = ThisComponent
  l = CreateUnoListener("Handler_", "com.sun.star.awt.XMouseClickHandler")
  doc.getCurrentController().addMouseClickHandler(l)
End Sub

Function Handler_disposing(ev)
End Function
Function Handler_mousePressed(ev)
  Handler_mousePressed = False
End Function
Function Handler_mouseReleased(ev)
  If ev.Buttons = com.sun.star.awt.MouseButton.LEFT Then
    async(Array())
  End If
  Handler_mouseReleased = False
End Function

Sub ShowPopup
  oPopup = CreateUnoService("com.sun.star.awt.PopupMenu")
  oPopup.insertItem(1, "item1", 0, 0)
  oPopup.insertItem(2, "item2", 0, 1)
 
  oCompWin = ThisComponent.getCurrentController()._
      getFrame().getComponentWindow()
  n = oPopup.execute(oCompWin, _
      CreateUnoStruct("com.sun.star.awt.Rectangle"), 0)
  If n > 0 Then
    msgbox n
  End If
End Sub


Sub async(args)
  oASync = CreateUnoService( _
     "com.sun.star.awt.AsyncCallback")
  oCallback = CreateUnoListener("Callback_", _
     "com.sun.star.awt.XCallback" )
  oASync.addCallback(oCallback, args)
End Sub

Sub Callback_notify( aData )
  ShowPopup()
End Sub
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
Apache OpenOffice 4-dev on Xubuntu 14.04
hanya
Volunteer
 
Posts: 885
Joined: Fri Nov 23, 2007 9:27 am
Location: Japan

Re: Pop up menu in a Calc document

Postby Flitec » Sat Sep 17, 2011 7:20 am

Thanks for your great help Hanya .....
Paul
Libre Office 5.0.4.2 Windows 7
Flitec
 
Posts: 8
Joined: Wed Sep 14, 2011 11:46 am
Location: Italy


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests