Ask Your Question

How to run a macro on leaving a special cell (by return - if possible)?

asked 2017-05-10 15:20:05 +0200

inJesus gravatar image

updated 2017-05-10 19:27:14 +0200

Would like to implement this ...

Additionaly the formulas which reference to the cell shall have the time to recalculate before the (sorting) macro is run ...

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-05-10 17:08:10 +0200

librebel gravatar image

updated 2017-05-10 21:40:46 +0200

Hello InJesus, you could implement an XModifyListener ( no FocusListener for cells ...). For Example:

Global oModifyListener
Global oCell

REM call this method once to set the ModifyListener.
REM to destroy the listener, call oCell.removeModifyListener( oModifyListener )
Sub SetModifyListener()
REM ( change "A1" to the cell which should trigger the callback if modified )
    Dim strCellAddress : strCellAddress = "A1"      REM Your Cell Address here.
    oModifyListener = createUnoListener("CellModify_","")
    oSheet = ThisComponent.CurrentController.ActiveSheet
    oCell = oSheet.getCellRangebyName( strCellAddress )
    oCell.addModifyListener( oModifyListener )
End Sub

Sub CellModify_modified( oEvent )
    Msgbox "Modified"
End Sub

Sub CellModify_disposing( oEvent )
    Msgbox "Disposing"
End Sub
edit flag offensive delete link more


Thank you.

The Sheet-Event "Formula calculated" with my sorting macro kills the file.

The other Sheet-Events would happen with each cell so not usable if I want it only be triggered for one cell.

The XFocusListener and your code are beyond my understanding.

inJesus gravatar imageinJesus ( 2017-05-10 19:26:55 +0200 )edit

@inJesus you're right, these events are global for the Sheet and are triggered often. And calling an inspector or messagebox in the event handler might cause an undesirable result! Hence the need for testing if a particular cell triggered the event, as in the example above.

librebel gravatar imagelibrebel ( 2017-05-10 19:44:29 +0200 )edit

@inJesus, i updated my previous answer and included a example for a XModifyListener instead of XFocusListener, since the latter appears to be not available for cells.

librebel gravatar imagelibrebel ( 2017-05-10 21:46:19 +0200 )edit

Thank you. Before I try this ... Why Global? Would it be OK to do it with Dim? Would you post the renewed version? Where is your knowledge from? I do not find ModfiyListener in Andrews Basic Guide. What program language did you use? I guess you tried your solution yourself before you posted it?

inJesus gravatar imageinJesus ( 2017-05-11 13:10:46 +0200 )edit

Hello inJesus, yes i tried this solution myself ( and it works fine for what it's worth: a messagebox pops up after celll A1 is updated ). A Global variable keeps its value after the associated macro has executed, A public domain variable ( defined with Dim or Public ) is only available so long as the associated macro is executing and then the variable is reset. ( See Scope and Life Span of Variables ) i haven't tri

librebel gravatar imagelibrebel ( 2017-05-11 17:57:46 +0200 )edit

i haven't tried Dim in this case, perhaps it also works. In this case i just adapted an example from FJCC on the OpenOffice Forum. Also Andrew Pitonyak has a big chapter about Listeners in his document called "AndrewMacro.odt" ( chapter 14 )

librebel gravatar imagelibrebel ( 2017-05-11 18:20:21 +0200 )edit

Thank you. Great you have tried it yourself. I need to be able to comprehend code before implementing it. To have some security and not ruin my system. For instance find a description in the internet. I did not find modifylistener with Basic. So I asked about the code language ... So you don't know it, I guess, as you have not answered this. OK. "Global" ist running all time so I will only use it if I really know why. I have updated myself about "global" in Andrew's guide before I wrote to you.

inJesus gravatar imageinJesus ( 2017-05-11 18:47:01 +0200 )edit

You're welcome @inJesus. Yes this code is all in BASIC. Just try it out if it works, this won't ruin your system.

librebel gravatar imagelibrebel ( 2017-05-11 19:46:39 +0200 )edit

No. As I told you above.

inJesus gravatar imageinJesus ( 2017-05-11 19:49:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-10 15:20:05 +0200

Seen: 128 times

Last updated: May 10 '17