Apache OpenOffice Community Forum

Code: Select all

Private Sub Workbook_Open()
    StartAppEvents
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "App_WorksheetChange"
  
    ' Disable events to prevent infinite loop
    Application.EnableEvents = False
    
    On Error GoTo ErrorHandler
    Dim i As Long, initialRow As Long
    initialRow = Target.Row

    For i = 1 To initialRow - 1
        If Sh.Cells(i, Target.Column) = Sh.Cells(initialRow, Target.Column) Then
            Target.ClearContents
            Sh.Cells(initialRow, Target.Column).Select
            Exit For
        Else
            Sh.Cells(initialRow, Target.Column) = Target.Value
            Sh.Cells(initialRow + 1, Target.Column).Select
        End If
    Next i
    
IgnoreInput:
    ' Re-enable events
    Application.EnableEvents = True
    Exit Sub
ErrorHandler:
     If Err.Number <> 0 Then
        Msg = "Error # " & Str(Err.Number) & " was generated by " _
         & Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & Err.Description
        MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
     End If
     Resume IgnoreInput
End Sub

Code: Select all

Option Explicit

' Declare a global variable to hold the listener
Private sheetChangeListener As Object

' This function initializes the sheet change listener
Sub InitializeSheetChangeListener()

  ' Check if the listener is already initialized
  If Not IsNull(sheetChangeListener) Then Exit Sub ' prevent multiple listeners

  ' Create an instance of the listener class
  Set sheetChangeListener = CreateObject("com.sun.star.sheet.SheetChangeListener")

  ' Get the current document
  Dim document As Object
  document = ThisComponent

  ' Get the active sheet
  Dim sheet As Object
  sheet = document.CurrentController.ActiveSheet

  ' Add the listener to the sheet
  sheet.addSheetChangeListener(sheetChangeListener)
End Sub

' Implement the SheetChangeListener interface's "modified" method
Sub sheetChangeListener_modified(event As Object)

  ' Get the affected sheet
  Dim affectedSheet As Object
  affectedSheet = event.Sheet

  '  Unfortunately, there's no direct equivalent to VBA's "Target" range in OpenOffice.
  '  The following demonstrates how to get *all* changed cells since the last event.
  '  This is NOT the same as Target, as it doesn't give the cells directly involved in the current change.
  Dim changedCells As Object
  changedCells = affectedSheet.getChangedCells()

  ' Example: Print the address of each changed cell to the console.
  Dim cell As Object
  For Each cell In changedCells
    Print cell.getCellAddress().AbsoluteName
  Next cell

  ' Example: Check if cell A1 was among the changed cells
  Dim a1 As Object
  a1 = affectedSheet.getCellByPosition(0, 0) ' Column A, Row 1 (zero-based)

  If NOT IsNull(changedCells) Then
     Dim cellRange As Object
     For Each cellRange in changedCells
      If cellRange.getCellAddress().AbsoluteName = a1.getCellAddress().AbsoluteName Then 
        msgbox "Cell A1 was changed!"
        Exit For ' Cell A1 found among changes
      End If
     next cellRange
   End If

  ' IMPORTANT FOR PERFORMANCE: Clear the changed cells after processing
  '  Otherwise, they accumulate and checking change gets slow!
  affectedSheet.clearChangedCells()

End Sub

' This Sub can be called from "Tools -> Macros -> Organize Macros -> OpenOffice Basic"
' to initialize the listener.  It needs to be run only once per document session.
Sub StartListeningForSheetChanges()
  InitializeSheetChangeListener
End Sub