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

Keyboard macros or custom scripts

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

Postby Newoldie » Thu Apr 29, 2010 8:15 pm

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
Last edited by Hagar Delest on Sun Apr 19, 2015 2:54 pm, edited 1 time in total.
Reason: tagged solved
openoffice 3 on windows xp pro
Newoldie
 
Posts: 2
Joined: Tue Mar 23, 2010 2:08 pm

Re: How do you make a macro rerun on data change in a calc s

Postby FJCC » Fri Apr 30, 2010 4:32 am

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   Expand viewCollapse view
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
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7016
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How do you make a macro rerun on data change in a calc s

Postby davea0511 » Thu Jun 02, 2011 7:13 pm

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.
OOo3.1.1, Win7
davea0511
 
Posts: 5
Joined: Fri Apr 08, 2011 10:30 pm

[Solved]How do you make a macro rerun on data change in a ca

Postby belchergb » Tue Feb 03, 2015 6:03 am

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.
LibreOffice 3.5.7.2 Ubuntu 12.04
belchergb
 
Posts: 36
Joined: Sun Mar 24, 2013 4:08 am

Re: How do you make a macro rerun on data change in a calc s

Postby FJCC » Tue Feb 03, 2015 6:15 am

I just tried the posted code with no modifications and it works for me in AOO 4.1.1
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7016
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How do you make a macro rerun on data change in a calc s

Postby GrimmSpector » Thu Apr 09, 2015 6:43 am

This is great, but where can I find what data is passed to oEv by the listener?
OpenOffice 4.1.1 on Windows 7 x64
GrimmSpector
 
Posts: 5
Joined: Wed Apr 08, 2015 6:20 am

Re: How do you make a macro rerun on data change in a calc s

Postby belchergb » Thu Apr 09, 2015 4:45 pm

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.
LibreOffice 3.5.7.2 Ubuntu 12.04
belchergb
 
Posts: 36
Joined: Sun Mar 24, 2013 4:08 am

Re: How do you make a macro rerun on data change in a calc s

Postby Villeroy » Thu Apr 09, 2015 7:00 pm

GrimmSpector wrote: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)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How do you make a macro rerun on data change in a calc s

Postby belchergb » Thu Apr 09, 2015 8:51 pm

I just learned about SheetEvents. Thank you Villeroy. This is even better!
LibreOffice 3.5.7.2 Ubuntu 12.04
belchergb
 
Posts: 36
Joined: Sun Mar 24, 2013 4:08 am

Re: How do you make a macro rerun on data change in a calc s

Postby Villeroy » Thu Apr 09, 2015 10:44 pm

belchergb wrote: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   Expand viewCollapse view
Sub myListenerName_modified(event)
GlobalScopeBasicLibraries.loadLibrary("MRILib")
MRILib.Module1.Mri event.Source
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Make a macro rerun on data change in a Calc sheet

Postby jknepper » Tue May 12, 2015 12:22 am

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”.
Open Office 4.0.1
on both Windows 7 and Mac OS X 10.6
jknepper
 
Posts: 2
Joined: Sun Dec 02, 2012 5:38 pm

Re: [Solved] Make a macro rerun on data change in a Calc she

Postby Villeroy » Tue May 12, 2015 12:03 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Make a macro rerun on data change in a Calc she

Postby jknepper » Tue May 12, 2015 4:33 pm

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?
Open Office 4.0.1
on both Windows 7 and Mac OS X 10.6
jknepper
 
Posts: 2
Joined: Sun Dec 02, 2012 5:38 pm

Re: [Solved] Make a macro rerun on data change in a Calc she

Postby Villeroy » Tue May 12, 2015 7:49 pm

Declare it as
Code: Select all   Expand viewCollapse view
Global oListener

on a separate module so you can still edit the other modules and keep the variable set.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Make a macro rerun on data change in a Calc she

Postby Villeroy » Tue May 12, 2015 9:51 pm

Found an example document with a form control bound to a sheet cell and a script event: https://forum.openoffice.org/en/forum/d ... 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".
Last edited by Villeroy on Sat Jun 04, 2016 9:44 am, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Make a macro rerun on data change in a Calc she

Postby ktrain » Sat Jun 04, 2016 7:03 am

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.
openoffice 4.1.1 on Windows 7
ktrain
 
Posts: 3
Joined: Fri Nov 06, 2015 2:21 am

Re: [Solved] Make a macro rerun on data change in a Calc she

Postby FJCC » Sat Jun 04, 2016 3:42 pm

I put this part of your code in my routine
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
Cell = Doc.currentSelection
CurrentStr = Cell.String

msgbox "You need to order " & CurrentStr & " more"
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7016
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Make a macro rerun on data change in a Calc she

Postby OObat » Wed Aug 15, 2018 10:36 am

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 ?
OpenOffice 4.1.5 on Windows 8.1
OObat
 
Posts: 1
Joined: Wed Aug 15, 2018 10:04 am

Re: [Solved] Make a macro rerun on data change in a Calc she

Postby FJCC » Wed Aug 15, 2018 10:41 pm

Are you sure you have
Code: Select all   Expand viewCollapse view
Private CellRng as Object

at the top of your code as shown in my post of Apr 29, 2010?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7016
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests