Updating Spreadsheet after Cell change

[harddrive747]

I have got to work in Microsoft Excel some code that when a certain range of cells are updated that certain cells get updated. I would like to do that in Calc. I will include the code that works in Excel here

Const WS_RANGE As String = "F18:F450"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, -5).Value = Format(Date, "dd-mmm-yyyy")
End If
End With

End If

I also know that VB script will not run in Calc, but I would like to be able to update the sheet with date information.

Thanks for helping me out on this.
Terry

[probe1]

In OOo you would use a so-called "listener" to react on sheet changes.
The following example was found in the net, some remarks:
* code has to be attached to document (where needed) or to STANDARD library of "My Macros" section
* used variables are defined GLOBAL, you cannot use them in your code elsewhere
* to activate run macros SetListeners, maybe attach it to document open event

* De-activate listener with macro RemoveListener
Removing is immanent not to influence OOo's stability

* On my testing with (2.3.0) I found out that fast calls to the macro sometimes messes the cell coordination.

Can you work it from this example?

Code: Select all

    ' http://www.oooforum.org/forum/viewtopic.phtml?t=27026
    ' ms777 Posted: Sat Nov 19, 2005 3:30 pm
    ' angepasst 2007-10-17
    ' http://de.openoffice.info/viewtopic.php?f=2&t=14347

    global lastCol as long
    global lastRow as Long
    global actCol as long
    global actRow as Long

    Global oRange
    Global oContr
    Global oDatChangeListener
    Global oSelChangeListener


    Sub RemoveListener
    oRange.removeChartDataChangeEventListener(oDatChangeListener)
    oContr.removeSelectionChangeListener(oSelChangeListener)
    End Sub

    Sub SetListeners()
    oSelChangeListener = createUnoListener("O1_","com.sun.star.view.XSelectionChangeListener")
    oDatChangeListener = createUnoListener("O2_","com.sun.star.chart.XChartDataChangeEventListener")
    ' Tabellen-Index
    ' oSheet = ThisComponent.sheets.getByIndex(0)
    oSheet = ThisComponent.Sheets.getByName( "Tabelle1" )
    oContr = ThisComponent.CurrentController

    ' zu beobachtender Bereich
    ' Angabe der Zellen
    ' Null-basiert: A1=0,0  B1=1,0   C4=3,3
    ' Links, Oben, Rechts, Unten
    ' hier: gesamte Spalte A, OOo Version 2
    oRange = oSheet.getCellRangeByPosition(0,0,0,65535)

    lastRow = 0
    lastCol = 0

    oContr.addSelectionChangeListener(oSelChangeListener)
    oRange.addChartDataChangeEventListener(oDatChangeListener)

    ' ausgelagert !
    ' oRange.removeChartDataChangeEventListener(oDatChangeListener)
    ' oContr.removeSelectionChangeListener(oSelChangeListener)

    End Sub

    Sub O1_selectionChanged(aEvent as com.sun.star.lang.EventObject)
    'xray.xray aEvent
    oSel = aEvent.Source.Selection
    if HasUnoInterfaces(oSel, "com.sun.star.sheet.XCellAddressable") then
      oCA = oSel.CellAddress
      actCol = lastCol
      actRow = lastRow
      lastCol = oCA.Column
      lastRow = oCA.Row
      endif
    end sub


    Sub O2_chartDataChanged(a as com.sun.star.chart.ChartDataChangeEvent)
    ' Spaltenangabe: B
    oDateCell=ThisComponent.CurrentController.ActiveSheet.getCellByPosition(1, actRow)
    oDateCell.SetString(Now)
    End Sub