Problem with combo box

users
#1

Hello,
I tried to define a combo box in Scalc which take its source cell range from a table. When I enter the cell reference directly, it works as expected. I defined a name for the table, but when I try to used it instead of the direct cell reference, I just get a blank combo box. I tried several syntax like 'name', ='name' but that doen't make any difference. I tried that both under version 3.3.3 (on Windows XP Pro 32 bit) and version 3.4.2 (on Windows 7 64 bit) as well as OpenOffice v3.3.0:
That doesn't make any difference either.
Is there a way to make that works?
(In fact the original file was made with MS Excel XP)
Thanks in advance,
Jean-Louis

#2

Am 15.08.2011 11:37, Jean-Louis Oneto wrote:

Hello,
I tried to define a combo box in Scalc which take its source cell range
from a table. When I enter the cell reference directly, it works as
expected. I defined a name for the table, but when I try to used it
instead of the direct cell reference, I just get a blank combo box. I
tried several syntax like 'name', ='name' but that doen't make any
difference. I tried that both under version 3.3.3 (on Windows XP Pro 32
bit) and version 3.4.2 (on Windows 7 64 bit) as well as OpenOffice v3.3.0:
That doesn't make any difference either.
Is there a way to make that works?
(In fact the original file was made with MS Excel XP)
Thanks in advance,
Jean-Louis

Don't use form controls with spreadsheets. They are made for database input in the first place.
menu:Data>Validation...
Accept: <cell range>
... does the trick accepting any formula expression, even calculated references to dynamic ranges.
When you turn off the error message, you get the same behaviour as a combo, which is just a text box with auto-complete suggestions.
Unlike form controls, this spreadsheet variant of a combo box can be copied across ranges.
Hope this helps,
Andreas

#3

Am 15.08.2011 11:37, Jean-Louis Oneto wrote:

Hello,
I tried to define a combo box in Scalc which take its source cell range
from a table. When I enter the cell reference directly, it works as
expected. I defined a name for the table, but when I try to used it
instead of the direct cell reference, I just get a blank combo box. I
tried several syntax like 'name', ='name' but that doen't make any
difference. I tried that both under version 3.3.3 (on Windows XP Pro 32
bit) and version 3.4.2 (on Windows 7 64 bit) as well as OpenOffice v3.3.0:
That doesn't make any difference either.
Is there a way to make that works?
(In fact the original file was made with MS Excel XP)
Thanks in advance,
Jean-Louis

Don't use form controls with spreadsheets. They are made for database input
in the first place.

If something should not be used, it should not exist. Why is it there
if it's not supposed to be used?

menu:Data>Validation...

Mine is grayed out…

Accept: <cell range>
... does the trick accepting any formula expression, even calculated
references to dynamic ranges.
When you turn off the error message, you get the same behaviour as a combo,
which is just a text box with auto-complete suggestions.
Unlike form controls, this spreadsheet variant of a combo box can be copied
across ranges.
Hope this helps,
Andreas

Regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

#4

In fact I had found a very similar thread (with a very similar answer...) in the OO forum:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=38322&p=175957&hilit=combo+box#p175957
but the problem is that in either case I am unable to understand the solution.: I was able to define a Data:Validation..Accept:CellRange, but only if I'm not in Form creation mode, otherwise all the entries of the Data mennue are greyed out. And if define a CellRange with a name, then I'm unable to use it in the combo box (or a list box)

I don't understand either why there are controls in Scalc which are supposed to be left unused. And with all the hectic thread about Base and databases, I even feel that if I should avoid something, that would be... databases! Besides that, I just need to choose a set of parameters from list of less than ten entries, and I would feel like overkill to build a database for that!

-- Jean-Louis Oneto e-mail: jl.oneto@free.fr

#5

There is more than one way and you are free to do whatever you want. It is just me who suggests a better solution. Form controls used to be in there since version 1.
As a matter of fact, most of their functionality is tailored for database fields. Form controls used to be in Excel since many years, so they added the interfaces that are needed to bind them to sheet cells.

Data validation by cell ranges has been introduced in version 2.0 because it is in Excel and list/combo boxes are inappropriate in many cases. In version 2.3 they added support for validation by dynamic cell ranges (OFFSET, INDEX, INDIRECT and such).

A typical example of what most people want to do:

http://user.services.openoffice.org/en/forum/download/file.php?id=8592

refer to the first sheet, columns labeled "Category" and "Name".
We can not know what you are trying to do exactly, but certainly you don't want 1000 form controls.

Another example of the dynamic ranges that do work since version 2.3:

#6

I have never seen that validation thing before, but I can see it is
useful, and it will probably solve a few of my problems I've had that
I previously gave up on… :smiley:

Still there are cases when form controls are the right way to go, for
example when needing a way to start a macro manually with a button or
something.

Best regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

#7

HI :slight_smile:
A classic OpenSource answer. An initial statement saying something can't be
done. Then giving 3 ways of doing it and alternatives to achieve the goal a
different way, followed by advice as to why it's not a good idea. All done in
one neat post too :slight_smile: Ok, maybe just 1 or 2 ways this time but still a classic
OpenSource thread.

Regards from
Tom :slight_smile:

#8

HI :slight_smile:
A classic OpenSource answer.  An initial statement saying something can't be
done.  Then giving 3 ways of doing it and alternatives to achieve the goal a
different way, followed by advice as to why it's not a good idea.  All done in
one neat post too :slight_smile:  Ok, maybe just 1 or 2 ways this time but still a classic
OpenSource thread.

And a small amount of healthy debate… :smiley:

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

#9

One major problem in the LibreOffice community is the lack of knowledge about
the built-in features inherited from 2 decades StarOffice/OpenOffice.org
development.
This leads to invalid bug reports, pointless feature requests, nonsense
macros, even newly implemented features disregarding the rest of the
program.

Tom wrote:

#10

Johnny Rosenberg wrote:

I have never seen that validation thing before, but I can see it isuseful,
and it will probably solve a few of my problems I've had thatI previously
gave up on… :DStill there are cases when form controls are the right way
to go, forexample when needing a way to start a macro manually with a
button orsomething.Best regardsJohnny Rosenberg

The feature exists since decades. It is possible to call validation macros.
Validation can also be used as a simple alternative to modify listeners.
http://user.services.openoffice.org/en/forum/viewtopic.php?f=21&t=12575
[Calc,Python,Basic,Base] Several ways to time stamps

#11

Johnny Rosenberg wrote:

I have never seen that validation thing before, but I can see it isuseful,
and it will probably solve a few of my problems I've had thatI previously
gave up on… :DStill there are cases when form controls are the right way
to go, forexample when needing a way to start a macro manually with a
button orsomething.Best regardsJohnny Rosenberg

The feature exists since decades.

Ok, so I'd better kill myself then. Thanks.

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

#12

Hi :slight_smile:
Noooo, who would there be to argue with then? Errr, i mean discuss.
Regards from
Tom :slight_smile:

#13

Hi :slight_smile:
Noooo, who would there be to argue with then?  Errr, i mean discuss.

Fortunately saying something and doing it is sometimes not the same thing… :stuck_out_tongue: