Which Event Macro on spreadsheet contents change?

[HippySteve]

I have an excel sheet which tests a value in one column before it allows data to be entered in another (it is an accounting sheet which won’t allow an amount to be entered unless there is a category set). I’m an experienced vba programmer but a newbie with LibreOffice macros. What is the easiest way to do this in Calc? I’m struggling to even find the correct event!!

vba code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim a&
On Error GoTo errh

With Target
If .Column = 14 And Target.Value2 <> "" Then
   If Cells(.Row, 12) = "" Then
     a = MsgBox("Please select a Business Stream", , "Input Error")
     Target.Value = ""
     Cells(.Row, 12).Select
   End If
End If
If .Column = 6 And Target.Value2 <> "" Then
   If Cells(.Row, 3) = "" Then
     a = MsgBox("Please select a Business Stream", , "Input Error")
     Target.Value = ""
     Cells(.Row, 3).Select
   End If
End If
errh:
End With

End Sub

[peterwt]

@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.

@HippySteve 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]

excellent answer here so I removed my partial response.

[Trismegistos]

I can not find any documentation for SheetChange. In particular I don’t know what is type of oEvent parameter and what methods/fileds this type have. Could you point me to any resource?