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
Updating Spreadsheet after Cell change
Re: Updating Spreadsheet after Cell change
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?
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