[Solved~] Obtaining a Cell Range Selection from a Dialog

Creating a macro - Writing a Script - Using the API

[Solved~] Obtaining a Cell Range Selection from a Dialog

Postby Alex6361 » Mon Jun 22, 2015 4:56 am

Using Basic I would like to display a Dialog which asks a spreadsheet user to select a cell or cell range on a spreadsheet, but don't see any type of dialog control which allows that kind of user interaction. The controls all seem to be geared for text of one kind or another, and retain focus without allowing the user to temporarily shift the focus to the spreadsheet in order to select something there. Yes, I know I can ask the user to type in a cell range address, but that takes longer and is prone to errors. Is there a way to do this? Or, is there a way within Basic to access the built-in LibreOffice dialogs (like the one obtained from Insert > Names > Manage) and obtain user input from them? As evidenced by that example, LibreOffice has a way to let a user select something external to the dialog from a dialog control. Does the Basic programmer have such an option?
Last edited by Alex6361 on Wed Jun 24, 2015 11:47 am, edited 1 time in total.
LibreOffice Version: 5.0.0.5
OpenSUSE Linux 3.11.10-29 & Windows XP
Alex6361
 
Posts: 24
Joined: Fri Jun 05, 2015 2:15 am

Re: Obtaining a Cell Range Selection from a Dialog

Postby FJCC » Mon Jun 22, 2015 5:24 am

This can be done with a RangeSelectionListener. The attached file contains code I wrote long ago for a larger application. Click the button on Sheet1 and when the little window appears, select a cell range. When you release the mouse button, the little window will disappear and the macro will print the selected range.
Attachments
RangeSelect.ods
(10.4 KiB) Downloaded 215 times
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: 7650
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Obtaining a Cell Range Selection from a Dialog

Postby Alex6361 » Mon Jun 22, 2015 11:58 am

FJCC - Thanks for the reply and example, which does exactly what you said it does. I was hoping to use a control field on my own dialog, but decided I could live with the separate pop-up box which the RangeSelectionListener creates. However, I was unable to select entire rows or columns, which is what I need to do. Perhaps I'm not doing something correctly, since it seems inexplicable that the creators of this listener process would omit this capability.
LibreOffice Version: 5.0.0.5
OpenSUSE Linux 3.11.10-29 & Windows XP
Alex6361
 
Posts: 24
Joined: Fri Jun 05, 2015 2:15 am

Re: Obtaining a Cell Range Selection from a Dialog

Postby Villeroy » Mon Jun 22, 2015 12:55 pm

Another problem with the range selection object is that Cancel does not cancel, i.e. it does not trigger the aborted() routine. To cancel the the range selection, the user needs to click the window's [X] button. This is a known bug already: https://bz.apache.org/ooo/show_bug.cgi?id=121904
For a single column I would prompt for a single cell (property "SingleCellMode"=True) and then get objRange.Columns.getByIndex(0)
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: 28341
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Obtaining a Cell Range Selection from a Dialog

Postby Alex6361 » Wed Jun 24, 2015 11:45 am

Thanks, Villeroy, for the additional info on the RangeSelectionListener. Since I didn't find a Cancel button on the pop-up, I hadn't realized that it wouldn't work if it was there, and just used the [X] by instinct. I also didn't care for the default CloseOnMouseRelease = true and changed that to false, so the users could change their minds if the feedback they got from RangeSelectionListener wasn't what they expected. Hitting [Enter] when satisfied with the selection then finished the job. I also figured I could use a set of Radio Buttons on the Dialog I presented first so the user could indicate whether they wanted entire rows, columns, single cell, or simple cell range, and then interpret the selection accordingly. So, I was given a way to achieve what I asked for initially, and I appreciate that!

But, I go away not really satisfied! It seems, from my attempts thus far to understand the controls available to me that none fully takes into account how users work with documents, and especially spreadsheets. What I would expect is some kind of dialog control entry field with the ability to select text, a single cell, a cell range, columns, rows, etc. using the same techniques I'd use if I was entering something directly into a cell on a spreadsheet. I should, for example, be able to enter an = in the control field (as I might do directly in a cell) , and then compose a formula by a combination of keyboard entries and cell selections. From my experiments so far, I can't even select and copy a bit of text elsewhere in the document and paste it into a dialog text box. I can select and copy text anywhere outside the document (from which I've launched a dialog) and paste that text into the control field, but that's all. I can't temporarily shift focus from the dialog to anyplace on its document (in order to select and copy something). Had I not been doing all this for twenty years with Excel and its VBA, maybe I wouldn't expect it and therefore be satisfied with things like RangeSelectionListener, which seems to me to be more of a work-around than a true solution to constructing dialogs to help users enter spreadsheet data more easily with less errors.

Whether I like the answer or not, I'm still grateful to people like you and others on this forum who generously share their knowledge and experience with those of us who are trying to climb a very steep learning curve with a the OO API and a somewhat primitive IDE. It's better to know soon that you can't do something you want to do, than spend days of effort searching through the documentation for the answer when you don't even know what you need is called! From reading the description of RangeSelectionListener, for example, I would never have guesed how it works or that it might be a way to do what I wanted to do. Thanks!
LibreOffice Version: 5.0.0.5
OpenSUSE Linux 3.11.10-29 & Windows XP
Alex6361
 
Posts: 24
Joined: Fri Jun 05, 2015 2:15 am

Re: [~Solved] Obtaining a Cell Range Selection from a Dialog

Postby RoryOF » Wed Jun 24, 2015 1:40 pm

If you have some programming experience and want a project, you might consider writing an extension that does what you outline.

You should refer to Andrew Pitonyak's site for his books on OO/LibO macros which form a good starting point. You may also need to dive into the OpenOffice/LibreOffice API, although it is possible that between Andrew's works and the help you will receive here you may be spared that.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31090
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [~Solved] Obtaining a Cell Range Selection from a Dialog

Postby Villeroy » Wed Jun 24, 2015 1:45 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: 28341
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved~] Obtaining a Cell Range Selection from a Dialog

Postby dbmest » Sun Sep 27, 2015 10:07 pm

Eu pretendo divulgar uma extensão que precisa selecionar um intervalo de célular com um clique no botão de outro formulário, mas estou tentando há vários dias e não consegui. Alguém pode, por favor, indicar uma solução?
Lubuntu 15.04 and LibreOffice 4.4
dbmest
 
Posts: 4
Joined: Sun Sep 27, 2015 9:26 pm

Re: [Solved~] Obtaining a Cell Range Selection from a Dialog

Postby acknak » Mon Sep 28, 2015 2:35 pm

Welcome to the community forum!

You're much more likely to get an answer if you can post in English. Sorry, we don't have a Portuguese-language forum; the available languages can be accessed here: forum.openoffice.org

Here's what Google makes of the post:
I plan to publish an extension that needs to select a cell range by clicking the button of another form, but I'm trying for several days and could not. Can someone please indicate a solution?
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved~] Obtaining a Cell Range Selection from a Dialog

Postby dbmest » Tue Sep 29, 2015 11:44 pm

Thank you. I plan to publish an extension that needs to select a cell range by clicking the button of another form, but I'm trying for several days and could not. At the Forum, the solution uses a form button in the spreadsheet, but when I call the macro to a button on another form simply does not. Can someone please indicate a solution?
Lubuntu 15.04 and LibreOffice 4.4
dbmest
 
Posts: 4
Joined: Sun Sep 27, 2015 9:26 pm

Re: [Solved~] Obtaining a Cell Range Selection from a Dialog

Postby Villeroy » Wed Sep 30, 2015 12:41 am

What's wrong with the above RangeSelect.ods?
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: 28341
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved~] Obtaining a Cell Range Selection from a Dialog

Postby dbmest » Thu Oct 01, 2015 3:51 am

when I put the RangeSelect.ods spreadsheet button in a dialog and call the main macro for selection of the range in the spreadsheet does not.
Lubuntu 15.04 and LibreOffice 4.4
dbmest
 
Posts: 4
Joined: Sun Sep 27, 2015 9:26 pm

Re: [Solved~] Obtaining a Cell Range Selection from a Dialog

Postby FJCC » Thu Oct 01, 2015 4:30 am

I added a dialog from which to call the range selection and a text field to show the selection. Is that what you were looking for?
Attachments
RangeSelectionFromDialog.ods
(11.69 KiB) Downloaded 196 times
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: 7650
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved~] Obtaining a Cell Range Selection from a Dialog

Postby dbmest » Thu Oct 01, 2015 5:10 am

Thank you. I spent a week trying to operate the button in a dialog. Wed God bless you!
Lubuntu 15.04 and LibreOffice 4.4
dbmest
 
Posts: 4
Joined: Sun Sep 27, 2015 9:26 pm

Re: [Solved~] Obtaining a Cell Range Selection from a Dialog

Postby Villeroy » Thu Oct 01, 2015 2:24 pm

Stop waisting your time trying. Start learning.
Yes, I am patronizing.
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: 28341
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 2 guests