Ask Your Question

Revision history [back]

@doug The event you referred to "Modified Status was Changed" is a document level event. It is connected to the "Document Modified Flag". It is false when the document and file are the same and false when the document is different to the file. If you open a spreadsheet the flag will be false. As soon as you make any change to the spreadsheet the flag will be set to true and the "Modified Status was Changed" event will fire. However any further changes will not have any effect as the flag is already set and the event "Modified Status was Changed" will not fire. If you Save the document the flag will be reset and the "Modified Status was Changed" will fire.

In order to have a Macro run whenever a change is made to the spreadsheet you need to use the Sheet events. These are a bit hidden away and can be seen by right clicking on the Sheet Tab (at the bottom next to the + to add a further sheet). This has "Sheet Events" where there is the event "Content Changed". They are also available under Edit > Sheet > Events. If a Macro is linked to this Event it will run every time a change is made to the sheet.

@Hippy Steve the Macro below attached to the Sheet Event "Content Changed" will show the Cell Column and Row of the Cell that was changed. You can use this in your Macro.

Sub SheetChange(oEvent)
MsgBox "Column is " & oEvent.CellAddress.Column
MsgBox "Row is " & oEvent.CellAddress.Row
End Sub

I have attached a demonstration file. When opened and a change is made to a Cell the Macro attached to the "Modified Status was Changed" will display a MessageBox with "DOCUMENT MODIFIED" and then MessageBoxes with the Column and Row of the Cell changed. Subsequent Cell changes will show only the Column and Row MessageBoxes. If you save the document the MessageBox "DOCUMENT MODIFIED" will show.

CalcMacroRun.ods

@doug The event you referred to "Modified Status was Changed" "Modified Status was Changed" is a document level event. It is connected to the "Document Modified Flag". It is false when the document and file are the same and false when the document is different to the file. If you open a spreadsheet the flag will be false. As soon as you make any change to the spreadsheet the flag will be set to true and the "Modified Status was Changed" "Modified Status was Changed" event will fire. However any further changes will not have any effect as the flag is already set and the event "Modified Status was Changed" "Modified Status was Changed" will not fire. If you Save the document the flag will be reset and the "Modified Status was Changed" "Modified Status was Changed" will fire.

In order to have a Macro run whenever a change is made to the spreadsheet you need to use the Sheet events. These are a bit hidden away and can be seen by right clicking on the Sheet Tab (at the bottom next to the + to add a further sheet). This has "Sheet Events" where there is the event "Content Changed". "Content Changed". They are also available under Edit > Sheet > Events. If a Macro is linked to this Event it will run every time a change is made to the sheet.

@Hippy Steve the Macro below attached to the Sheet Event "Content Changed" "Content Changed" will show the Cell Column and Row of the Cell that was changed. You can use this in your Macro.

Sub SheetChange(oEvent)
MsgBox "Column is " & oEvent.CellAddress.Column
MsgBox "Row is " & oEvent.CellAddress.Row
End Sub

I have attached a demonstration file. When opened and a change is made to a Cell the Macro attached to the "Modified Status was Changed" "Modified Status was Changed" will display a MessageBox with "DOCUMENT MODIFIED" and then MessageBoxes with the Column and Row of the Cell changed. Subsequent Cell changes will show only the Column and Row MessageBoxes. If you save the document the MessageBox "DOCUMENT MODIFIED" will show.

CalcMacroRun.ods