[Solved] Pop up menu in a Calc document

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Flitec
Posts: 8
Joined: Wed Sep 14, 2011 11:46 am
Location: Italy

[Solved] Pop up menu in a Calc document

Post by Flitec »

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

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
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pop up menu in a calc document

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
hanya
Volunteer
Posts: 885
Joined: Fri Nov 23, 2007 9:27 am
Location: Japan

Re: Pop up menu in a calc document

Post by hanya »

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
Flitec
Posts: 8
Joined: Wed Sep 14, 2011 11:46 am
Location: Italy

Re: Pop up menu in a calc document

Post by Flitec »

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

'
'
'
'
                   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
hanya
Volunteer
Posts: 885
Joined: Fri Nov 23, 2007 9:27 am
Location: Japan

Re: Pop up menu in a Calc document

Post by hanya »

I meant, in Python:

Code: Select all

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
Flitec
Posts: 8
Joined: Wed Sep 14, 2011 11:46 am
Location: Italy

Re: Pop up menu in a Calc document

Post by Flitec »

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
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Pop up menu in a Calc document

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
hanya
Volunteer
Posts: 885
Joined: Fri Nov 23, 2007 9:27 am
Location: Japan

Re: Pop up menu in a Calc document

Post by hanya »

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

Here you are:

Code: Select all

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
Flitec
Posts: 8
Joined: Wed Sep 14, 2011 11:46 am
Location: Italy

Re: Pop up menu in a Calc document

Post by Flitec »

Thanks for your great help Hanya .....
Paul
Libre Office 5.0.4.2 Windows 7
Post Reply