Ask Your Question
1

Which Event Macro on spreadsheet contents change?

asked 2016-12-30 13:30:03 +0200

Hippy Steve gravatar image

updated 2016-12-30 17:04:32 +0200

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
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2016-12-31 16:37:51 +0200

peterwt gravatar image

updated 2016-12-31 16:43:08 +0200

@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

edit flag offensive delete link more

Comments

excellent answer here so I removed my partial response.

doug gravatar imagedoug ( 2016-12-31 21:34:52 +0200 )edit

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?

Trismegistos gravatar imageTrismegistos ( 2018-07-16 14:17:08 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-12-30 13:30:03 +0200

Seen: 1,301 times

Last updated: Dec 31 '16