[Solved] Make a macro rerun on data change in a Calc sheet

[Newoldie]

I have written a macro that reads values from cells on a calc sheet , processes them and outputs the results to other cells on the sheet.This works fine but I would like the macro automatically run if I change the value that is in one of the "input" cells.
Is this possible and if so how?

Thanks,Dave

[FJCC]

Listeners are used to detect changes and take some action. Here is a simple set of routines that put a Modify Listener on the cell range A1:A10 of sheet1. When a cell in that range is changed, the procedure CalledRoutine is invoked. CalledRoutine simply increments the value of the cell C2. The Listener is associated with the string "Modify_" which is used as a prefix on the name of the two methods associated with the ModifyListener. The "modified" method is the one called whenever a cell in A1:A10 is changed. The "disposing" method is not usually needed, but since the method exists, it is best to put in procedure to account for it. If the routine called by the listener modifies one of the cells in the range covered by the listener, you have to remove the listener before any action is taken, or you will get an infinite loop.
I used the Tools->Customize -> Events menu to trigger the AddListener procedure on the Open Document event and to trigger RmvListener on the Close Document Event.

Code: Select all

Private oListener as Object
Private CellRng as Object

Sub AddListener
Dim Doc, Sheet, Cell as Object

Doc = ThisComponent
Sheet = Doc.Sheets.getByIndex(0)  'get leftmost sheet

CellRng = Sheet.getCellrangeByName("A1:A10") 

oListener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")  'create a listener
Cellrng.addModifyListener(oListener)  'register the listener

End Sub

Sub Modify_modified(oEv)  'macro jumps here when oListener detects modification of Sheet
CalledRoutine
End Sub

Sub Modify_disposing(oEv)
End Sub

Sub RmvListener
CellRng.removeModifyListener(oListener)
End Sub

Sub CalledRoutine
Doc = ThisComponent
Sheet = Doc.Sheets.getByIndex(0)
Cell = Sheet.getCellByPosition(2,1)
CurrentVal = Cell.Value
Cell.Value = CurrentVal + 1

End Sub

[davea0511]

Nice. THX! This is a great example put in terms that makes it easy to follow and master how to add a listener at the right time and place, and remove it as needed.

[belchergb]

This code is fabulous! It introduced me to the whole idea of Listeners.

I ran into one problem, though. PRIVATE does not work for me. I had to go all the way to GLOBAL. Maybe LibreOffice works differently, or maybe it has changed since 2010, but I doubt either case. I fear it was simply a mistake in the code given.

I mention this in the hope that it saves some other poor coder the hours of hair-pulling it cost me.

Thanks for the work, never the less.

[FJCC]

I just tried the posted code with no modifications and it works for me in AOO 4.1.1

[GrimmSpector]

This is great, but where can I find what data is passed to oEv by the listener?

[belchergb]

My code uses a Modify listener. When the cell to which it is attached is changed, the "myListenerName_modified(event)" is called. The cell on which the modification was made will be at "thisComponent.currentSelection". The data you want will be on that cell as "value", "text", whatever is appropriate for the kind of cell you are looking at.

[Villeroy]

This is great, but where can I find what data is passed to oEv by the listener?

MRI or XRay tells you everything about the listener.

Instead of the XModifyListener which needs to be created during runtime, you can declare a script event:
Right-click on sheet tab > SheetEvents...
Event: Content Changed
Point the macro to MyMacros.MRILib.Module1.Mri

Modify a cell (Enter) or a cell range (Alt+Enter) or an array formula (Ctrl+Shift+Enter)

[belchergb]

I just learned about SheetEvents. Thank you Villeroy. This is even better!

[Villeroy]

My code uses a Modify listener. When the cell to which it is attached is changed, the "myListenerName_modified(event)" is called. The cell on which the modification was made will be at "thisComponent.currentSelection". The data you want will be on that cell as "value", "text", whatever is appropriate for the kind of cell you are looking at.

The CurrentSelection can be a single cell, a range of cells, multiple ranges of cells or any kind of shape. Only the single cell has a value, formula and string.

The event's object is the calling object:

Code: Select all

Sub myListenerName_modified(event)
GlobalScopeBasicLibraries.loadLibrary("MRILib")
MRILib.Module1.Mri event.Source

[jknepper]

This routine works for changes to one cell or several contiguous sells on a spreadsheet just fine. I have a different challenge. I have one sheet with 15 different fields that are interrelated, and when some of them are changed independently, or when two or three of them are changed in conjunction with each other, I want to have a “recalc” routine triggered. I could, of course, define a series of “CellRng” definitions (CellRng_Gender, CellRng_Plan, CellRng_ZipCode, CellRng_Tobacco, etc.) but that isn't very elegant.

I have a range of cells that translate each field into a number, and when I add all the numbers together and compare with the total that I had calculated before any changes were made, I set a flag. “1” means that there has been a change to those trigger fields that requires a recalculation, “0” means that no recalculation is required (I set the “1” to “0” after the recalculation has been done).

Is there a way to trigger a macro to run when a field is changed (my “flag” field), when that field is not actually changed via the keyboard? In other words, I want that recalculation to happen when the sum of the trigger fields has changed the flag from a “0” to a “1”.

[Villeroy]

I find named cells in combination with macros very elegant because you never need to change any code just because you rearranged your sheets. What you describe is more like an input form rather than spreadsheet cells. OpenOffice has input forms which are usually bound to database records. In spreadsheet context single form controls can be bound to single cells. Each form control supports a wide range of script events.

[jknepper]

Thanks for your help with my multiple-field trigger issue. Now, I have another issue - How do I "turn off" the listener? I have tried executing the "RmvListener" subroutine that was illustrated in the example provided earlier, but i gives me a "Basic runtime error - Object variable not set" error message.

Sub RmvListener
CellRng.removeModifyListener(oListener)
print "RmvListener"
End Sub

What lines of code do I add to make this work?

[Villeroy]

Declare it as

Code: Select all

Global oListener

on a separate module so you can still edit the other modules and keep the variable set.

[Villeroy]

Found an example document with a form control bound to a sheet cell and a script event: download/file.php?id=136
The text you type into the text box is written into the linked cell D2 and a formula in the criteria range concatenates this value to a filter criterion.
On every key stroke the macro performs the equivalent of menu:Data>Filter>Advanced ... read criteria from named range "criteria".

[ktrain]

Hello everyone,

The macro code supplied by FJCC is very close to what I'm looking for, but I need help with one more thing to add.

When the Sub CalledRoutine is invoked, I want it to display in a msgbox the "value" of the modified cell found by XmodifyListener. I believe it has something to do with "ThisComponent.currentSelection", but I can't quite figure it out. The modified cell will be a number or blank cell.

I've tried the following:
Sub CalledRoutine
Doc = ThisComponent
Sheet = Doc.Sheets.getByName("Inventory")
rem Cell = Sheet.getCellByPosition(2,0)
Cell = Doc.currentSelection
CurrentVal = Cell.Value

msgbox "You need to order " & CurrentVal & " more"
End Sub

It will only return "0" in the msgbox

Any help would be greatly appreciated, thanks.

[FJCC]

I put this part of your code in my routine

Code: Select all

Cell = Doc.currentSelection
CurrentVal = Cell.Value

msgbox "You need to order " & CurrentVal & " more"

and it works fine. Perhaps the value of the cell really is zero.
What happens if you use

Code: Select all

Cell = Doc.currentSelection
CurrentStr = Cell.String

msgbox "You need to order " & CurrentStr & " more"

[OObat]

hello, listener works fine, but when i try to close doc it drops error.

opens window and shows message in smaller separate windows
windows: My Macros & Dialogs.Standard-OpenOffice Basic
message windows name: OpenOffice 4.1.5
message: BASIC runtime error. Object variable not set
i press OK on message
another Message pop up
message windows name: OpenOffice 4.1.5 - Fatal Error
message: SEH Exception: ACCESS VIOLATION
i push only one button OK.

if i comment one line in RmvListener
Sub RmvListener
'CellRng.removeModifyListener(oListener)
End Sub
no error happens, but im afraid to make listener active.... can some one to try help me ?

[FJCC]

Are you sure you have

Code: Select all

Private CellRng as Object

at the top of your code as shown in my post of Apr 29, 2010?