Ask Your Question

Run macro on cell edit

asked 2016-09-16 09:42:00 +0200

JakeM gravatar image

Is it possible to make a macro/macro function execute when a cells text changes? Ie, make a macro run a function when an event occurs.

Or can macros only be run manually by clicking Tools->Macros->Run Macros?

I'm attempting to detect a cell text change/edit: if the text == "Resolved": change the text colour to green.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-09-16 10:50:28 +0200

Lupp gravatar image

updated 2016-09-16 12:33:19 +0200

There is a standard event 'Content changed' for each sheet of any Calc document. You can assign a user routine (Sub) to it opening the dialogue via the context menu of the respective sheet's tab. This specific event is passing one parameter which contains a SheetCellRange object (may describe a single cell) or a SheetCellRanges object for the range(s) of cells afflicted by the action of changing cell contents.
Your 'Sub' has first to analyse the sender object to decide if / what action is to take. Then it can perform this action itself or delegate the task to a different routine.
You may also create and register UNOlisteners of your own. This is significantly more complicated.

(I do not know for sure the versions doing it this way by tab context menu. At least V5.0 and higher. Older versions may offer different means.) All the vesrions of LibO, including the "startup" V3.3 do it the mentioned way. AOO also does. (Just checked.)
Make sure to understand that this actually is about the contents (may also be formulae), not about formula results.

(General advice: Avoid relying on macros whereever possible.)
To set a background colour depending on current contents just for emphasis you should use conditional formatting. Only if you want to also cause a persistent change of the cell's appearence it may be advisable to actually apply a different named CellStyle by means of user code.

edit flag offensive delete link more

answered 2016-09-16 12:06:38 +0200

pierre-yves samyn gravatar image


@Lupp answered exactly to your question. However if your need is changing the text color you can do this with a simple conditional formatting, no macro is needed. See A1 in Resolved.ods attached


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-16 09:42:00 +0200

Seen: 1,453 times

Last updated: Sep 16 '16