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

[Alex6361]

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?

[FJCC]

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.

[Alex6361]

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.

[Villeroy]

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)

[Alex6361]

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!

[RoryOF]

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.

[Villeroy]

Non-modal dialogs are possible. viewtopic.php?f=20&t=74660&p=338153#p338153

[dbmest]

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?

[acknak]

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?

[dbmest]

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?

[Villeroy]

What's wrong with the above RangeSelect.ods?

[dbmest]

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.

[FJCC]

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?

[dbmest]

Thank you. I spent a week trying to operate the button in a dialog. Wed God bless you!

[Villeroy]

Stop waisting your time trying. Start learning.
Yes, I am patronizing.