Hallo Andreas, Hallo Ihr Anderen,
Den einzigen offensichtlichen Fehler, den ich gesehen habe, ist im Modul Privat Sub ComboBox1_Change....da müsste es heißen....For iCounter 1 To 13 (nicht 10), jedoch nimmt er meine Änderungen im Code nicht an und verweist immer auf den Automatisierungsfehler?.....Debuggen bringt auch nur den gleichen Hinweis.....Ich habe noch ein Backup, wo diese UF noch nicht drin ist, und die läuft, also denke ich, daß der Haken im nachstehenden Code zu suchen sei.....und meine Denke geht in die Richtung, daß das Spreadsheet irgendwas verursacht?...
Private Sub ComboBox1_Change()
Dim iCounter As Integer, iRow As Integer
Dim sSelect As String
With ComboBox1
If .ListIndex >= 0 Then
sSelect = .List(.ListIndex, 0) & " " & .List(.ListIndex, 1) & ", " & .List(.ListIndex, 2) & ", " & .List(.ListIndex, 3) & " mit " & .List(.ListIndex, 6)
iRow = .ListIndex
ComboBox1.Text = sSelect
End If
For iCounter = 1 To 10
Controls("TextBox" & iCounter).Text = .List(iRow, iCounter - 1)
Next iCounter
End With
End Sub
Private Sub ComboBox2_Change()
Dim iCounter As Integer, iRow As Integer
Dim sSelect As String
With ComboBox2
If .ListIndex >= 0 Then
sSelect = .List(.ListIndex, 0) & " " & .List(.ListIndex, 1) & ", " & .List(.ListIndex, 2) & ", " & .List(.ListIndex, 3) & " mit " & .List(.ListIndex, 4)
iRow = .ListIndex
ComboBox2.Text = sSelect
End If
For iCounter = 1 To 11
Controls("TxtBox" & iCounter).Text = .List(iRow, iCounter - 1)
Next iCounter
End With
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim z As Integer, myArr As Variant
Application.ScreenUpdating = False
Sheets("WKHindernislauf").Activate
z = Range("A1000").End(xlUp).Row
Cells(z + 1, 1) = TextBox1.Value
Cells(z + 1, 2) = TextBox2.Value
Cells(z + 1, 3) = TextBox3.Value
Cells(z + 1, 4) = TextBox4.Value
Cells(z + 1, 5) = TextBox7.Value
Cells(z + 1, 6) = TextBox10.Value
Cells(z + 1, 7) = TextBox12.Value
Cells(z + 1, 8) = TextBox13.Value
myArr = Sheets("Hindernislauf").Range("A2:L1000")
ComboBox1 = ""
ComboBox1.List = myArr
For iCounter = 1 To 13
Controls("TextBox" & iCounter) = ""
Next iCounter
Dim i%, j%
For i = 1 To 100
For j = 1 To 100
Me.Spreadsheet1.Cells(j, i).Value = Worksheets("WKHindernislauf").Cells(j, i).Value
Next j
Next i
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton3_Click()
Dim z As Integer, myArr As Variant
Application.ScreenUpdating = False
Sheets("WKHindernislauf").Activate
z = Range("A15:J1000").Find(What:=TxtBox1).Row
Cells(z, 1) = TxtBox1.Value
Cells(z, 2) = TxtBox2.Value
Cells(z, 3) = TxtBox3.Value
Cells(z, 4) = TxtBox4.Value
Cells(z, 5) = TxtBox5.Value
Cells(z, 6) = TxtBox6.Value
Cells(z, 7) = TxtBox7.Value
Cells(z, 8) = TxtBox8.Value
Cells(z, 10) = TxtBox10.Value
Cells(z, 11) = TxtBox11.Value
myArr = Sheets("WKHindernislauf").Range("A15:L1000")
ComboBox2 = ""
ComboBox2.List = myArr
For iCounter = 1 To 11
Controls("TxtBox" & iCounter) = ""
Next iCounter
Dim i%, j%
For i = 1 To 100
For j = 1 To 100
Me.Spreadsheet1.Cells(j, i).Value = Worksheets("WKHindernislauf").Cells(j, i).Value
Next j
Next i
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Initialize()
ComboBox1.Visible = True
ComboBox2.Visible = False
ComboBox1.List = Range("Hindernislauf!A2:L1000").CurrentRegion.Value
ComboBox1.ListIndex = iCounter - 1
ComboBox2.List = Range("WKHindernislauf!A15:L1000").CurrentRegion.Value
ComboBox2.ListIndex = iCounter - 1
TxtBox10.Enabled = False
TxtBox10.BackColor = &H8000000B
TxtBox11.Enabled = False
TxtBox11.BackColor = &H8000000B
CommandButton3.Enabled = False
Frame2.SpecialEffect = fmSpecialEffectEtched
Frame1.SpecialEffect = fmSpecialEffectRaised
With Spreadsheet1
.Range("A14:L14").Font.Size = 8
.Range("A14:L14").Font.Bold = True
.Range("A1:L1").Font.Color = &HC00000
.Range("A2:L1000").Font.Size = 8
.Range("A2:L1000").Font.Color = &HC00000
End With
Dim i%, j%
For i = 1 To 100
For j = 10 To 100
Me.Spreadsheet1.Cells(j, i).Value = Worksheets("WKHindernislauf").Cells(j, i).Value
Next j
Next i
End Sub
Private Sub ToggleButton1_Click()
If ToggleButton1 Then
ComboBox1.Visible = False
ComboBox2.Visible = True
TextBox12.Enabled = False
TextBox12.BackColor = &H8000000B
TextBox13.Enabled = False
TextBox13.BackColor = &H8000000B
CommandButton2.Enabled = False
Frame1.SpecialEffect = fmSpecialEffectEtched
TxtBox10.Enabled = True
TxtBox10.BackColor = &HFFFFFF
TxtBox11.Enabled = True
TxtBox11.BackColor = &HFFFFFF
CommandButton3.Enabled = True
Frame2.SpecialEffect = fmSpecialEffectRaised
Else
ComboBox1.Visible = True
ComboBox2.Visible = False
TextBox12.Enabled = True
TextBox12.BackColor = &HFFFFFF
TextBox13.Enabled = True
TextBox13.BackColor = &HFFFFFF
CommandButton2.Enabled = True
Frame1.SpecialEffect = fmSpecialEffectRaised
TxtBox10.Enabled = False
TxtBox10.BackColor = &H8000000B
TxtBox11.Enabled = False
TxtBox11.BackColor = &H8000000B
CommandButton3.Enabled = False
Frame2.SpecialEffect = fmSpecialEffectEtched
End If
End Sub
Vielen Dank für Eure Unterstützung
Peter S