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