Daten ändern über CoboBox-Auswahl
16.11.2019 10:25:08
Andreas
ich komme mal wieder nicht weiter. Aus anderen Projekten (vielen Dank an dieses Forum) habe ich mir nachfolgenden Code zusammengebastelt. Leider verstehe ich zu wenig davon um die scheinbar notwendigen Anpassungen vorzunehmen.
Was ich erreichen möchte:
1. über die "ComboBox_RechnAusw" wähle ich eine Zeile aus dem Blatt "EinAusgangsRech", dabei werden bestimmte Werte aus den Spalten in die Textboxen auf dem UserForm geschrieben
2. nach Änderung des Inhalts der Textboxen sollen die Werte wieder in die Zeile geschrieben, bzw. überschrieben werden
Das einlesen in die "ComboBox_RechnAusw" funktioniert schon.
Beim zurückschreiben über den "CommandButton_AendSpeich" wird allerdings eine andere Zeile gelöscht und die geändereten Daten in wieder eine andere Zeile geschrieben, wobei diese überschrieben wird.
Zum besseren Verständnis habe ich mal eine abgespeckte Mappe hochgeladen.
https://www.herber.de/bbs/user/133248.xlsm
Ich hoffe mir kann Jemand bei diesem Problem helfen und bedanke mich schon mal fürs lesen und die Mühe.
Option Explicit
Private wks As Worksheet
Private bolAktion As Boolean 'Merker, dass eine Aktion bereits ausgeführt wird
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Worksheets("EinAusgangsRech").Visible = True
Worksheets("EinAusgangsRech").Activate
Worksheets("EinAusgangsRech").Unprotect
Application.DisplayAlerts = False
With Me.ComboBoxZahlung
.RowSource = "Zahlweise"
.ListIndex = -1
End With
CommandButton_AendSpeich.Visible = False
End Sub
Private Sub UserForm_Activate()
Dim wks As Worksheet
Dim a As Long
Set wks = Worksheets("EinAusgangsRech")
Dim Zelle As Range
Dim Bereich As Range
Set Bereich = ActiveSheet.Range("A8:A200")
If bolAktion = True Then Exit Sub
bolAktion = True
ComboBox_RechnAusw.Clear
With ComboBox_RechnAusw
.ColumnCount = 3
.ColumnWidths = ";0;0 cm; 4cm;4cm"
For a = 8 To 200
If wks.Rows(a).RowHeight > 0 Then
.AddItem
.List(.ListCount - 1, 0) = wks.Cells(a, 1) & " - " & wks.Cells(a, 3) & " - " & wks. _
_
Cells(a, 4)
.List(.ListCount - 1, 1) = a
End If
Next a
End With
bolAktion = False
With BuchungVorbAend ' UserForm
.Height = Application.Height / 1 ' Userform im Vollbildmodus
.Width = Application.Width / 1 - 5
.Label_A.Left = (.Width - .Label_A.Width) / 2 ' Rahmen mittig
.Label_A.Top = (.Height - .Label_A.Height) / 2 - 10 ' Rahmen mittig
.Label_1.Left = (.Width - .Label_1.Width) / 2 ' Überschrift mittig
.Label_1.Top = .Label_A.Top - 30
.CommandButton_AendSpeich.Left = .Label_A.Left + 437
.CommandButton_AendSpeich.Top = .Label_A.Top + 84
.CommandButton4.Left = .Label_A.Left + 568
.CommandButton4.Top = .Label_A.Top + 106
.Label18.Left = .Label_A.Left + 12
.Label18.Top = .Label_A.Top + 14
.ComboBox_RechnAusw.Left = (.Width - .ComboBox_RechnAusw.Width) / 2
.ComboBox_RechnAusw.Top = .Label_A.Top + 34
.Label16.Left = .Label_A.Left + 12
.Label16.Top = .Label_A.Top + 144
.TextBox_Fälligkeit.Left = .Label_A.Left + 12
.TextBox_Fälligkeit.Top = .Label_A.Top + 164
.Label12.Left = .Label_A.Left + 140
.Label12.Top = .Label_A.Top + 166
.Label17.Left = .Label_A.Left + 200
.Label17.Top = .Label_A.Top + 144
.TextBox_BetragEinlesen.Left = .Label_A.Left + 200
.TextBox_BetragEinlesen.Top = .Label_A.Top + 164
.LabelZahlung.Left = .Label_A.Left + 340
.LabelZahlung.Top = .Label_A.Top + 144
.ComboBoxZahlung.Left = .Label_A.Left + 340
.ComboBoxZahlung.Top = .Label_A.Top + 164
.Label20.Left = .Label_A.Left + 384
.Label20.Top = .Label_A.Top + 166
.Label15.Left = .Label_A.Left + 294
.Label15.Top = .Label_A.Top + 166
.Label1.Left = .Label_A.Left + 12
.Label1.Top = .Label_A.Top + 194
.TextBoxDaten.Left = .Label_A.Left + 12
.TextBoxDaten.Top = .Label_A.Top + 214
.Label14.Left = .Label_A.Left + 268
.Label14.Top = .Label_A.Top + 216
.Label2.Left = .Label_A.Left + 12
.Label2.Top = .Label_A.Top + 244
.TextBoxVorgang.Left = .Label_A.Left + 12
.TextBoxVorgang.Top = .Label_A.Top + 264
.Label13.Left = .Label_A.Left + 268
.Label13.Top = .Label_A.Top + 266
.Label19.Left = .Label_A.Left + 370
.Label19.Top = .Label_A.Top + 244
.TextBoxBemerk.Left = .Label_A.Left + 370
.TextBoxBemerk.Top = .Label_A.Top + 264
.Label7.Left = .Label_A.Left + 12
.Label7.Top = .Label_A.Top + 294
.TextBox50.Left = .Label_A.Left + 12
.TextBox50.Top = .Label_A.Top + 314
.TextBox1.Left = .Label_A.Left + 300
.TextBox1.Top = .Label_A.Top + 314
.Label8.Left = .Label_A.Left + 370
.Label8.Top = .Label_A.Top + 294
.TextBox60.Left = .Label_A.Left + 370
.TextBox60.Top = .Label_A.Top + 314
.TextBox10.Left = .Label_A.Left + 658
.TextBox10.Top = .Label_A.Top + 314
.TextBox51.Left = .Label_A.Left + 12
.TextBox51.Top = .Label_A.Top + 338
.TextBox2.Left = .Label_A.Left + 300
.TextBox2.Top = .Label_A.Top + 338
.TextBox61.Left = .Label_A.Left + 370
.TextBox61.Top = .Label_A.Top + 338
.TextBox11.Left = .Label_A.Left + 658
.TextBox11.Top = .Label_A.Top + 338
.TextBox52.Left = .Label_A.Left + 12
.TextBox52.Top = .Label_A.Top + 362
.TextBox3.Left = .Label_A.Left + 300
.TextBox3.Top = .Label_A.Top + 362
.TextBox62.Left = .Label_A.Left + 370
.TextBox62.Top = .Label_A.Top + 362
.TextBox12.Left = .Label_A.Left + 658
.TextBox12.Top = .Label_A.Top + 362
.TextBox53.Left = .Label_A.Left + 12
.TextBox53.Top = .Label_A.Top + 386
.TextBox4.Left = .Label_A.Left + 300
.TextBox4.Top = .Label_A.Top + 386
.TextBox63.Left = .Label_A.Left + 370
.TextBox63.Top = .Label_A.Top + 386
.TextBox13.Left = .Label_A.Left + 658
.TextBox13.Top = .Label_A.Top + 386
.TextBox54.Left = .Label_A.Left + 12
.TextBox54.Top = .Label_A.Top + 410
.TextBox5.Left = .Label_A.Left + 300
.TextBox5.Top = .Label_A.Top + 410
.TextBox64.Left = .Label_A.Left + 370
.TextBox64.Top = .Label_A.Top + 410
.TextBox14.Left = .Label_A.Left + 658
.TextBox14.Top = .Label_A.Top + 410
.TextBox55.Left = .Label_A.Left + 12
.TextBox55.Top = .Label_A.Top + 434
.TextBox6.Left = .Label_A.Left + 300
.TextBox6.Top = .Label_A.Top + 434
.TextBox65.Left = .Label_A.Left + 370
.TextBox65.Top = .Label_A.Top + 434
.TextBox15.Left = .Label_A.Left + 658
.TextBox15.Top = .Label_A.Top + 434
.TextBox56.Left = .Label_A.Left + 12
.TextBox56.Top = .Label_A.Top + 434
.TextBox7.Left = .Label_A.Left + 300
.TextBox7.Top = .Label_A.Top + 434
.TextBox66.Left = .Label_A.Left + 370
.TextBox66.Top = .Label_A.Top + 434
.TextBox16.Left = .Label_A.Left + 658
.TextBox16.Top = .Label_A.Top + 434
.TextBox57.Left = .Label_A.Left + 12
.TextBox57.Top = .Label_A.Top + 458
.TextBox8.Left = .Label_A.Left + 300
.TextBox8.Top = .Label_A.Top + 458
.TextBox67.Left = .Label_A.Left + 370
.TextBox67.Top = .Label_A.Top + 458
.TextBox17.Left = .Label_A.Left + 658
.TextBox17.Top = .Label_A.Top + 458
.TextBox58.Left = .Label_A.Left + 12
.TextBox58.Top = .Label_A.Top + 482
.TextBox9.Left = .Label_A.Left + 300
.TextBox9.Top = .Label_A.Top + 482
.TextBox68.Left = .Label_A.Left + 370
.TextBox68.Top = .Label_A.Top + 482
.TextBox18.Left = .Label_A.Left + 658
.TextBox18.Top = .Label_A.Top + 482
.TextBox69.Left = .Label_A.Left + 370
.TextBox69.Top = .Label_A.Top + 506
.TextBox19.Left = .Label_A.Left + 658
.TextBox19.Top = .Label_A.Top + 506
.TextBox70.Left = .Label_A.Left + 370
.TextBox70.Top = .Label_A.Top + 530
.TextBox20.Left = .Label_A.Left + 658
.TextBox20.Top = .Label_A.Top + 530
.TextBox71.Left = .Label_A.Left + 370
.TextBox71.Top = .Label_A.Top + 554
.TextBox21.Left = .Label_A.Left + 658
.TextBox21.Top = .Label_A.Top + 554
.TextBox72.Left = .Label_A.Left + 370
.TextBox72.Top = .Label_A.Top + 578
.TextBox22.Left = .Label_A.Left + 658
.TextBox22.Top = .Label_A.Top + 578
.TextBox73.Left = .Label_A.Left + 370
.TextBox73.Top = .Label_A.Top + 602
.TextBox23.Left = .Label_A.Left + 658
.TextBox23.Top = .Label_A.Top + 602
.TextBox74.Left = .Label_A.Left + 370
.TextBox74.Top = .Label_A.Top + 626
.TextBox24.Left = .Label_A.Left + 658
.TextBox24.Top = .Label_A.Top + 626
.TextBox75.Left = .Label_A.Left + 370
.TextBox75.Top = .Label_A.Top + 650
.TextBox25.Left = .Label_A.Left + 658
.TextBox25.Top = .Label_A.Top + 650
.TextBox76.Left = .Label_A.Left + 370
.TextBox76.Top = .Label_A.Top + 674
.TextBox26.Left = .Label_A.Left + 658
.TextBox26.Top = .Label_A.Top + 674
.TextBox77.Left = .Label_A.Left + 370
.TextBox77.Top = .Label_A.Top + 698
.TextBox27.Left = .Label_A.Left + 658
.TextBox27.Top = .Label_A.Top + 698
.TextBox78.Left = .Label_A.Left + 370
.TextBox78.Top = .Label_A.Top + 722
.TextBox28.Left = .Label_A.Left + 658
.TextBox28.Top = .Label_A.Top + 722
.TextBox79.Left = .Label_A.Left + 370
.TextBox79.Top = .Label_A.Top + 722
.TextBox29.Left = .Label_A.Left + 658
.TextBox29.Top = .Label_A.Top + 722
.TextBox80.Left = .Label_A.Left + 370
.TextBox80.Top = .Label_A.Top + 746
.TextBox30.Left = .Label_A.Left + 658
.TextBox30.Top = .Label_A.Top + 746
.TextBox81.Left = .Label_A.Left + 370
.TextBox81.Top = .Label_A.Top + 768
.TextBox31.Left = .Label_A.Left + 658
.TextBox31.Top = .Label_A.Top + 768
.TextBox82.Left = .Label_A.Left + 370
.TextBox82.Top = .Label_A.Top + 792
.TextBox32.Left = .Label_A.Left + 658
.TextBox32.Top = .Label_A.Top + 792
End With
Me.TextBox_BetragEinlesen.Text = "0.00"
Me.TextBox_Fälligkeit.Text = ""
Me.TextBoxDaten.Text = ""
Me.TextBoxVorgang.Text = ""
Me.TextBox1.Text = "0,00"
Me.TextBox2.Text = "0,00"
Me.TextBox3.Text = "0,00"
Me.TextBox4.Text = "0,00"
Me.TextBox5.Text = "0,00"
Me.TextBox6.Text = "0,00"
Me.TextBox7.Text = "0,00"
Me.TextBox8.Text = "0,00"
Me.TextBox9.Text = "0,00"
Me.TextBox10.Text = "0,00"
Me.TextBox11.Text = "0,00"
Me.TextBox12.Text = "0,00"
Me.TextBox13.Text = "0,00"
Me.TextBox14.Text = "0,00"
Me.TextBox15.Text = "0,00"
Me.TextBox16.Text = "0,00"
Me.TextBox17.Text = "0,00"
Me.TextBox18.Text = "0,00"
Me.TextBox19.Text = "0,00"
Me.TextBox20.Text = "0,00"
Me.TextBox21.Text = "0,00"
Me.TextBox22.Text = "0,00"
Me.TextBox23.Text = "0,00"
Me.TextBox24.Text = "0,00"
Me.TextBox25.Text = "0,00"
Me.TextBox26.Text = "0,00"
Me.TextBox27.Text = "0,00"
Me.TextBox28.Text = "0,00"
Me.TextBox29.Text = "0,00"
Me.TextBox30.Text = "0,00"
Me.TextBox31.Text = "0,00"
Me.TextBox32.Text = "0,00"
Me.TextBoxBemerk.Text = ""
'alle Zeilen einblenden
Bereich.EntireRow.Hidden = True
'jede Zeile prüfen
For Each Zelle In Bereich
'wenn Zelleninhalt null, dann diese Zeile ausblenden
If Zelle "" Then
'Call FilternBereich
Zelle.EntireRow.Hidden = False
End If
Next
End Sub
Private Sub ComboBox_RechnAusw_Change()
Dim a As Long
If ComboBox_RechnAusw.ListIndex > -1 Then
a = ComboBox_RechnAusw.List(ComboBox_RechnAusw.ListIndex, 1)
With ActiveSheet
Me.TextBoxDaten.Text = ""
Me.TextBoxVorgang.Text = ""
TextBox_Fälligkeit.Text = Cells(a, 1).Text
TextBoxDaten.Text = Cells(a, 3).Text
TextBoxVorgang.Text = Cells(a, 4).Text
TextBoxBemerk.Text = Cells(a, 42).Text
If Cells(a, 6) "" Then
TextBox_BetragEinlesen = Format(Cells(a, 6), "#.##0,00")
TextBox_BetragEinlesen.Text = Format(TextBox_BetragEinlesen.Text, "#,##0.00")
Else
TextBox_BetragEinlesen = Format("0,00")
End If
If Cells(a, 7) "" Then
TextBox1 = Format(Cells(a, 7), "#.##0,00")
TextBox1.Text = Format(TextBox1.Text, "#,##0.00")
Else
TextBox1 = Format("0,00")
End If
If Cells(a, 8) "" Then
TextBox2 = Format(Cells(a, 8), "#.##0,00")
TextBox2.Text = Format(TextBox2.Text, "#,##0.00")
Else
TextBox2 = Format("0,00")
End If
If Cells(a, 9) "" Then
TextBox3 = Format(Cells(a, 9), "#.##0,00")
TextBox3.Text = Format(TextBox3.Text, "#,##0.00")
Else
TextBox3 = Format("0,00")
End If
If Cells(a, 10) "" Then
TextBox4 = Format(Cells(a, 10), "#.##0,00")
TextBox4.Text = Format(TextBox4.Text, "#,##0.00")
Else
TextBox4 = Format("0,00")
End If
If Cells(a, 11) "" Then
TextBox5 = Format(Cells(a, 11), "#.##0,00")
TextBox5.Text = Format(TextBox5.Text, "#,##0.00")
Else
TextBox5 = Format("0,00")
End If
If Cells(a, 12) "" Then
TextBox6 = Format(Cells(a, 12), "#.##0,00")
TextBox6.Text = Format(TextBox6.Text, "#,##0.00")
Else
TextBox6 = Format("0,00")
End If
If Cells(a, 13) "" Then
TextBox7 = Format(Cells(a, 13), "#.##0,00")
TextBox7.Text = Format(TextBox7.Text, "#,##0.00")
Else
TextBox7 = Format("0,00")
End If
If Cells(a, 14) "" Then
TextBox8 = Format(Cells(a, 14), "#.##0,00")
TextBox8.Text = Format(TextBox8.Text, "#,##0.00")
Else
TextBox8 = Format("0,00")
End If
If Cells(a, 15) "" Then
TextBox9 = Format(Cells(a, 15), "#.##0,00")
TextBox9.Text = Format(TextBox9.Text, "#,##0.00")
Else
TextBox9 = Format("0,00")
End If
If Cells(a, 16) "" Then
TextBox10 = Format(Cells(a, 16), "#.##0,00")
TextBox10.Text = Format(TextBox10.Text, "#,##0.00")
Else
TextBox10 = Format("0,00")
End If
If Cells(a, 17) "" Then
TextBox11 = Format(Cells(a, 17), "#.##0,00")
TextBox11.Text = Format(TextBox11.Text, "#,##0.00")
Else
TextBox11 = Format("0,00")
End If
If Cells(a, 18) "" Then
TextBox12 = Format(Cells(a, 18), "#.##0,00")
TextBox12.Text = Format(TextBox12.Text, "#,##0.00")
Else
TextBox12 = Format("0,00")
End If
If Cells(a, 19) "" Then
TextBox13 = Format(Cells(a, 19), "#.##0,00")
TextBox13.Text = Format(TextBox13.Text, "#,##0.00")
Else
TextBox13 = Format("0,00")
End If
If Cells(a, 20) "" Then
TextBox14 = Format(Cells(a, 20), "#.##0,00")
TextBox14.Text = Format(TextBox14.Text, "#,##0.00")
Else
TextBox14 = Format("0,00")
End If
If Cells(a, 21) "" Then
TextBox15 = Format(Cells(a, 21), "#.##0,00")
TextBox15.Text = Format(TextBox15.Text, "#,##0.00")
Else
TextBox15 = Format("0,00")
End If
If Cells(a, 22) "" Then
TextBox16 = Format(Cells(a, 22), "#.##0,00")
TextBox16.Text = Format(TextBox16.Text, "#,##0.00")
Else
TextBox16 = Format("0,00")
End If
If Cells(a, 23) "" Then
TextBox17 = Format(Cells(a, 23), "#.##0,00")
TextBox17.Text = Format(TextBox17.Text, "#,##0.00")
Else
TextBox17 = Format("0,00")
End If
If Cells(a, 24) "" Then
TextBox18 = Format(Cells(a, 24), "#.##0,00")
TextBox18.Text = Format(TextBox18.Text, "#,##0.00")
Else
TextBox18 = Format("0,00")
End If
If Cells(a, 25) "" Then
TextBox19 = Format(Cells(a, 25), "#.##0,00")
TextBox19.Text = Format(TextBox19.Text, "#,##0.00")
Else
TextBox19 = Format("0,00")
End If
If Cells(a, 26) "" Then
TextBox20 = Format(Cells(a, 26), "#.##0,00")
TextBox20.Text = Format(TextBox20.Text, "#,##0.00")
Else
TextBox20 = Format("0,00")
End If
If Cells(a, 27) "" Then
TextBox21 = Format(Cells(a, 27), "#.##0,00")
TextBox21.Text = Format(TextBox21.Text, "#,##0.00")
Else
TextBox21 = Format("0,00")
End If
If Cells(a, 28) "" Then
TextBox22 = Format(Cells(a, 28), "#.##0,00")
TextBox22.Text = Format(TextBox22.Text, "#,##0.00")
Else
TextBox22 = Format("0,00")
End If
If Cells(a, 29) "" Then
TextBox23 = Format(Cells(a, 29), "#.##0,00")
TextBox23.Text = Format(TextBox23.Text, "#,##0.00")
Else
TextBox23 = Format("0,00")
End If
If Cells(a, 30) "" Then
TextBox24 = Format(Cells(a, 30), "#.##0,00")
TextBox24.Text = Format(TextBox24.Text, "#,##0.00")
Else
TextBox24 = Format("0,00")
End If
If Cells(a, 31) "" Then
TextBox25 = Format(Cells(a, 31), "#.##0,00")
TextBox25.Text = Format(TextBox25.Text, "#,##0.00")
Else
TextBox25 = Format("0,00")
End If
If Cells(a, 32) "" Then
TextBox26 = Format(Cells(a, 32), "#.##0,00")
TextBox26.Text = Format(TextBox26.Text, "#,##0.00")
Else
TextBox26 = Format("0,00")
End If
If Cells(a, 33) "" Then
TextBox27 = Format(Cells(a, 33), "#.##0,00")
TextBox27.Text = Format(TextBox27.Text, "#,##0.00")
Else
TextBox27 = Format("0,00")
End If
If Cells(a, 34) "" Then
TextBox28 = Format(Cells(a, 34), "#.##0,00")
TextBox28.Text = Format(TextBox28.Text, "#,##0.00")
Else
TextBox28 = Format("0,00")
End If
If Cells(a, 35) "" Then
TextBox29 = Format(Cells(a, 35), "#.##0,00")
TextBox29.Text = Format(TextBox29.Text, "#,##0.00")
Else
TextBox29 = Format("0,00")
End If
If Cells(a, 36) "" Then
TextBox30 = Format(Cells(a, 36), "#.##0,00")
TextBox30.Text = Format(TextBox30.Text, "#,##0.00")
Else
TextBox30 = Format("0,00")
End If
If Cells(a, 37) "" Then
TextBox31 = Format(Cells(a, 37), "#.##0,00")
TextBox31.Text = Format(TextBox31.Text, "#,##0.00")
Else
TextBox31 = Format("0,00")
End If
If Cells(a, 38) "" Then
TextBox32 = Format(Cells(a, 38), "#.##0,00")
TextBox32.Text = Format(TextBox32.Text, "#,##0.00")
Else
TextBox32 = Format("0,00")
End If
Label1.Visible = True
TextBoxDaten.Visible = True
Label14.Visible = True
Label2.Visible = True
TextBoxVorgang.Visible = True
Label13.Visible = True
Label17.Visible = True
Label15.Visible = True
TextBox_BetragEinlesen.Visible = True
CommandButton_AendSpeich.Visible = True
End With
End If
End Sub
Private Sub CommandButton_AendSpeich_Click()
Dim lngzeile As Long
lngzeile = ComboBox_RechnAusw.ListIndex + 1
Dim iAntwort As Integer
' Abfrage ob Pflichtfelder gefüllt sind
If TextBox_Fälligkeit.Value = "" Or TextBox_BetragEinlesen.Value = 0 Or TextBoxDaten.Value = _
_
"" _
Or TextBoxVorgang.Value = "" Then
MsgBox "Bitte alle Pflichtfelder * füllen!", vbInformation, "Achtung"
TextBoxleer.SetFocus
Else
'Abfrage ob Einnahmekonten im plus und Ausgabekonten im minus gebucht sind
If TextBox1.Value 0 Or TextBox11.Value > 0 Or TextBox12.Value > 0 Or TextBox13.Value > _
_
0 Or TextBox14.Value > 0 _
Or TextBox15.Value > 0 Or TextBox16.Value > 0 Or TextBox17.Value > 0 Or TextBox18.Value > _
_
0 Or TextBox19.Value > 0 _
Or TextBox20.Value > 0 Or TextBox21.Value > 0 Or TextBox22.Value > 0 Or TextBox23.Value > _
_
0 Or TextBox24.Value > 0 _
Or TextBox25.Value > 0 Or TextBox26.Value > 0 Or TextBox27.Value > 0 Or TextBox28.Value > _
_
0 Or TextBox29.Value > 0 _
Or TextBox30.Value > 0 Or TextBox31.Value > 0 Or TextBox32.Value > 0 Then
iAntwort = MsgBox("Einnahmekonten müssen normalerweise mit Pluswerten und Ausgabekonten _
mit Minuswerten gebucht werden! Ist diese Buchung eine Ausnahme?", vbYesNo)
If iAntwort = vbNo Then
Me.TextBox_BetragEinlesen.Text = "0.00"
Me.TextBox_Fälligkeit.Text = "" 'gibt 0,00 aus
Me.TextBoxDaten.Text = ""
Me.TextBoxVorgang.Text = ""
Me.TextBox1.Text = "0,00"
Me.TextBox2.Text = "0,00"
Me.TextBox3.Text = "0,00"
Me.TextBox4.Text = "0,00"
Me.TextBox5.Text = "0,00"
Me.TextBox6.Text = "0,00"
Me.TextBox7.Text = "0,00"
Me.TextBox8.Text = "0,00"
Me.TextBox9.Text = "0,00"
Me.TextBox10.Text = "0,00"
Me.TextBox11.Text = "0,00"
Me.TextBox12.Text = "0,00"
Me.TextBox13.Text = "0,00"
Me.TextBox14.Text = "0,00"
Me.TextBox15.Text = "0,00"
Me.TextBox16.Text = "0,00"
Me.TextBox17.Text = "0,00"
Me.TextBox18.Text = "0,00"
Me.TextBox19.Text = "0,00"
Me.TextBox20.Text = "0,00"
Me.TextBox21.Text = "0,00"
Me.TextBox22.Text = "0,00"
Me.TextBox23.Text = "0,00"
Me.TextBox24.Text = "0,00"
Me.TextBox25.Text = "0,00"
Me.TextBox26.Text = "0,00"
Me.TextBox27.Text = "0,00"
Me.TextBox28.Text = "0,00"
Me.TextBox29.Text = "0,00"
Me.TextBox30.Text = "0,00"
Me.TextBox31.Text = "0,00"
Me.TextBox32.Text = "0,00"
TextBoxleer.SetFocus
End If
If iAntwort = vbYes Then
TrageWerteEin lngzeile
End If
Else
TrageWerteEin lngzeile
End If
End If
'Call SortEinbl_EingAusg
Range("A6").Select
'Buchungen Sortieren nach Datum
'Call SortierenBuchungen
' leere Zeilen ausblenden
'Call EinAusblenden
Unload Me
BuchungVorbAend.Show
End Sub
Function TrageWerteEin(ByVal lngzeile As Long)
Cells(lngzeile, 6).Value = TextBox_BetragEinlesen.Value * 1
Cells(lngzeile, 1).Value = TextBox_Fälligkeit.Value
Cells(lngzeile, 2).Value = ComboBoxZahlung.Value
Cells(lngzeile, 3).Value = TextBoxDaten.Value
Cells(lngzeile, 4).Value = TextBoxVorgang.Value
Cells(lngzeile, 7).Value = TextBox1.Value * 1
Cells(lngzeile, 8).Value = TextBox2.Value * 1
Cells(lngzeile, 9).Value = TextBox3.Value * 1
Cells(lngzeile, 10).Value = TextBox4.Value * 1
Cells(lngzeile, 11).Value = TextBox5.Value * 1
Cells(lngzeile, 12).Value = TextBox6.Value * 1
Cells(lngzeile, 13).Value = TextBox7.Value * 1
Cells(lngzeile, 14).Value = TextBox8.Value * 1
Cells(lngzeile, 15).Value = TextBox9.Value * 1
Cells(lngzeile, 16).Value = TextBox10.Value * 1
Cells(lngzeile, 17).Value = TextBox11.Value * 1
Cells(lngzeile, 18).Value = TextBox12.Value * 1
Cells(lngzeile, 19).Value = TextBox13.Value * 1
Cells(lngzeile, 20).Value = TextBox14.Value * 1
Cells(lngzeile, 21).Value = TextBox15.Value * 1
Cells(lngzeile, 22).Value = TextBox16.Value * 1
Cells(lngzeile, 23).Value = TextBox17.Value * 1
Cells(lngzeile, 24).Value = TextBox18.Value * 1
Cells(lngzeile, 25).Value = TextBox19.Value * 1
Cells(lngzeile, 26).Value = TextBox20.Value * 1
Cells(lngzeile, 27).Value = TextBox21.Value * 1
Cells(lngzeile, 28).Value = TextBox22.Value * 1
Cells(lngzeile, 29).Value = TextBox23.Value * 1
Cells(lngzeile, 30).Value = TextBox24.Value * 1
Cells(lngzeile, 31).Value = TextBox25.Value * 1
Cells(lngzeile, 32).Value = TextBox26.Value * 1
Cells(lngzeile, 33).Value = TextBox27.Value * 1
Cells(lngzeile, 34).Value = TextBox28.Value * 1
Cells(lngzeile, 35).Value = TextBox29.Value * 1
Cells(lngzeile, 36).Value = TextBox30.Value * 1
Cells(lngzeile, 37).Value = TextBox31.Value * 1
Cells(lngzeile, 38).Value = TextBox32.Value * 1
Cells(lngzeile, 42).Value = TextBoxBemerk.Value
End Function
Private Sub TextBox_Betrag_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox_Betrag_AfterUpdate()
If TextBox_Betrag = Empty Then TextBox_Betrag = 0
TextBox_Betrag = Format(CDbl(TextBox_Betrag.Text), "#,##0.00")
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox1_AfterUpdate()
If TextBox1 = Empty Then TextBox1 = 0
TextBox1 = Format(CDbl(TextBox1.Text), "#,##0.00")
End Sub
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox2_AfterUpdate()
If TextBox2 = Empty Then TextBox2 = 0
TextBox2 = Format(CDbl(TextBox2.Text), "#,##0.00")
End Sub
Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox3_AfterUpdate()
If TextBox3 = Empty Then TextBox3 = 0
TextBox3 = Format(CDbl(TextBox3.Text), "#,##0.00")
End Sub
Private Sub TextBox4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox4_AfterUpdate()
If TextBox4 = Empty Then TextBox4 = 0
TextBox4 = Format(CDbl(TextBox4.Text), "#,##0.00")
End Sub
Private Sub TextBox5_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox5_AfterUpdate()
If TextBox5 = Empty Then TextBox5 = 0
TextBox5 = Format(CDbl(TextBox5.Text), "#,##0.00")
End Sub
Private Sub TextBox6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox6_AfterUpdate()
If TextBox6 = Empty Then TextBox6 = 0
TextBox6 = Format(CDbl(TextBox6.Text), "#,##0.00")
End Sub
Private Sub TextBox7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox7_AfterUpdate()
If TextBox7 = Empty Then TextBox7 = 0
TextBox7 = Format(CDbl(TextBox7.Text), "#,##0.00")
End Sub
Private Sub TextBox8_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox8_AfterUpdate()
If TextBox8 = Empty Then TextBox8 = 0
TextBox8 = Format(CDbl(TextBox8.Text), "#,##0.00")
End Sub
Private Sub TextBox9_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox9_AfterUpdate()
If TextBox9 = Empty Then TextBox9 = 0
TextBox9 = Format(CDbl(TextBox9.Text), "#,##0.00")
End Sub
Private Sub TextBox10_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox10_AfterUpdate()
If TextBox10 = Empty Then TextBox10 = 0
TextBox10 = Format(CDbl(TextBox10.Text), "#,##0.00")
End Sub
Private Sub TextBox11_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox11_AfterUpdate()
If TextBox11 = Empty Then TextBox11 = 0
TextBox11 = Format(CDbl(TextBox11.Text), "#,##0.00")
End Sub
Private Sub TextBox12_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox12_AfterUpdate()
If TextBox12 = Empty Then TextBox12 = 0
TextBox12 = Format(CDbl(TextBox12.Text), "#,##0.00")
End Sub
Private Sub TextBox13_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox13_AfterUpdate()
If TextBox13 = Empty Then TextBox13 = 0
TextBox13 = Format(CDbl(TextBox13.Text), "#,##0.00")
End Sub
Private Sub TextBox14_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox14_AfterUpdate()
If TextBox14 = Empty Then TextBox14 = 0
TextBox14 = Format(CDbl(TextBox14.Text), "#,##0.00")
End Sub
Private Sub TextBox15_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox15_AfterUpdate()
If TextBox15 = Empty Then TextBox15 = 0
TextBox15 = Format(CDbl(TextBox15.Text), "#,##0.00")
End Sub
Private Sub TextBox16_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox16_AfterUpdate()
If TextBox16 = Empty Then TextBox16 = 0
TextBox16 = Format(CDbl(TextBox16.Text), "#,##0.00")
End Sub
Private Sub TextBox17_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox17_AfterUpdate()
If TextBox17 = Empty Then TextBox17 = 0
TextBox17 = Format(CDbl(TextBox17.Text), "#,##0.00")
End Sub
Private Sub TextBox18_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox18_AfterUpdate()
If TextBox18 = Empty Then TextBox18 = 0
TextBox18 = Format(CDbl(TextBox18.Text), "#,##0.00")
End Sub
Private Sub TextBox19_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox19_AfterUpdate()
If TextBox19 = Empty Then TextBox19 = 0
TextBox19 = Format(CDbl(TextBox19.Text), "#,##0.00")
End Sub
Private Sub TextBox20_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox20_AfterUpdate()
If TextBox20 = Empty Then TextBox20 = 0
TextBox20 = Format(CDbl(TextBox20.Text), "#,##0.00")
End Sub
Private Sub TextBox21_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox21_AfterUpdate()
If TextBox21 = Empty Then TextBox21 = 0
TextBox21 = Format(CDbl(TextBox21.Text), "#,##0.00")
End Sub
Private Sub TextBox22_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox22_AfterUpdate()
If TextBox22 = Empty Then TextBox22 = 0
TextBox22 = Format(CDbl(TextBox22.Text), "#,##0.00")
End Sub
Private Sub TextBox23_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox23_AfterUpdate()
If TextBox23 = Empty Then TextBox23 = 0
TextBox23 = Format(CDbl(TextBox23.Text), "#,##0.00")
End Sub
Private Sub TextBox24_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox24_AfterUpdate()
If TextBox24 = Empty Then TextBox24 = 0
TextBox24 = Format(CDbl(TextBox24.Text), "#,##0.00")
End Sub
Private Sub TextBox25_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox25_AfterUpdate()
If TextBox25 = Empty Then TextBox25 = 0
TextBox25 = Format(CDbl(TextBox25.Text), "#,##0.00")
End Sub
Private Sub TextBox26_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox26_AfterUpdate()
If TextBox26 = Empty Then TextBox26 = 0
TextBox26 = Format(CDbl(TextBox26.Text), "#,##0.00")
End Sub
Private Sub TextBox27_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox27_AfterUpdate()
If TextBox27 = Empty Then TextBox27 = 0
TextBox27 = Format(CDbl(TextBox27.Text), "#,##0.00")
End Sub
Private Sub TextBox28_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox28_AfterUpdate()
If TextBox28 = Empty Then TextBox28 = 0
TextBox28 = Format(CDbl(TextBox28.Text), "#,##0.00")
End Sub
Private Sub TextBox29_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox29_AfterUpdate()
If TextBox29 = Empty Then TextBox29 = 0
TextBox29 = Format(CDbl(TextBox29.Text), "#,##0.00")
End Sub
Private Sub TextBox30_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox30_AfterUpdate()
If TextBox30 = Empty Then TextBox30 = 0
TextBox30 = Format(CDbl(TextBox30.Text), "#,##0.00")
End Sub
Private Sub TextBox31_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox31_AfterUpdate()
If TextBox31 = Empty Then TextBox31 = 0
TextBox31 = Format(CDbl(TextBox31.Text), "#,##0.00")
End Sub
Private Sub TextBox32_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57, 8, 44, 45
Case Else
KeyAscii = 0
MsgBox "Es sind nur Ziffern, Komma und Minus zulässig!", vbInformation, "Hinweis"
End Select
End Sub
Private Sub TextBox32_AfterUpdate()
If TextBox32 = Empty Then TextBox32 = 0
TextBox32 = Format(CDbl(TextBox32.Text), "#,##0.00")
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then Cancel = True 'Schließkreuz ausschalten
End Sub
Private Sub CommandButton4_Click() ' Formular schließen
Application.DisplayAlerts = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Save
Unload Me
End Sub
mfg, Andreas