[Solved] Mouse click to select cell in basic Calc macro

Creating a macro - Writing a Script - Using the API

[Solved] Mouse click to select cell in basic Calc macro

Postby GWS » Wed Jul 13, 2011 10:33 pm

Need to select a cell location while a macro is running.

Macro would ask "Select a cell" and wait for a confermation such as mouse click.

Can't input a line # as complete sheet may not show so must be able to scroll up or down to select the desired cell.

Can't seem to get a mouse click working in a macro!

Thanks for any ideas.

Gary
Last edited by GWS on Sat Jul 16, 2011 5:12 pm, edited 1 time in total.
OpenOffice 2.3.1 on Windows Vista
GWS
 
Posts: 137
Joined: Sat Sep 05, 2009 3:46 pm

Re: mouse click to select cell in basic calc macro

Postby Zizi64 » Thu Jul 14, 2011 8:46 am

Hi,

Need to select a cell location while a macro is running.
Macro would ask "Select a cell" and wait for a confermation such as mouse click.

It is not feasible, I think.

You can use the "Inputbox" to enter a cell or cellrange reference. (It is not a mouse action, it is a "Type in, and Confirm" methode...). You will need to be interpreted the typed in text as a cell reference in the Basic code.

...Or you can try to put the selecting procedure before the start of macro.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8133
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: mouse click to select cell in basic calc macro

Postby FJCC » Thu Jul 14, 2011 3:01 pm

I'm not sure I understand exactly what is wanted, but it seems to me a RangeSelectionListener would do what you want. Check out the example here
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7220
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Mouse click to select cell in basic Calc macro

Postby GWS » Sat Jul 16, 2011 3:48 pm

I tried fjcc'c sample but was not able to get it working for some reason. However this prompted some more searching and found a "XRangeSelectionListener" routine that I was able to get working! I think it is very similar to fjcc's.

The last problem is that it returns the selected location as a "sheet,Column,row". I dont' want/need the sheet as I need to address 3 sheets.

The command in question is "aResult = oEvent.RangeDescriptor". I need to determine how to just get the column and row without the sheet. Guess I can use string commands to seperate out just the column and row but that seems excessive.

If you know the command I need it would be appreciated. Will try xray but am still trying to figure that all out.

Thanks for the help

Gary
OpenOffice 2.3.1 on Windows Vista
GWS
 
Posts: 137
Joined: Sat Sep 05, 2009 3:46 pm

Re: Mouse click to select cell in basic Calc macro

Postby Villeroy » Sat Jul 16, 2011 4:31 pm

One of my very first StarBasic modules:
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****
Option Explicit
Private sRangeSelection$,bRangeSelecting As Boolean

Sub test_getRangeSelectionAddress()
Dim oView, sInitStr$, bAuto as Boolean, bSingle as Boolean, sTitleStr$, oTest
   oView = ThisComponent.getCurrentController()
   on error resume next
      sInitStr = oView.Selection.AbsoluteName
   on error goto 0
   bAuto = True
   bSingle = True
   sTitleStr = "Select some cell"& iif(bSingle,"", " or single range")& iif(bAuto, "", " and hit Enter")
   oTest = getRangeSelectionAddress(oController:=oView,sInitial:=sInitStr,sTitle:=sTitleStr,bAutoClose:=bAuto,bCell:=bSingle)
   print oTest.Sheet, oTest.StartColumn, oTest.EndColumn, oTest.StartRow, oTest.EndRow
End Sub

'calls: module-vars bRangeSelecting, sRangeSelection
'return a valid com.sun.star.table.CellRangeAddress from a user's range-selection (or Empty)
Function getRangeSelectionAddress(oController,sInitial$,sTitle$,bAutoClose as Boolean, bCell as Boolean)
On error goto returnEmpty
Dim oListener,aProps(3) As New com.sun.star.beans.PropertyValue
   oListener = createUnoListener("RangeSelection_","com.sun.star.sheet.XRangeSelectionListener")
   oController.addRangeSelectionListener(oListener)
   aProps(0).Name = "InitialValue"
   aProps(0).Value = sInitial
   aProps(1).Name = "Title"
   aProps(1).Value = sTitle
   aProps(2).Name = "CloseOnMouseRelease"
   aProps(2).Value = bAutoClose
   aProps(3).Name = "SingleCellMode"
   aProps(3).Value = bCell
   With oController.getFrame
   'this is required when calling from IDE or other frame in order to avoid endless loop
      .activate
      .getContainerWindow.toFront
   End With
   bRangeSelecting = True
   oController.startRangeSelection(aProps())
   while bRangeSelecting
      wait 500
   Wend
   oController.removeRangeSelectionListener(oListener)
   getRangeSelectionAddress = oController.getActiveSheet.getCellRangeByName(sRangeSelection).getRangeAddress()
returnEmpty:
End Function
'calls: module-vars bRangeSelecting, sRangeSelection
Sub RangeSelection_done(oEv)
   sRangeSelection = oEv.RangeDescriptor
   bRangeSelecting = false
End Sub
'calls: module-vars bRangeSelecting, sRangeSelection
Sub RangeSelection_aborted(oEv)
   sRangeSelection = ""
   bRangeSelecting = false
End Sub
Sub RangeSelection_disposing(oEv)
End Sub
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.x
User avatar
Villeroy
Volunteer
 
Posts: 26967
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [SOLVED]Mouse click to select cell in basic Calc macro

Postby GWS » Sat Jul 16, 2011 5:14 pm

Thanks Villeroy

These two lines are what I needed!

(oController:=oView,sInitial:=sInitStr,sTitle:=sTitleStr,bAutoClose:=bAuto,bCell:=bSingle)
print oTest.Sheet, oTest.StartColumn, oTest.EndColumn, oTest.StartRow, oTest.EndRow

Thanks again to all

Gary
OpenOffice 2.3.1 on Windows Vista
GWS
 
Posts: 137
Joined: Sat Sep 05, 2009 3:46 pm

Re: [SOLVED]Mouse click to select cell in basic Calc macro

Postby Villeroy » Sat Jul 16, 2011 6:28 pm

No, you need the whole module and implement your own call to getRangeSelectionAddress() analog to my test routine.

All this is documented in the API reference for the listener:
http://api.openoffice.org/docs/common/r ... ction.html
where you find the link to all the valid range selection arguments:
http://api.openoffice.org/docs/common/r ... ments.html
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.x
User avatar
Villeroy
Volunteer
 
Posts: 26967
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests