Converting Excel Macro to Libre Office Calc - Run Macro on Cell Value Change - If, Else, Nothing

[RyanGrist]

Hello,

Apologies if this is not posted correctly, I am new to the sub and would appreciate any help.

I am attempting to accomplish the following with macros in Libre Office Calc:

If cell A1 on sheet1 is modified at all to anything else, then
if cell E1 on sheet2 doesn’t equal anything then
make cell C2 on sheet2 equal cell A1 on sheet1 Else
do nothing

The following is a working Macro in excel that accomplishes the above, I am just having difficulty rewriting this macro in Libre Office:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = “$A$1” Then

If Sheet2.Range(“E1”).Value2 = 0 Then
ThisWorkbook.Worksheets(“Sheet2”).Range(“C1”).Value2 = ThisWorkbook.Worksheets(“Sheet1”).Range(“A1”).Value2
Else
End If

End If
End Sub

If anyone can provide how to accomplish this in Libre Office Calc that would be greatly appreciated.

[Zizi64]

The LibreOffice API is basicly different than the MS VBA. Please study Andrew Pitonyak’s free macro books.
https://www.pitonyak.org/oo.php

[RyanGrist]

Edit: I was able to solve it myself using listeners and some examples from the community. Still, if anyone knows a better way I would be happy to hear it, but for now I will mark this thread as solved. The following is what I am using:

REM ***** BASIC *****

Global oListener as Object
Global CellRng as Object

Sub AddListener
Dim Doc, Sheet, Cell as Object

Doc = ThisComponent
Sheet = Doc.Sheets.getByIndex(0) 'get leftmost sheet

CellRng = Sheet.getCellrangeByName(“A1”)

oListener = createUnoListener(“Modify_”,“com.sun.star.util.XModifyListener”) 'create a listener
Cellrng.addModifyListener(oListener) 'register the listener

End Sub

Sub Modify_modified(oEv) 'macro jumps here when oListener detects modification of Sheet
CalledRoutine
End Sub

Sub Modify_disposing(oEv)
End Sub

Sub RmvListener
CellRng.removeModifyListener(oListener)
End Sub

Sub CalledRoutine
Doc = ThisComponent
If Doc.Sheets.getByIndex(1).getcellrangebyname(“E1”).Value = 0 Then
Doc.Sheets.getByIndex(1).getcellrangebyname(“C1”).Value = Doc.Sheets.getByIndex(0).getcellrangebyname(“A1”).Value
Else
End If

End Sub