Macros: How to enable and disable onChange events?

[matthewfenn]

I confess I am converting a macro heavy Excel spreadsheet into Calc. I’m absolutely fine with the fact that I need to convert stuff, and a lot of initial hurdles have already been overcome… however, I have one hurdle I’m really struggling with.

In Excel, I use Worksheet_Change subroutines to detect when a cell changes on a particular sheet so action can be taken and I use Application.EnableEvents = True / False to enable or disable the triggering of that change macro.

I’ve learnt that in Calc, this becomes Sub OnSheetContentChange.

What I’m not sure about is how to enable/disable the triggering of OnSheetContentChange… I want to use the OnSheetContentChange macro to insert new lines when someone enters data into certain cells… But I don’t want the OnSheetContentChange macro to recursively call itself.

Ideas anyone?

Matthew

[Lupp]

As you surely already found, the ‘Content Changed’ event must get assigned one specific Sub which then may be used as a kind of splitter. The event passes one parameter to the Sub describing the set of cells that were changed by a single action. This parameter can either be a SheetCellRange object describing a single rectangular cell range (single cell included) or a set of such ranges (SheetCellRanges) accessible one by one using an index.

Depending on which characteristic service is supported by the event parameter your Sub may take different actions, typically by calling the appropriate secondary Sub performing the eventual process.

To avoid an unwanted (endless?) recursion you need to evaluate some property accessible via the event parameter.

Let’s assume you are talking of an action to be taken only if a single cell was changed, and this only if that cell had assigned the cell style csTriggerAction. The event parameter may be pEvent.

If NOT pEvent.SupportsService("com.sun.star.sheet.SheetCell") Then Exit Sub
If NOT pEvent.CellStyle="csTriggerAction" Then Exit Sub  

will break the loop then, at least in your case.

Edit 1:

As the OriginalQuestioner confirmed my view of the question, I attach this demonstration concerning the handling of the different types a ‘Content changed’ event can have.

[jimk]

+1 It sounds like you were able to guess what the OQ is trying to do.

[Lupp]

Hmm, I recently developed (just for fun - and for answering questions here) some demonstrations based on ‘Content changed’ events.

[jimk]

It shouldn’t recursively call itself unless your code is doing something that causes it. For example, right-click on the sheet tab and assign the following macro to the Content changed event.

Sub SheetChange(oEvent)
	oSheet = oEvent.Spreadsheet
	oSheet.Rows.insertByIndex(3,1)
End Sub

This will insert a row whenever the spreadsheet is changed, but will not recursively call itself.

[Lupp]

I assumed a newly inserted row will often have to take some formulae (+…) by a copy process. This would trigger the ‘Content Changed’ event anew.

[matthewfenn]

Hi guys,

Many thanks for taking the time to respond.

The sheet has several sections, starting initially with each section containing two lines.
Column B is the “unique index”, and the rest of the line has various vlookup formulas to pull data from another sheet depending on the contents of the Index cell for that row.

The user can only enter data into certain cells in column B.

My current iteration of my “change macro” code is triggered by the Content Changed event as set up by right clicking on the tab and going to Sheet Events.

Its purpose is to a) check if the user has entered a value that is already in use elsewhere, and if so, to delete it from the other place, and b) check to see if they have filled up the section they are in, and if so, to add in a new row (and copy the vlookups etc into it).

Thinking about it, I suspect I have just thought of a work-around… If I create a global Boolean variable (e.g. “ChangeEventRunning”) set up that I can check for in the change macro. At the beginning of the macro I check to see if it is true - if so exit the sub. If it isn’t true, then set it to true and carry on through the macro till I get to the end, at which point set it back to False.

All I need to do now is research how to set up global variables!

• Mattthew

EDIT: Thanks Lupp! Much appreciated!

[Lupp]

Global variables are declared for a module above any Sub / Function using the keyword Global instead of Dim. However, development of code in the “IDE” using global variables may be aggravated a bit by the fact that any tiny change causing a re-“compilation” empties the GV.
See also: https://help.libreoffice.org/Basic/Using_Procedures_and_Functions#Scope_of_Variables