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

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

5572 ●15 ●30 ●48

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

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_","com.sun.star.util.XModifyListener")
    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

Comments

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 ( 2017-05-10 19:26:55 +0100 )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 ( 2017-05-10 19:44:29 +0100 )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 ( 2017-05-10 21:46:19 +0100 )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 ( 2017-05-11 13:10:46 +0100 )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 ( 2017-05-11 17:57:46 +0100 )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 ( 2017-05-11 18:20:21 +0100 )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 ( 2017-05-11 18:47:01 +0100 )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 ( 2017-05-11 19:46:39 +0100 )edit

No. As I told you above.

inJesus ( 2017-05-11 19:49:28 +0100 )edit

add a comment

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

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-03 21:13:24.497478

1 Answer

Comments

Question Tools

Stats

Related questions

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

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-10-03 21:13:24.497478

1 Answer

Comments

Question Tools

Stats

Related questions

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

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-03 21:13:24.497478