Hallo Hajo,
leider kann ich die Datei nicht hochladen, da die Datei etwa 13 Mb umfasst! Ich kann leider nur den vba code hochladen wo die daten nach auswahl der dropdown eingelesen werden. Und wenn der commond button gedrückt wird dieses wieder in die tabelle zurückgegeben wird! Ich hoffe du kannst mir nun helfen!
Option Explicit
Private Sub ComboBox1_Click() ' Ausgabe in Felder wenn Project in Dropdown ausgewählt
If ComboBox1.ListIndex <> 0 Then
TextBox1 = Cells(ComboBox1.ListIndex + 1, 1)
TextBox2 = Cells(ComboBox1.ListIndex + 1, 2)
TextBox3 = Cells(ComboBox1.ListIndex + 1, 3)
TextBox4 = Cells(ComboBox1.ListIndex + 1, 4)
TextBox5 = Cells(ComboBox1.ListIndex + 1, 5)
TextBox76 = Cells(ComboBox1.ListIndex + 1, 6)
TextBox20 = Cells(ComboBox1.ListIndex + 1, 7)
TextBox6 = Cells(ComboBox1.ListIndex + 1, 8)
CheckBox1 = Cells(ComboBox1.ListIndex + 1, 9)
ComboBox2 = Cells(ComboBox1.ListIndex + 1, 11)
TextBox26 = Cells(ComboBox1.ListIndex + 1, 12)
ComboBox3 = Cells(ComboBox1.ListIndex + 1, 13)
TextBox28 = Cells(ComboBox1.ListIndex + 1, 14)
ComboBox4 = Cells(ComboBox1.ListIndex + 1, 15)
TextBox30 = Cells(ComboBox1.ListIndex + 1, 16)
ComboBox5 = Cells(ComboBox1.ListIndex + 1, 17)
TextBox27 = Cells(ComboBox1.ListIndex + 1, 18)
ComboBox6 = Cells(ComboBox1.ListIndex + 1, 19)
TextBox29 = Cells(ComboBox1.ListIndex + 1, 20)
TextBox8 = Cells(ComboBox1.ListIndex + 1, 21)
TextBox9 = Cells(ComboBox1.ListIndex + 1, 22)
CheckBox2 = Cells(ComboBox1.ListIndex + 1, 23)
ComboBox7 = Cells(ComboBox1.ListIndex + 1, 25)
TextBox35 = Cells(ComboBox1.ListIndex + 1, 26)
ComboBox8 = Cells(ComboBox1.ListIndex + 1, 27)
TextBox34 = Cells(ComboBox1.ListIndex + 1, 28)
ComboBox9 = Cells(ComboBox1.ListIndex + 1, 29)
TextBox33 = Cells(ComboBox1.ListIndex + 1, 30)
ComboBox10 = Cells(ComboBox1.ListIndex + 1, 31)
TextBox32 = Cells(ComboBox1.ListIndex + 1, 32)
ComboBox11 = Cells(ComboBox1.ListIndex + 1, 33)
TextBox31 = Cells(ComboBox1.ListIndex + 1, 34)
TextBox11 = Cells(ComboBox1.ListIndex + 1, 35)
TextBox12 = Cells(ComboBox1.ListIndex + 1, 36)
CheckBox3 = Cells(ComboBox1.ListIndex + 1, 37)
ComboBox12 = Cells(ComboBox1.ListIndex + 1, 39)
TextBox61 = Cells(ComboBox1.ListIndex + 1, 40)
ComboBox13 = Cells(ComboBox1.ListIndex + 1, 41)
TextBox62 = Cells(ComboBox1.ListIndex + 1, 42)
ComboBox14 = Cells(ComboBox1.ListIndex + 1, 43)
TextBox63 = Cells(ComboBox1.ListIndex + 1, 44)
ComboBox15 = Cells(ComboBox1.ListIndex + 1, 45)
TextBox64 = Cells(ComboBox1.ListIndex + 1, 46)
ComboBox16 = Cells(ComboBox1.ListIndex + 1, 47)
TextBox65 = Cells(ComboBox1.ListIndex + 1, 48)
TextBox14 = Cells(ComboBox1.ListIndex + 1, 49)
TextBox15 = Cells(ComboBox1.ListIndex + 1, 50)
CheckBox4 = Cells(ComboBox1.ListIndex + 1, 51)
ComboBox17 = Cells(ComboBox1.ListIndex + 1, 53)
TextBox55 = Cells(ComboBox1.ListIndex + 1, 54)
ComboBox18 = Cells(ComboBox1.ListIndex + 1, 55)
TextBox54 = Cells(ComboBox1.ListIndex + 1, 56)
ComboBox19 = Cells(ComboBox1.ListIndex + 1, 57)
TextBox53 = Cells(ComboBox1.ListIndex + 1, 58)
ComboBox20 = Cells(ComboBox1.ListIndex + 1, 59)
TextBox52 = Cells(ComboBox1.ListIndex + 1, 60)
ComboBox21 = Cells(ComboBox1.ListIndex + 1, 61)
TextBox51 = Cells(ComboBox1.ListIndex + 1, 62)
TextBox17 = Cells(ComboBox1.ListIndex + 1, 63)
TextBox18 = Cells(ComboBox1.ListIndex + 1, 64)
CheckBox5 = Cells(ComboBox1.ListIndex + 1, 65)
ComboBox22 = Cells(ComboBox1.ListIndex + 1, 67)
TextBox71 = Cells(ComboBox1.ListIndex + 1, 68)
ComboBox23 = Cells(ComboBox1.ListIndex + 1, 69)
TextBox75 = Cells(ComboBox1.ListIndex + 1, 70)
ComboBox24 = Cells(ComboBox1.ListIndex + 1, 71)
TextBox74 = Cells(ComboBox1.ListIndex + 1, 72)
ComboBox25 = Cells(ComboBox1.ListIndex + 1, 73)
TextBox73 = Cells(ComboBox1.ListIndex + 1, 74)
ComboBox26 = Cells(ComboBox1.ListIndex + 1, 75)
TextBox72 = Cells(ComboBox1.ListIndex + 1, 76)
CheckBox6 = Cells(ComboBox1.ListIndex + 1, 77)
CheckBox7 = Cells(ComboBox1.ListIndex + 1, 78)
Else
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox8 = ""
TextBox9 = ""
TextBox11 = ""
TextBox12 = ""
TextBox14 = ""
TextBox15 = ""
TextBox17 = ""
TextBox18 = ""
TextBox20 = ""
ComboBox2 = ""
ComboBox3 = ""
ComboBox4 = ""
ComboBox5 = ""
ComboBox6 = ""
TextBox26 = ""
TextBox27 = ""
TextBox28 = ""
TextBox29 = ""
TextBox30 = ""
TextBox31 = ""
TextBox32 = ""
TextBox33 = ""
TextBox34 = ""
TextBox35 = ""
TextBox51 = ""
TextBox52 = ""
TextBox53 = ""
TextBox54 = ""
TextBox55 = ""
TextBox61 = ""
TextBox62 = ""
TextBox63 = ""
TextBox64 = ""
TextBox65 = ""
TextBox71 = ""
TextBox72 = ""
TextBox73 = ""
TextBox74 = ""
TextBox75 = ""
TextBox76 = ""
CheckBox6 = ""
CheckBox1 = ""
CheckBox2 = ""
CheckBox3 = ""
CheckBox4 = ""
CheckBox5 = ""
CheckBox7 = ""
ComboBox3 = ""
ComboBox4 = ""
ComboBox5 = ""
ComboBox6 = ""
ComboBox7 = ""
ComboBox8 = ""
ComboBox9 = ""
ComboBox10 = ""
ComboBox11 = ""
ComboBox12 = ""
ComboBox13 = ""
ComboBox14 = ""
ComboBox15 = ""
ComboBox16 = ""
ComboBox17 = ""
ComboBox18 = ""
ComboBox19 = ""
ComboBox20 = ""
ComboBox21 = ""
ComboBox22 = ""
ComboBox23 = ""
ComboBox24 = ""
ComboBox25 = ""
ComboBox26 = ""
End If
End Sub
Private Sub CommandButton1_Click()
If ComboBox1.ListIndex > 0 Then
Rows(ComboBox1.ListIndex + 1).Delete
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
TextBox12 = ""
TextBox13 = ""
TextBox14 = ""
TextBox15 = ""
TextBox16 = ""
TextBox17 = ""
TextBox18 = ""
TextBox19 = ""
TextBox20 = ""
TextBox21 = ""
TextBox22 = ""
TextBox23 = ""
TextBox23 = ""
TextBox24 = ""
TextBox25 = ""
TextBox26 = ""
TextBox27 = ""
TextBox28 = ""
TextBox29 = ""
TextBox30 = ""
TextBox31 = ""
TextBox32 = ""
TextBox33 = ""
TextBox34 = ""
TextBox35 = ""
TextBox41 = ""
TextBox42 = ""
TextBox43 = ""
TextBox44 = ""
TextBox45 = ""
TextBox46 = ""
TextBox47 = ""
TextBox48 = ""
TextBox49 = ""
TextBox50 = ""
TextBox51 = ""
TextBox52 = ""
TextBox53 = ""
TextBox54 = ""
TextBox55 = ""
TextBox61 = ""
TextBox62 = ""
TextBox63 = ""
TextBox64 = ""
TextBox65 = ""
TextBox71 = ""
TextBox72 = ""
TextBox73 = ""
TextBox74 = ""
TextBox75 = ""
TextBox76 = ""
CheckBox6 = ""
CheckBox1 = ""
CheckBox2 = ""
CheckBox3 = ""
CheckBox4 = ""
CheckBox5 = ""
CheckBox7 = ""
ComboBox2 = ""
ComboBox3 = ""
ComboBox4 = ""
ComboBox5 = ""
ComboBox6 = ""
ComboBox7 = ""
ComboBox8 = ""
ComboBox9 = ""
ComboBox10 = ""
ComboBox11 = ""
ComboBox12 = ""
ComboBox13 = ""
ComboBox14 = ""
ComboBox15 = ""
ComboBox16 = ""
ComboBox17 = ""
ComboBox18 = ""
ComboBox19 = ""
ComboBox20 = ""
ComboBox21 = ""
ComboBox22 = ""
ComboBox23 = ""
ComboBox24 = ""
ComboBox25 = ""
ComboBox26 = ""
UserForm_Initialize
End If
End Sub
End Sub
Private Sub CommandButton2_Click() 'Übergabe in Excel wenn CommandButton gedrückt
Dim xZeile As Long
Dim oneRange As Range
Dim aCell As Range
Dim arrTmp(1 To 1, 1 To 78)
If TextBox1 = "" Then Exit Sub
If ComboBox1.ListIndex = 0 Then
xZeile = [A65536].End(xlUp).Row + 1
Else
xZeile = ComboBox1.ListIndex + 1
End If
arrTmp(1, 1) = TextBox1
arrTmp(1, 2) = TextBox2
arrTmp(1, 3) = TextBox3
arrTmp(1, 4) = TextBox4
arrTmp(1, 5) = TextBox5
arrTmp(1, 6) = TextBox76
arrTmp(1, 7) = CDate(TextBox20)
arrTmp(1, 8) = CDate(TextBox6)
arrTmp(1, 11) = ComboBox2
arrTmp(1, 12) = TextBox26
arrTmp(1, 13) = ComboBox3
arrTmp(1, 14) = TextBox28
arrTmp(1, 15) = ComboBox4
arrTmp(1, 16) = TextBox30
arrTmp(1, 17) = ComboBox5
arrTmp(1, 18) = TextBox27
arrTmp(1, 19) = ComboBox6
arrTmp(1, 20) = TextBox29
arrTmp(1, 21) = CDate(TextBox8)
arrTmp(1, 22) = CDate(TextBox9)
arrTmp(1, 25) = ComboBox7
arrTmp(1, 26) = TextBox35
arrTmp(1, 27) = ComboBox8
arrTmp(1, 28) = TextBox34
arrTmp(1, 29) = ComboBox9
arrTmp(1, 30) = TextBox33
arrTmp(1, 31) = ComboBox10
arrTmp(1, 32) = TextBox32
arrTmp(1, 33) = ComboBox11
arrTmp(1, 34) = TextBox31
arrTmp(1, 35) = CDate(TextBox11)
arrTmp(1, 36) = CDate(TextBox12)
arrTmp(1, 39) = ComboBox13
arrTmp(1, 40) = TextBox61
arrTmp(1, 41) = ComboBox12
arrTmp(1, 42) = TextBox62
arrTmp(1, 43) = ComboBox14
arrTmp(1, 44) = TextBox63
arrTmp(1, 45) = ComboBox15
arrTmp(1, 46) = TextBox64
arrTmp(1, 47) = ComboBox16
arrTmp(1, 48) = TextBox65
arrTmp(1, 49) = CDate(TextBox14)
arrTmp(1, 50) = CDate(TextBox15)
arrTmp(1, 53) = ComboBox17
arrTmp(1, 54) = TextBox55
arrTmp(1, 55) = ComboBox18
arrTmp(1, 56) = TextBox54
arrTmp(1, 57) = ComboBox19
arrTmp(1, 58) = TextBox53
arrTmp(1, 59) = ComboBox20
arrTmp(1, 60) = TextBox52
arrTmp(1, 61) = ComboBox21
arrTmp(1, 62) = TextBox51
arrTmp(1, 63) = CDate(TextBox17)
arrTmp(1, 64) = CDate(TextBox18)
arrTmp(1, 67) = ComboBox22
arrTmp(1, 68) = TextBox71
arrTmp(1, 69) = ComboBox23
arrTmp(1, 70) = TextBox75
arrTmp(1, 71) = ComboBox24
arrTmp(1, 72) = TextBox74
arrTmp(1, 73) = ComboBox25
arrTmp(1, 74) = TextBox73
arrTmp(1, 75) = ComboBox26
arrTmp(1, 76) = TextBox72
If Gate_Planning.CheckBox1.Value = True Then 'Closed Gate 1
arrTmp(1, 9) = "Y"
Else
arrTmp(1, 9) = ""
End If
If Gate_Planning.CheckBox2.Value = True Then 'Closed Gate 2
arrTmp(1, 23) = "Y"
Else
arrTmp(1, 23) = ""
End If
If Gate_Planning.CheckBox3.Value = True Then 'Closed Gate 3
arrTmp(1, 37) = "Y"
Else
arrTmp(1, 37) = ""
End If
If Gate_Planning.CheckBox4.Value = True Then 'Closed Gate 4
arrTmp(1, 51) = "Y"
Else
arrTmp(1, 51) = ""
End If
If Gate_Planning.CheckBox5.Value = True Then ' Closed Gate 5
arrTmp(1, 65) = "Y"
Else
arrTmp(1, 65) = ""
End If
If Gate_Planning.CheckBox6.Value = True Then ' Closed Projects
arrTmp(1, 77) = "x"
Else
arrTmp(1, 77) = ""
End If
If Gate_Planning.CheckBox7.Value = True Then ' Delete Projects
arrTmp(1, 78) = "d"
Else
arrTmp(1, 78) = ""
End If
Cells(xZeile, 1).Resize(, 78) = arrTmp
Set oneRange = Range("A3:BZ250")
Set aCell = Range("A3")
oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes
UserForm_Initialize
End Sub
Private Sub CommandButton3_Click() ' Cancel Button
Unload Me
End Sub
Private Sub UserForm_Initialize() ' Formatierung Dropdown Menü
Dim aRow, i As Long
Application.EnableEvents = False
ComboBox1.Clear
aRow = [A65536].End(xlUp).Row
ComboBox1.AddItem "add new project"
For i = 2 To aRow
ComboBox1.AddItem Cells(i, 1) & ", " & Cells(i, 2)
Next i
ComboBox1.ListIndex = 0
Application.EnableEvents = True
End Sub