Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
26.04.2024 16:05:28
PivotChart-Stapeldiagramm erstellen
26.04.2024 15:34:42
Überlagernde Textbox-Ansicht
26.04.2024 15:27:20
Excel öffnet nach Auswahl einer Excel diese leer
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1388to1392
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1388to1392
1388to1392
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Übergabe UserForum in Excelsheet viel zu langsam

Übergabe UserForum in Excelsheet viel zu langsam
10.11.2014 09:40:25
Jens
Guten Morgen zusammen,
ich hab ein kleines Problem mit der Geschwindigkeit meines UserForum. Dort ist eine Combobox vorhanden, wo der User ein neues oder ein bereits bestehendes Projekt auswählen kann. Wird ein Bestehendes Projekt ausgewählt werden die Daten automatisch in den Textboxen angezeigt. Wird ein neues Projekt angelegt sollen diese Daten nach auslösen des CommandButton2 in die Excel Tabelle übertragen werden. Leider dauert dieser Vorgang 30 sek.! Im Anhang findet ihr meinen Code wäre top wenn mir jemand helfen könnte, bin absoluter VBA-Neuling. Die Datei kann ich leider nicht hochladen da diese zu groß ist!
Gruß Jens
Option Explicit
Private Sub ComboBox1_Click()' Anzeige nach auswahl der Combobox
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)
TextBox20 = Cells(ComboBox1.ListIndex + 1, 6)
TextBox6 = Cells(ComboBox1.ListIndex + 1, 7)
CheckBox1 = Cells(ComboBox1.ListIndex + 1, 8)
TextBox21 = Cells(ComboBox1.ListIndex + 1, 10)
TextBox26 = Cells(ComboBox1.ListIndex + 1, 11)
TextBox23 = Cells(ComboBox1.ListIndex + 1, 12)
TextBox28 = Cells(ComboBox1.ListIndex + 1, 13)
TextBox22 = Cells(ComboBox1.ListIndex + 1, 14)
TextBox30 = Cells(ComboBox1.ListIndex + 1, 15)
TextBox24 = Cells(ComboBox1.ListIndex + 1, 16)
TextBox27 = Cells(ComboBox1.ListIndex + 1, 17)
TextBox25 = Cells(ComboBox1.ListIndex + 1, 18)
TextBox29 = Cells(ComboBox1.ListIndex + 1, 19)
TextBox8 = Cells(ComboBox1.ListIndex + 1, 20)
TextBox9 = Cells(ComboBox1.ListIndex + 1, 21)
CheckBox2 = Cells(ComboBox1.ListIndex + 1, 22)
TextBox40 = Cells(ComboBox1.ListIndex + 1, 24)
TextBox35 = Cells(ComboBox1.ListIndex + 1, 25)
TextBox39 = Cells(ComboBox1.ListIndex + 1, 26)
TextBox34 = Cells(ComboBox1.ListIndex + 1, 27)
TextBox38 = Cells(ComboBox1.ListIndex + 1, 28)
TextBox33 = Cells(ComboBox1.ListIndex + 1, 29)
TextBox37 = Cells(ComboBox1.ListIndex + 1, 30)
TextBox32 = Cells(ComboBox1.ListIndex + 1, 31)
TextBox36 = Cells(ComboBox1.ListIndex + 1, 32)
TextBox31 = Cells(ComboBox1.ListIndex + 1, 33)
TextBox11 = Cells(ComboBox1.ListIndex + 1, 34)
TextBox12 = Cells(ComboBox1.ListIndex + 1, 35)
CheckBox3 = Cells(ComboBox1.ListIndex + 1, 36)
TextBox70 = Cells(ComboBox1.ListIndex + 1, 38)
TextBox61 = Cells(ComboBox1.ListIndex + 1, 39)
TextBox69 = Cells(ComboBox1.ListIndex + 1, 40)
TextBox62 = Cells(ComboBox1.ListIndex + 1, 41)
TextBox68 = Cells(ComboBox1.ListIndex + 1, 42)
TextBox63 = Cells(ComboBox1.ListIndex + 1, 43)
TextBox67 = Cells(ComboBox1.ListIndex + 1, 44)
TextBox66 = Cells(ComboBox1.ListIndex + 1, 45)
TextBox64 = Cells(ComboBox1.ListIndex + 1, 46)
TextBox65 = Cells(ComboBox1.ListIndex + 1, 47)
TextBox14 = Cells(ComboBox1.ListIndex + 1, 48)
TextBox15 = Cells(ComboBox1.ListIndex + 1, 49)
CheckBox4 = Cells(ComboBox1.ListIndex + 1, 50)
TextBox60 = Cells(ComboBox1.ListIndex + 1, 52)
TextBox55 = Cells(ComboBox1.ListIndex + 1, 53)
TextBox59 = Cells(ComboBox1.ListIndex + 1, 54)
TextBox54 = Cells(ComboBox1.ListIndex + 1, 55)
TextBox58 = Cells(ComboBox1.ListIndex + 1, 56)
TextBox53 = Cells(ComboBox1.ListIndex + 1, 57)
TextBox57 = Cells(ComboBox1.ListIndex + 1, 58)
TextBox52 = Cells(ComboBox1.ListIndex + 1, 59)
TextBox56 = Cells(ComboBox1.ListIndex + 1, 60)
TextBox51 = Cells(ComboBox1.ListIndex + 1, 61)
TextBox17 = Cells(ComboBox1.ListIndex + 1, 62)
TextBox18 = Cells(ComboBox1.ListIndex + 1, 63)
CheckBox5 = Cells(ComboBox1.ListIndex + 1, 64)
TextBox80 = Cells(ComboBox1.ListIndex + 1, 66)
TextBox71 = Cells(ComboBox1.ListIndex + 1, 67)
TextBox79 = Cells(ComboBox1.ListIndex + 1, 68)
TextBox75 = Cells(ComboBox1.ListIndex + 1, 69)
TextBox78 = Cells(ComboBox1.ListIndex + 1, 70)
TextBox74 = Cells(ComboBox1.ListIndex + 1, 71)
TextBox77 = Cells(ComboBox1.ListIndex + 1, 72)
TextBox73 = Cells(ComboBox1.ListIndex + 1, 73)
TextBox76 = Cells(ComboBox1.ListIndex + 1, 74)
TextBox72 = Cells(ComboBox1.ListIndex + 1, 75)
CheckBox6 = Cells(ComboBox1.ListIndex + 1, 76)
CheckBox7 = Cells(ComboBox1.ListIndex + 1, 77)
Else                        '
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox8 = ""
TextBox9 = ""
TextBox11 = ""
TextBox12 = ""
TextBox14 = ""
TextBox15 = ""
TextBox17 = ""
TextBox18 = ""
TextBox20 = ""
TextBox21 = ""
TextBox22 = ""
TextBox23 = ""
TextBox23 = ""
TextBox24 = ""
TextBox25 = ""
TextBox26 = ""
TextBox27 = ""
TextBox28 = ""
TextBox29 = ""
TextBox30 = ""
TextBox31 = ""
TextBox32 = ""
TextBox33 = ""
TextBox34 = ""
TextBox35 = ""
TextBox36 = ""
TextBox37 = ""
TextBox38 = ""
TextBox39 = ""
TextBox40 = ""
TextBox51 = ""
TextBox52 = ""
TextBox53 = ""
TextBox54 = ""
TextBox55 = ""
TextBox56 = ""
TextBox57 = ""
TextBox58 = ""
TextBox59 = ""
TextBox60 = ""
TextBox61 = ""
TextBox62 = ""
TextBox63 = ""
TextBox64 = ""
TextBox65 = ""
TextBox66 = ""
TextBox67 = ""
TextBox68 = ""
TextBox69 = ""
TextBox70 = ""
TextBox71 = ""
TextBox72 = ""
TextBox73 = ""
TextBox74 = ""
TextBox75 = ""
TextBox76 = ""
TextBox77 = ""
TextBox78 = ""
TextBox79 = ""
TextBox80 = ""
CheckBox6 = ""
CheckBox1 = ""
CheckBox2 = ""
CheckBox3 = ""
CheckBox4 = ""
CheckBox5 = ""
CheckBox7 = ""
End If
End Sub


Private Sub CommandButton1_Click()' UserForum leeren wenn dies geschlossen wird
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 = ""
TextBox36 = ""
TextBox37 = ""
TextBox38 = ""
TextBox39 = ""
TextBox40 = ""
TextBox41 = ""
TextBox42 = ""
TextBox43 = ""
TextBox44 = ""
TextBox45 = ""
TextBox46 = ""
TextBox47 = ""
TextBox48 = ""
TextBox49 = ""
TextBox50 = ""
TextBox51 = ""
TextBox52 = ""
TextBox53 = ""
TextBox54 = ""
TextBox55 = ""
TextBox56 = ""
TextBox57 = ""
TextBox58 = ""
TextBox59 = ""
TextBox60 = ""
TextBox61 = ""
TextBox62 = ""
TextBox63 = ""
TextBox64 = ""
TextBox65 = ""
TextBox66 = ""
TextBox67 = ""
TextBox68 = ""
TextBox69 = ""
TextBox70 = ""
TextBox71 = ""
TextBox72 = ""
TextBox73 = ""
TextBox74 = ""
TextBox75 = ""
TextBox76 = ""
TextBox77 = ""
TextBox78 = ""
TextBox79 = ""
TextBox80 = ""
CheckBox6 = ""
CheckBox1 = ""
CheckBox2 = ""
CheckBox3 = ""
CheckBox4 = ""
CheckBox5 = ""
CheckBox7 = ""
UserForm_Initialize
End If
End Sub


Private Sub CommandButton2_Click()'Übergabe in Excel wenn CommandButton gedrückt
Dim xZeile As Long
If TextBox1 = "" Then Exit Sub
If ComboBox1.ListIndex = 0 Then
xZeile = [A65536].End(xlUp).Row + 1
Else
xZeile = ComboBox1.ListIndex + 1
End If
Cells(xZeile, 1) = TextBox1
Cells(xZeile, 2) = TextBox2
Cells(xZeile, 3) = TextBox3
Cells(xZeile, 4) = TextBox4
Cells(xZeile, 5) = TextBox5
Cells(xZeile, 6) = CDate(TextBox20)
Cells(xZeile, 7) = CDate(TextBox6)
Cells(xZeile, 10) = TextBox21
Cells(xZeile, 11) = TextBox26
Cells(xZeile, 12) = TextBox23
Cells(xZeile, 13) = TextBox28
Cells(xZeile, 14) = TextBox22
Cells(xZeile, 15) = TextBox30
Cells(xZeile, 16) = TextBox24
Cells(xZeile, 17) = TextBox27
Cells(xZeile, 18) = TextBox25
Cells(xZeile, 19) = TextBox29
Cells(xZeile, 20) = CDate(TextBox8)
Cells(xZeile, 21) = CDate(TextBox9)
Cells(xZeile, 24) = TextBox40
Cells(xZeile, 25) = TextBox35
Cells(xZeile, 26) = TextBox39
Cells(xZeile, 27) = TextBox34
Cells(xZeile, 28) = TextBox38
Cells(xZeile, 29) = TextBox33
Cells(xZeile, 30) = TextBox37
Cells(xZeile, 31) = TextBox32
Cells(xZeile, 32) = TextBox36
Cells(xZeile, 33) = TextBox31
Cells(xZeile, 34) = CDate(TextBox11)
Cells(xZeile, 35) = CDate(TextBox12)
Cells(xZeile, 38) = TextBox70
Cells(xZeile, 39) = TextBox61
Cells(xZeile, 40) = TextBox69
Cells(xZeile, 41) = TextBox62
Cells(xZeile, 42) = TextBox68
Cells(xZeile, 43) = TextBox63
Cells(xZeile, 44) = TextBox67
Cells(xZeile, 45) = TextBox64
Cells(xZeile, 46) = TextBox66
Cells(xZeile, 47) = TextBox65
Cells(xZeile, 48) = CDate(TextBox14)
Cells(xZeile, 49) = CDate(TextBox15)
Cells(xZeile, 52) = TextBox60
Cells(xZeile, 53) = TextBox55
Cells(xZeile, 54) = TextBox59
Cells(xZeile, 55) = TextBox54
Cells(xZeile, 56) = TextBox58
Cells(xZeile, 57) = TextBox53
Cells(xZeile, 58) = TextBox57
Cells(xZeile, 59) = TextBox52
Cells(xZeile, 60) = TextBox56
Cells(xZeile, 61) = TextBox51
Cells(xZeile, 62) = CDate(TextBox17)
Cells(xZeile, 63) = CDate(TextBox18)
Cells(xZeile, 66) = TextBox80
Cells(xZeile, 67) = TextBox71
Cells(xZeile, 68) = TextBox79
Cells(xZeile, 69) = TextBox75
Cells(xZeile, 70) = TextBox78
Cells(xZeile, 71) = TextBox74
Cells(xZeile, 72) = TextBox77
Cells(xZeile, 73) = TextBox73
Cells(xZeile, 74) = TextBox76
Cells(xZeile, 75) = TextBox72
If Gate_Planning.CheckBox1.Value = True Then
Cells(xZeile, 8) = "Y"
End If
If Gate_Planning.CheckBox1.Value = False Then
Cells(xZeile, 8) = "  "
End If
If Gate_Planning.CheckBox2.Value = True Then
Cells(xZeile, 22) = "Y"
End If
If Gate_Planning.CheckBox2.Value = False Then
Cells(xZeile, 22) = " "
End If
If Gate_Planning.CheckBox3.Value = True Then
Cells(xZeile, 36) = "Y"
End If
If Gate_Planning.CheckBox3.Value = False Then
Cells(xZeile, 36) = "  "
End If
If Gate_Planning.CheckBox4.Value = True Then
Cells(xZeile, 50) = "Y"
End If
If Gate_Planning.CheckBox4.Value = False Then
Cells(xZeile, 50) = "  "
End If
If Gate_Planning.CheckBox5.Value = True Then
Cells(xZeile, 64) = "Y"
End If
If Gate_Planning.CheckBox5.Value = False Then
Cells(xZeile, 64) = " "
End If
If Gate_Planning.CheckBox6.Value = True Then
Cells(xZeile, 76) = "x"
End If
If Gate_Planning.CheckBox6.Value = False Then
Cells(xZeile, 76) = " "
End If
If Gate_Planning.CheckBox7.Value = True Then
Cells(xZeile, 77) = "d"
End If
If Gate_Planning.CheckBox7.Value = False Then
Cells(xZeile, 77) = " "
End If
Dim oneRange As Range
Dim aCell As Range
Set oneRange = Range("A3:BY250")
Set aCell = Range("A3")
oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes
UserForm_Initialize
End Sub


Private Sub CommandButton3_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()' Anzeige der ersten beiden Spalten in der Combobox
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

2
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Übergabe UserForum in Excelsheet viel zu langsam
10.11.2014 10:22:53
Rudi
Hallo,
sollte schneller sein
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 77)
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) = CDate(TextBox20)
arrTmp(1, 7) = CDate(TextBox6)
arrTmp(1, 10) = TextBox21
arrTmp(1, 11) = TextBox26
arrTmp(1, 12) = TextBox23
arrTmp(1, 13) = TextBox28
arrTmp(1, 14) = TextBox22
arrTmp(1, 15) = TextBox30
arrTmp(1, 16) = TextBox24
arrTmp(1, 17) = TextBox27
arrTmp(1, 18) = TextBox25
arrTmp(1, 19) = TextBox29
arrTmp(1, 20) = CDate(TextBox8)
arrTmp(1, 21) = CDate(TextBox9)
arrTmp(1, 24) = TextBox40
arrTmp(1, 25) = TextBox35
arrTmp(1, 26) = TextBox39
arrTmp(1, 27) = TextBox34
arrTmp(1, 28) = TextBox38
arrTmp(1, 29) = TextBox33
arrTmp(1, 30) = TextBox37
arrTmp(1, 31) = TextBox32
arrTmp(1, 32) = TextBox36
arrTmp(1, 33) = TextBox31
arrTmp(1, 34) = CDate(TextBox11)
arrTmp(1, 35) = CDate(TextBox12)
arrTmp(1, 38) = TextBox70
arrTmp(1, 39) = TextBox61
arrTmp(1, 40) = TextBox69
arrTmp(1, 41) = TextBox62
arrTmp(1, 42) = TextBox68
arrTmp(1, 43) = TextBox63
arrTmp(1, 44) = TextBox67
arrTmp(1, 45) = TextBox64
arrTmp(1, 46) = TextBox66
arrTmp(1, 47) = TextBox65
arrTmp(1, 48) = CDate(TextBox14)
arrTmp(1, 49) = CDate(TextBox15)
arrTmp(1, 52) = TextBox60
arrTmp(1, 53) = TextBox55
arrTmp(1, 54) = TextBox59
arrTmp(1, 55) = TextBox54
arrTmp(1, 56) = TextBox58
arrTmp(1, 57) = TextBox53
arrTmp(1, 58) = TextBox57
arrTmp(1, 59) = TextBox52
arrTmp(1, 60) = TextBox56
arrTmp(1, 61) = TextBox51
arrTmp(1, 62) = CDate(TextBox17)
arrTmp(1, 63) = CDate(TextBox18)
arrTmp(1, 66) = TextBox80
arrTmp(1, 67) = TextBox71
arrTmp(1, 68) = TextBox79
arrTmp(1, 69) = TextBox75
arrTmp(1, 70) = TextBox78
arrTmp(1, 71) = TextBox74
arrTmp(1, 72) = TextBox77
arrTmp(1, 73) = TextBox73
arrTmp(1, 74) = TextBox76
arrTmp(1, 75) = TextBox72
If Gate_Planning.CheckBox1.Value = True Then
arrTmp(1, 8) = "Y"
Else
arrTmp(1, 8) = ""
End If
If Gate_Planning.CheckBox2.Value = True Then
arrTmp(1, 22) = "Y"
Else
arrTmp(1, 22) = ""
End If
If Gate_Planning.CheckBox3.Value = True Then
arrTmp(1, 36) = "Y"
Else
arrTmp(1, 36) = ""
End If
If Gate_Planning.CheckBox4.Value = True Then
arrTmp(1, 50) = "Y"
Else
arrTmp(1, 50) = ""
End If
If Gate_Planning.CheckBox5.Value = True Then
arrTmp(1, 64) = "Y"
Else
arrTmp(1, 64) = ""
End If
If Gate_Planning.CheckBox6.Value = True Then
arrTmp(1, 76) = "x"
Else
arrTmp(1, 76) = ""
End If
If Gate_Planning.CheckBox7.Value = True Then
arrTmp(1, 77) = "d"
Else
arrTmp(1, 77) = ""
End If
Cells(xZeile, 1).Resize(, 77) = arrTmp
Set oneRange = Range("A3:BY250")
Set aCell = Range("A3")
oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes
UserForm_Initialize
End Sub
Gruß
Rudi

Anzeige
Übergabe UserForum in Excelsheet viel zu langsam
10.11.2014 15:14:17
Jens
Vielen Dank Rudi! Es läuft sehr schnell!

Links zu Excel-Dialogen

CommandButton TextBox

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige