Ich hab ein Problem mit dem Schleifen binden, hab da ein ellenlanges Makro zum ändern oder neu schreiben von Daten in eine Tabelle über eine Userform.
Ich wäre sehr dankbar wenn mir Einer dieses Makro kürzen könnte.
Mit freundlichen Grüßen Wolfgang
Private Sub CommandButton2_Click()
CommandButton2.Caption = "Datensatz" & Chr(13) & "ändern / hinzufügen"
'Hier wird Meldung erzeugt ob Daten in der Datenbank geändert + oder Neu dazukommen sollen !?!?!?!
Dim xZeile As Long
Dim mldg, stil, titel, grc
On Error GoTo fehlermeldung
'Meldung ob Daten zum löschen vorhanden sind!
If TextBox1 = "" Or TextBox2 = "" Or ComboBox3 = "" Then
MsgBox "Kein Datensatz zum ändern / hinzufügen vorhanden!", vbInformation, " Info"
Exit Sub
Else
mldg = "Soll dieser Datensatz in die Datenbank übernommen werden ?"
stil = vbYesNo + vbInformation + vbDefaultButton2
titel = "Frage ?"
grc = MsgBox(mldg, stil, titel)
If grc = vbYes Then
Else
Exit Sub
End If
If TextBox1 = "" Then Exit Sub
If ComboBox1.ListIndex = 0 Or ComboBox2.ListIndex = 0 Then
xZeile = wks1.[a65536].End(xlUp).Row + 1
Else
xZeile = ComboBox1.ListIndex + 1 Or ComboBox2.ListIndex + 1
End If
wks1.Cells(xZeile, 1) = TextBox1.Value ' bei Zahleneingabe:.Value zum rechnen, cdbl(Textbox1), cint
wks1.Cells(xZeile, 2) = TextBox2
wks1.Cells(xZeile, 3) = ComboBox3
wks1.Cells(xZeile, 76) = ComboBox4
wks1.Cells(xZeile, 77) = ComboBox5
wks1.Cells(xZeile, 78) = ComboBox6
wks1.Cells(xZeile, 8) = TextBox3
wks1.Cells(xZeile, 4) = ComboBox7
wks1.Cells(xZeile, 5) = ComboBox8
wks1.Cells(xZeile, 6) = ComboBox9
wks1.Cells(xZeile, 7) = ComboBox10
wks1.Cells(xZeile, 9) = ComboBox11
wks1.Cells(xZeile, 10) = ComboBox12
'Page2
wks1.Cells(xZeile, 11) = ComboBox13
wks1.Cells(xZeile, 12) = TextBox4
wks1.Cells(xZeile, 13) = ComboBox14
wks1.Cells(xZeile, 14) = ComboBox15
wks1.Cells(xZeile, 15) = ComboBox16
wks1.Cells(xZeile, 16) = ComboBox17
wks1.Cells(xZeile, 17) = TextBox5
wks1.Cells(xZeile, 18) = TextBox6
wks1.Cells(xZeile, 19) = TextBox7
wks1.Cells(xZeile, 20) = ComboBox18
wks1.Cells(xZeile, 21) = TextBox8
wks1.Cells(xZeile, 22) = ComboBox19
wks1.Cells(xZeile, 23) = ComboBox20
wks1.Cells(xZeile, 24) = ComboBox21
wks1.Cells(xZeile, 25) = ComboBox22
wks1.Cells(xZeile, 26) = TextBox9
wks1.Cells(xZeile, 27) = TextBox10
wks1.Cells(xZeile, 28) = TextBox11
wks1.Cells(xZeile, 29) = ComboBox23
wks1.Cells(xZeile, 30) = TextBox12
wks1.Cells(xZeile, 31) = ComboBox24
wks1.Cells(xZeile, 32) = ComboBox25
wks1.Cells(xZeile, 33) = ComboBox26
wks1.Cells(xZeile, 34) = ComboBox27
wks1.Cells(xZeile, 35) = TextBox13
wks1.Cells(xZeile, 36) = TextBox14
wks1.Cells(xZeile, 37) = TextBox15
wks1.Cells(xZeile, 38) = ComboBox28
wks1.Cells(xZeile, 39) = TextBox16
wks1.Cells(xZeile, 40) = ComboBox29
wks1.Cells(xZeile, 41) = ComboBox30
wks1.Cells(xZeile, 42) = ComboBox31
wks1.Cells(xZeile, 43) = ComboBox32
wks1.Cells(xZeile, 44) = TextBox17
wks1.Cells(xZeile, 45) = TextBox18
wks1.Cells(xZeile, 46) = TextBox19
wks1.Cells(xZeile, 47) = ComboBox33
wks1.Cells(xZeile, 48) = TextBox20
wks1.Cells(xZeile, 49) = ComboBox34
wks1.Cells(xZeile, 50) = ComboBox35
wks1.Cells(xZeile, 51) = ComboBox36
wks1.Cells(xZeile, 52) = ComboBox37
wks1.Cells(xZeile, 53) = TextBox21
wks1.Cells(xZeile, 54) = TextBox22
wks1.Cells(xZeile, 55) = TextBox23
'Page3
wks1.Cells(xZeile, 56) = Replace(TextBox24, vbCr, "")
wks1.Cells(xZeile, 57) = Replace(TextBox25, vbCr, "")
'Page4
wks1.Cells(xZeile, 58) = TextBox26
wks1.Cells(xZeile, 59) = Replace(TextBox27, vbCr, "")
wks1.Cells(xZeile, 60) = TextBox28
wks1.Cells(xZeile, 61) = TextBox29
wks1.Cells(xZeile, 62) = TextBox30
wks1.Cells(xZeile, 63) = ComboBox38
wks1.Cells(xZeile, 64) = ComboBox39
wks1.Cells(xZeile, 65) = ComboBox40
wks1.Cells(xZeile, 66) = ComboBox41
wks1.Cells(xZeile, 67) = ComboBox42
wks1.Cells(xZeile, 68) = ComboBox43
wks1.Cells(xZeile, 69) = ComboBox44
wks1.Cells(xZeile, 70) = ComboBox45
wks1.Cells(xZeile, 71) = ComboBox46
wks1.Cells(xZeile, 72) = ComboBox47
wks1.Cells(xZeile, 73) = ComboBox48
wks1.Cells(xZeile, 74) = ComboBox49
wks1.Cells(xZeile, 75) = ComboBox50
TextBox1.Value = Format(WorksheetFunction.Max(wks1.Range("A2:A1000")) + 1, "0000")
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
TextBox12 = ""
TextBox13 = ""
TextBox14 = ""
TextBox15 = ""
TextBox16 = ""
TextBox17 = ""
TextBox18 = ""
TextBox19 = ""
TextBox20 = ""
TextBox21 = ""
TextBox22 = ""
TextBox23 = ""
TextBox24 = ""
TextBox25 = ""
TextBox26 = ""
TextBox27 = ""
TextBox28 = ""
TextBox29 = ""
TextBox30 = ""
ComboBox3 = Format(Date, "DD.MM.YYYY")
ComboBox4 = ""
ComboBox5 = ""
ComboBox6 = ""
ComboBox8 = ""
ComboBox9 = ""
ComboBox10 = ""
ComboBox11 = ""
ComboBox12 = ""
ComboBox13 = ""
ComboBox14 = ""
ComboBox15 = ""
ComboBox16 = ""
ComboBox17 = ""
ComboBox18 = ""
ComboBox19 = ""
ComboBox20 = ""
ComboBox21 = ""
ComboBox22 = ""
ComboBox23 = ""
ComboBox24 = ""
ComboBox25 = ""
ComboBox26 = ""
ComboBox27 = ""
ComboBox28 = ""
ComboBox29 = ""
ComboBox30 = ""
ComboBox31 = ""
ComboBox32 = ""
ComboBox33 = ""
ComboBox34 = ""
ComboBox35 = ""
ComboBox36 = ""
ComboBox37 = ""
ComboBox38 = ""
ComboBox39 = ""
ComboBox40 = ""
ComboBox41 = ""
ComboBox42 = ""
ComboBox43 = ""
ComboBox44 = ""
ComboBox45 = ""
ComboBox46 = ""
ComboBox47 = ""
ComboBox48 = ""
ComboBox49 = ""
ComboBox50 = Application.UserName
'Sortiervorgang
Call Sortieren_UserForm_Objekt_Nr_Aufwärts
UserForm_Initialize
End If
fehlermeldung:
End Sub