Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Die Excel/VBA-Beispiele

Thema

Wechselseitige Buchung in zwei Konten

Gruppe

Change

Problem

Wie kann ich auf zwei Konten - bspw. Kasse und Bank - wechselseitig buchen lassen?

Lösung
Geben Sie den Ereigniscode in das Klassenmodul des Arbeitsblattes ein.

ClassModule: Tabelle3

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   Dim intRow As Integer
   Application.EnableEvents = False
   On Error GoTo ERRORHANDLER
   If Target.Column = 2 Then
      With Worksheets(Target.Offset(0, -1).Value)
         intRow = .Cells(Rows.Count, 3).End(xlUp).Row + 1
         .Cells(intRow, 4) = Target
         .Cells(intRow, 3) = Target.Parent.Name
      End With
   ElseIf Target.Column = 4 Then
      With Worksheets(Target.Offset(0, -1))
         intRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
         .Cells(intRow, 2) = Target
         .Cells(intRow, 1) = Target.Parent.Name
      End With
   End If
ERRORHANDLER:
   Application.EnableEvents = True
End Sub

ClassModule: Tabelle1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   Dim iRow As Integer
   Application.EnableEvents = False
   On Error GoTo ERRORHANDLER
   If Target.Column = 2 Then
      With Worksheets(Target.Offset(0, -1).Value)
         iRow = .Cells(Rows.Count, 3).End(xlUp).Row + 1
         .Cells(iRow, 4) = Target
         .Cells(iRow, 3) = Target.Parent.Name
      End With
   ElseIf Target.Column = 4 Then
      With Worksheets(Target.Offset(0, -1).Value)
         iRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
         .Cells(iRow, 2) = Target
         .Cells(iRow, 1) = Target.Parent.Name
      End With
   End If
ERRORHANDLER:
   Application.EnableEvents = True
End Sub