[Semi Solved] JUMP! Go ahead and (help me) Jump!

[imadumby]

Ok, so I had this thread titled “Can I move a selection to another cell when a cell changes?“. You can see it at viewtopic.php?f=9&t=105367. It got too long and confusing.

I've gotten a lot of help, and I was told it worked elsewhere but I just cant get it to work on my computer and cant figure out why. Basically, I'm trying to figure out how to make a cell that is selected “make a jump” to a new cell when a value in a certain cell changes.

Click the link below to see what it's all about, and thanks in advance for any help!

[RoryOF]

This is only a pointer to where help might lie towards a solution:

in Andrew Pitonyak's OpenOffice.org Macros Explained.odt V3, section 10.12.2 is entitled " 10.12.2. A complete listener: selection change listener".

Out of curiosity, what happens if your selection has already moved to another cell (presuming you had achieved your jump objective), and the event is triggered once again?

[imadumby]

Sorry, but I just don't understand why this is a concern. I just don't know how it could possibly happen.

In the spreadsheet I'm trying to build, if the first 8 entries into it happen to be a certain (very rare) combination, the macro is supposed to work. But if that combination doesn't "hit" then B1 will never equal "1" because there is nothing else in the spreadsheet that will make it equal 1. And for the macro to work B1 as to equal 1.

Like this:
Suppose the inputs are put in column C (c1, c2, c3, c4, etc all the way down to c20). Lets say the inputs from C1 to C8 all equal 1.
Then lets say the formula in B1 is this: =IF(SUM(C1:C8)=8,1,0) and you have already entered all the inputs from C1 to C8 and they all equal 1 and they cant be changed, and you are now entering the next input (which would be into cell A26 since that is now where the selection is), how in the world will the macro ever be triggered again?

I probably didn't do a good job explaining it in the beginning but I hope this makes it a little easier to see.

[imadumby]

Is that what Lupp's solution provided? A "listener?"

[Zizi64]

Lupp's solution works without any user defined listener. It works by the Recalculation feature: the Cell functions will be recalculated on the change of any parameter, what you passed to the function.

You can not modify the content of an another cell by a user defined Cell Function. It is not possible in the Calc.
It can modify only the content of the cell, where the function was called. (See the string output of the sample function)


But - as Lupp's sample show it for you - the user defined Cell Function can modify the Selection (and some other properties) in a spreadsheet.


If you want to modify the content of an another cell, you must launch your macro in another way. For example you can launch your macro by a Listener (or by a Push Button, or by a custom Menu item, or by a Hotkey, etc...) The Listener will listen the content of the desired cell or the desired Cell range, and when the content will be changed, the Listener will launch the macro.

One cell content-related Listener exists by default in the Events list of the Calc:

Code: Select all

'Modified' status was changed

You can assign your macro to it. But it will be raised once (after you saved the document), and it raise on change of ANY Cell of the spreadsheet document. Then this flag will be active until you save the document again (or until you switch it off by a macro), therefore it can not trigger on an another change.


My suggestion: Study and use the Lupp's solution, or take a lot more time (which can take years!) to learn macro programming in detail.

[Zizi64]

Here is a screenshot about the Events list of the LibreOffice Calc:

Tools - Customize - Events

Click on the Image for a better view. (It is a 64 color lossless compression .png file.)

[Villeroy]

Why so complicated? And what is the purpose of all this?

[imadumby]

(Villeroys post)

WTH?!?

Your example -ALMOST- does what I need it to do except it just needs to make the jump automatically (when B1=1) rather than with a link click.

However this does give me another fascinating idea here!

[imadumby]

Lupp's solution works without any user defined listener. .....


My suggestion: Study and use the Lupp's solution, or take a lot more time (which can take years!) to learn macro programming in detail.

Hopefully the solution lies in what your talking about, because to spend years learning Macro programming just to solve this problem doesn't seem like a good time investment. Thats why I think the users here should have a lot of gratitude for you guys here. Can you just imagine the time you've saved them? God bless you all!

For now though I think I can work with Villeroy's example and create something that I believe can be an even better solution to my problem.

Thanks, and I will now consider this thread "Semi-Solved"!

[Villeroy]

Jumping to some data set based on selected values is what database forms do.

Download download/file.php?id=11250
Open the animals form.
There is a form navigation toolbar to navigate from animal to animal by means of arrow buttons. The last button on that toolbar displays an additional list view on the animals.
The orange section shows the persons which are related to the selected species. You can modify, delete or add persons.
The yellow section shows the things that belong to the selected person. You can modify, delete or add things.
Same with the forms for persons and things. One person can have many things but only one animal. The persons, things and animals are stored in separate tables, table P_T maps multiple persons to multiple things and the forms allow to edit all the related tables at once.
There is also a report showing all the information in a printable page layout.
This is only a very simplistic example of a database demonstrating how to implement one-to-many relations (animal<--person) and many-to-many relations (persons <--> things).