Selection Changed Event

[Vimal]

I want to implement a macro that mimics the goto function in ms excel. In excel if i press F5, goto box opens with previous selected cell in the address bar. If I press enter, it goes to the previously selected cell.

The F5 key in Libreoffice does not have this function. So everytime a cell is selected, i want to use a Selection Changed Event Listener to store the selected cell. For this i need a macro code.
Please give full code and instructions. I have used MS Excel and its macro coding but LibreOffice is new to me and really want to shift my entire office staff to libre office.

Using this stored cell address, i will use a macro assigned to a shortcut key to go to that previous cell.

[karolus]

Hallo
In a reasonably sensible workflow, the last cell in each case can be reached in a simple way using the arrow keys!

[JohnSUN]

Welcome @Vimal !
I will gladly give you a method for solving such a problem. But let’s clarify this point:

@Vimal

So everytime a cell is selected

What should the macro do if more than one cell is selected - a range or several non-contiguous ranges? When you go back, do you want to jump to the former active cell or select a range, like you did last time? (I call the “active cell” the one cell that is the only one on the sheet, usually surrounded by a thick border)

[Vimal]

Thanks JohnSun for your encouraging words.

It is enough that the active cell is stored. The entire selection need not be stored.

This is the use case scenario
I am in sheet2 cell A1. Assuming A1’s formula is linked or depends on sheet1 A1. So if I press ctrl+[ the focus goes to sheet1 A1 from sheet2 A1. Now I should be able to press a key combination that should take me back to sheet2 A1. (in excel it is F5 and enter).

So it is enough that previous selected active cell is stored and on pressing a key combination focus is set to that active cell.
I hope it is clear.
Thank you very much.

[JohnSUN]

Yes, thanks for the answer - it was important to me. The fact is that the algorithms for “return to the previous cell” and “return to the last selection” are quite different in the part where it is checked whether the current position should be saved as a new value.

Okay, please look at this file - GoToPrevActiveCell_demo.ods (13.6 KB)

Now the macros for moving between cells are assigned to the buttons on the toolbar, after the Undo/Redo group

You can easily assign keyboard shortcuts to them (for example Alt+F5 and Alt+Shift+F5) using the menu Tools - Customize - Keyboard tab

I don’t know how well you read the code, so I don’t know how much detail you need to explain how and what the macro does. Please try to read the program and if you have any questions - just ask, I will try to explain the unclear parts of the code.
The program uses the very old ActiveCell() function described here

The global array variable aPrevSelections() As Variant stores the positions of the last visited cells (and current cell also)
The constant MAX_STORED_ADDRESSES = 5 determines how big this array will be (how many moves back you can make). You can increase or decrease this value as you wish.

[Vimal]

Hi JohnSUN

Thanks for your code and file. It is working and I can customise it to my requirements.
But my requirement is the code should not be stored in one .ods or any format file.
The macro should work in all new files and any file being opened. It should be part of the Calc Application and should not be stored in any file or depend on any file. We use .xlsx and .xls files only. The macro should not be stored in these files but should be stored in the application Module itself.

I tried copying and pasting the code in Standard-Module 1 but it is not working or getting activated.
Could you please let me know what I should do to make the macro work in all files without saving the macro in individual files.

[JohnSUN]

Oh, this complicates things a bit. Preparing a detailed answer would take a lot of time. So now I’m just going to state a few theses.
Yes, you can make a universal tool for any file that Calc will open. It’s called "extension"
The executable part of the code will remain almost the same as it is now - it may have to be supplemented with remembering the names of the workbooks so that clicking on “jump back” does not force the macro to look for non-existent cells. In addition, you will need to take care of assigning a “Selection Changed” event listener for each sheet of the spreadsheet that is opened.
If it is enough to remember only one previous position, namely to return to the cell with the formula after Mark Precedents, then perhaps the macro can be simplified - don’t use the “Selection changed” event, but simply replace the hot key for the built-in command (Ctrl + [) with a macro call that will remember the position and execute .uno:MarkPrecedents . And the second macro will return the selection to the previous cell.