Ask Your Question

Calc - detect landing on a particular cell and triggering an event

asked 2015-12-02 18:37:58 +0100

Bill Gradwohl gravatar image

I want to trigger an event when a particular cell becomes the active cell.

Here's the situation:

After entering a payment in a specific cell (say $10 in A5) and hitting enter, I'd like to programatically put the current time as hidden "userdata" in the cell immediately below it (A6) - the new active cell. I'd like to detect leaving that cell (A6) or entering the cell below (A7) without modifying the visible contents of A6 or A7.

I want to use the elapsed time between the enter keystrokes to change the way the sheet reacts.

$10 (enter) (immediate enter) Signals that the transaction is over and a macro sets up for the next transaction.

$10 (enter) (wait a second) (enter) Signals the transaction isn't over and just sits there.

The time difference between the double enter keystrokes determines what happens next.

This spreadsheet has 6600 lines of macro code that makes it function, and I'm already relying heavily on event handling, but it only works on a modified cell. In this case, I'm not modifying A6 or A7, but I want to trigger an event that lets me know they have become the active cells.

edit retag flag offensive close merge delete

1 answer

Sort by » oldest newest most voted

answered 2015-12-04 10:57:52 +0100

pierre-yves samyn gravatar image

updated 2015-12-04 11:28:44 +0100


For ergonomic reasons I am very cautious about changing the habitual behavior of the interface.

You do not say if you already practice in programming... What you desire is complicated.

Sheets manage events that can be associated to macros: Right click the sheet's tab▸Sheet Events.

For example you could associate a macro to the event Selection changed but this may become very complicated: you have to test the nature of the current selection (is it a cell?), whether it comes from cell A5 (it is necessary to store this information), etc.

Another approach might be to use a listener XCallback as in the InsertNow.ods attached. The function call is made in cell A1. The first parameter is the cell to be inspected (A5, the one where you entered the amount) and the second cell "target" address ("A6", the one in which you desire to insert time).

Option Explicit

Function InsertNow(Watch As Double, Target as string)
Dim ac As Object, oCallback As Object

ac = CreateUnoService("")
oCallback = CreateUnoListener( "callback_", "" )
ac.addCallback( oCallback, array(Watch, Target))

End Function

Sub callback_notify(Pys)
Dim oSheet As Object, oCell As Object

oSheet = ThisComponent.Sheets.getByName("Sheet1")
oCell = oSheet.getCellRangeByName(Pys(1))

if pys(0) = 0 then
    oCell.string = ""
    if oCell.Value = 0 then
        oCell.Value = now
    end if  
end if

End Sub

As you can see from this code is not simple. And this only manages cell update after edit another. I do not even address the management of the elapsed time.


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-12-02 18:37:58 +0100

Seen: 232 times

Last updated: Dec 04 '15