ich habe es geahnt - ich stell mich blöd an...
hier meine Code (du wolltest es nicht anders) smile
Wo muss ich deinen Befehl eingeben, dass er die entsprechenden Zellen eine Makrozuweisung erhalten?
Option Explicit
Private Sub CommandButton1_Click()
'Sheets("Rechnungen").Unprotect Password:="Sw36988#"
Dim lUpdRow As Long
With Worksheets("Rechnungen")
'Erste freie Zelle in Spalte A oder Zeile die geändert wird und neue Zeile einfügen
If pLRow = 0 Then
lUpdRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Rows("3:3").Select
Selection.Copy
Dim newRow As Long
newRow = Cells(Rows.Count, 5).End(xlUp).Row + 1
Cells(newRow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Else
lUpdRow = pLRow
End If
'Zellenzuweisung der Userform in Text- bzw. ComboBoxen
' nur Texte
.Cells(lUpdRow, 2).Value = ComboBox1.Value
.Cells(lUpdRow, 7).Value = ComboBox2.Value
.Cells(lUpdRow, 6).Value = ComboBox3.Value
.Cells(lUpdRow, 8).Value = TextBox1.Value
.Cells(lUpdRow, 9).Value = TextBox2.Value
.Cells(lUpdRow, 10).Value = TextBox3.Value
.Cells(lUpdRow, 88).Value = TextBox25.Value
.Cells(lUpdRow, 168).Value = TextBox25.Value
.Cells(lUpdRow, 99).Value = TextBox31.Value
.Cells(lUpdRow, 179).Value = TextBox31.Value
' nur Datum
If TextBox3 "" Then
If IsDate(TextBox3) Then
.Cells(lUpdRow, 10).Value = CDate(TextBox3)
End If
End If
.Cells(lUpdRow, 11).Value = TextBox4.Value
If TextBox4 "" Then
If IsDate(TextBox4) Then
.Cells(lUpdRow, 11).Value = CDate(TextBox4)
End If
End If
.Cells(lUpdRow, 19).Value = TextBox23.Value
If TextBox23 "" Then
If IsDate(TextBox23) Then
.Cells(lUpdRow, 19).Value = CDate(TextBox23)
End If
End If
.Cells(lUpdRow, 104).Value = TextBox24.Value
If TextBox24 "" Then
If IsDate(TextBox24) Then
.Cells(lUpdRow, 104).Value = CDate(TextBox24)
End If
End If
' nur Währung
.Cells(lUpdRow, 17).Value = TextBox5
If TextBox5 "" Then
.Cells(lUpdRow, 17).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 17).Value = CDbl(TextBox5.Text)
End If
.Cells(lUpdRow, 20).Value = TextBox11
If TextBox11 "" Then
.Cells(lUpdRow, 20).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 20).Value = CDbl(TextBox11.Text)
End If
.Cells(lUpdRow, 29).Value = TextBox13
If TextBox13 "" Then
.Cells(lUpdRow, 29).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 29).Value = CDbl(TextBox13.Text)
End If
.Cells(lUpdRow, 38).Value = TextBox14
If TextBox14 "" Then
.Cells(lUpdRow, 38).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 38).Value = CDbl(TextBox14.Text)
End If
.Cells(lUpdRow, 47).Value = TextBox15
If TextBox15 "" Then
.Cells(lUpdRow, 47).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 47).Value = CDbl(TextBox15.Text)
End If
.Cells(lUpdRow, 56).Value = TextBox16
If TextBox16 "" Then
.Cells(lUpdRow, 56).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 56).Value = CDbl(TextBox16.Text)
End If
.Cells(lUpdRow, 65).Value = TextBox12
If TextBox12 "" Then
.Cells(lUpdRow, 65).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 65).Value = CDbl(TextBox12.Text)
End If
.Cells(lUpdRow, 106).Value = TextBox17
If TextBox17 "" Then
.Cells(lUpdRow, 106).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 106).Value = CDbl(TextBox17.Text)
End If
.Cells(lUpdRow, 114).Value = TextBox19
If TextBox19 "" Then
.Cells(lUpdRow, 114).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 114).Value = CDbl(TextBox19.Text)
End If
.Cells(lUpdRow, 122).Value = TextBox20
If TextBox20 "" Then
.Cells(lUpdRow, 122).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 122).Value = CDbl(TextBox20.Text)
End If
.Cells(lUpdRow, 130).Value = TextBox21
If TextBox21 "" Then
.Cells(lUpdRow, 130).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 130).Value = CDbl(TextBox21.Text)
End If
.Cells(lUpdRow, 138).Value = TextBox22
If TextBox22 "" Then
.Cells(lUpdRow, 138).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 138).Value = CDbl(TextBox22.Text)
End If
.Cells(lUpdRow, 146).Value = TextBox18
If TextBox18 "" Then
.Cells(lUpdRow, 146).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 146).Value = CDbl(TextBox18.Text)
End If
.Cells(lUpdRow, 89).Value = TextBox26
If TextBox26 "" Then
.Cells(lUpdRow, 89).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 89).Value = CDbl(TextBox26.Text)
End If
.Cells(lUpdRow, 169).Value = TextBox29
If TextBox29 "" Then
.Cells(lUpdRow, 169).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 169).Value = CDbl(TextBox29.Text)
End If
.Cells(lUpdRow, 100).Value = TextBox32
If TextBox32 "" Then
.Cells(lUpdRow, 100).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 100).Value = CDbl(TextBox32.Text)
End If
.Cells(lUpdRow, 180).Value = TextBox30
If TextBox30 "" Then
.Cells(lUpdRow, 180).NumberFormat = "#,##0.00 $"
.Cells(lUpdRow, 180).Value = CDbl(TextBox30.Text)
End If
End With
'Sheets("Rechnungen").Protect UserInterfaceOnly:=True, Password:="Sw36988#"
'Userform schließen
Unload UserForm_Rechnung
End Sub
Private Sub CommandButton2_Click()
Unload UserForm_Rechnung
End Sub
Private Sub TextBox11_Change()
Call Summe1
End Sub
Private Sub TextBox12_Change()
Call Summe1
End Sub
Private Sub TextBox13_Change()
Call Summe1
End Sub
Private Sub TextBox14_Change()
Call Summe1
End Sub
Private Sub TextBox15_Change()
Call Summe1
End Sub
Private Sub TextBox16_Change()
Call Summe1
End Sub
Private Sub Summe1()
If Len(TextBox11.Text) = 0 Then
TextBox11.Value = 0
End If
If Len(TextBox12.Text) = 0 Then
TextBox12.Value = 0
End If
If Len(TextBox13.Text) = 0 Then
TextBox13.Value = 0
End If
If Len(TextBox14.Text) = 0 Then
TextBox14.Value = 0
End If
If Len(TextBox15.Text) = 0 Then
TextBox15.Value = 0
End If
If Len(TextBox16.Text) = 0 Then
TextBox16.Value = 0
End If
Me.TextBox34.Value = CDbl(TextBox11.Value) + CDbl(TextBox12.Value) + CDbl(TextBox13.Value) + _
CDbl(TextBox14.Value) + CDbl(TextBox15.Value) + CDbl(TextBox16.Value)
End Sub
Private Sub TextBox17_Change()
Call Summe2
End Sub
Private Sub TextBox18_Change()
Call Summe2
End Sub
Private Sub TextBox19_Change()
Call Summe2
End Sub
Private Sub TextBox20_Change()
Call Summe2
End Sub
Private Sub TextBox21_Change()
Call Summe2
End Sub
Private Sub TextBox22_Change()
Call Summe2
End Sub
Private Sub Summe2()
If Len(TextBox17.Text) = 0 Then
TextBox17.Value = 0
End If
If Len(TextBox18.Text) = 0 Then
TextBox18.Value = 0
End If
If Len(TextBox19.Text) = 0 Then
TextBox19.Value = 0
End If
If Len(TextBox20.Text) = 0 Then
TextBox20.Value = 0
End If
If Len(TextBox21.Text) = 0 Then
TextBox21.Value = 0
End If
If Len(TextBox22.Text) = 0 Then
TextBox22.Value = 0
End If
Me.TextBox35.Value = CDbl(TextBox17.Value) + CDbl(TextBox18.Value) + CDbl(TextBox19.Value) + _
CDbl(TextBox20.Value) + CDbl(TextBox21.Value) + CDbl(TextBox22.Value)
End Sub
Private Sub TextBox34_Change()
TextBox34.Value = Format(TextBox34.Value, "#,##0.00 ")
End Sub
Private Sub TextBox35_Change()
TextBox35.Value = Format(TextBox35.Value, "#,##0.00 ")
End Sub
Private Sub UserForm_Initialize()
'Zuweisung Bauteilbezeichnung in Userform
Me.Label13 = Worksheets("Basis").Range("B11").Text
Me.Label14 = Worksheets("Basis").Range("B12").Text
Me.Label15 = Worksheets("Basis").Range("B13").Text
Me.Label16 = Worksheets("Basis").Range("B14").Text
Me.Label17 = Worksheets("Basis").Range("B15").Text
Me.Label18 = Worksheets("Basis").Range("B16").Text
'Drop-Down-Zuweisung ComboBoxen
ComboBox1.List = Worksheets("Basis").Range("E4:E23").Value
ComboBox2.List = Worksheets("SDD").Range("L2:L30").Value
ComboBox3.List = Worksheets("SDD").Range("B2:B30").Value
'Textzuweisung bei neuer Rechnung
ComboBox1 = "Auftragnehmer wählen"
With ThisWorkbook.Worksheets("Rechnungen")
If pLRow = 0 Then
Caption = "NEUE RECHNUNG ANLEGEN"
'Text- ComboBoxen Grundeintrag
Else
Caption = "RECHNUNGSSTATUS AKTUALISIEREN:" & .Cells(pLRow, 2).Text
ComboBox1.Text = .Cells(pLRow, 2).Text
ComboBox2.Text = .Cells(pLRow, 7).Text
ComboBox3.Text = .Cells(pLRow, 6).Text
TextBox1.Text = .Cells(pLRow, 8).Text
TextBox2.Text = .Cells(pLRow, 9).Text
TextBox3.Text = .Cells(pLRow, 10).Text
TextBox4.Text = .Cells(pLRow, 11).Text
TextBox23.Text = .Cells(pLRow, 19).Text
TextBox24.Text = .Cells(pLRow, 104).Text
TextBox5.Text = .Cells(pLRow, 17).Text
TextBox11.Text = .Cells(pLRow, 20).Text
TextBox13.Text = .Cells(pLRow, 29).Text
TextBox14.Text = .Cells(pLRow, 38).Text
TextBox15.Text = .Cells(pLRow, 47).Text
TextBox16.Text = .Cells(pLRow, 56).Text
TextBox12.Text = .Cells(pLRow, 65).Text
TextBox17.Text = .Cells(pLRow, 106).Text
TextBox19.Text = .Cells(pLRow, 114).Text
TextBox20.Text = .Cells(pLRow, 122).Text
TextBox21.Text = .Cells(pLRow, 130).Text
TextBox22.Text = .Cells(pLRow, 138).Text
TextBox18.Text = .Cells(pLRow, 146).Text
TextBox25.Text = .Cells(pLRow, 88).Text
TextBox26.Text = .Cells(pLRow, 89).Text
TextBox29.Text = .Cells(pLRow, 169).Text
TextBox31.Text = .Cells(pLRow, 99).Text
TextBox32.Text = .Cells(pLRow, 100).Text
TextBox30.Text = .Cells(pLRow, 180).Text
End If
End With
End Sub