Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema CheckBox
BildScreenshot zu CheckBox CheckBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema CommandButton
BildScreenshot zu CommandButton CommandButton-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema TextBox
BildScreenshot zu TextBox TextBox-Seite mit Beispielarbeitsmappe aufrufen

Übergabe UserForum in Excelsheet viel zu langsam

Betrifft: Übergabe UserForum in Excelsheet viel zu langsam von: Jens
Geschrieben am: 10.11.2014 09:40:25

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

  

Betrifft: AW: Übergabe UserForum in Excelsheet viel zu langsam von: Rudi Maintaire
Geschrieben am: 10.11.2014 10:22:53

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


  

Betrifft: Übergabe UserForum in Excelsheet viel zu langsam von: Jens
Geschrieben am: 10.11.2014 15:14:17

Vielen Dank Rudi! Es läuft sehr schnell!


 

Beiträge aus den Excel-Beispielen zum Thema "Übergabe UserForum in Excelsheet viel zu langsam"