Fehler in Code von UserForm
15.04.2007 14:44:26
Andreas
Hallo Excelforum!
Ich habe ein Problem, bei dem ich nicht weiterkomme.
Dank vieler Helfer aus diesem Forum (insbesondere Beni und Franz) habe ich eine Arbeitsmappe mit 2 Blättern für eine Besuchererfassung. Über ein Eingabeformular kann ich neue Einträge eingeben, Suchen, Ändern usw..
Jetzt habe ich folgendes Problem:
In einem Listenfeld (Listfeld1) werden mir alle Einträge aus dem Blatt "Auswertung" angezeigt. Das funktioniert auch super.
Wenn ich eine Suche durchführen will, kann ich in der Eingabemaske im Feld "Firma" Buchstaben eingeben und auf "Suchen" klicken. Daraufhin werden alle gefundenen Einträge (aus dem Blatt "Auswertung" in ein Listenfeld geschrieben.
Allerdings sind die Felder "Stadt" (Textfeld9) und "Land" (ComboBox2) bei der Suche jedesmals vertauscht. Bei der normalen Anzeige ist es richtig herum.
Kann mir bitte Jemand helfen?
Nachfolgend habe ich mal den gesamten Code des UserForm kopiert. Leider kann ich keine Beispielmappe hochladen, da Diese zu groß ist.
Danke für die Hilfe und Mühe!!
Private Sub UserForm_Activate()
Sheets("Auswertung").Unprotect "xyz"
End Sub
Private Sub cbAlleanzeigen_Click()
ListBox2.Visible = False
End Sub
Private Sub cbEnde_Click()
Unload Me
End Sub
Private Sub cbAendern_Click()
Dim lz As Integer
Application.ScreenUpdating = False
With Sheets("Auswertung")
Set c = .Columns(1).Find(What:=TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then lz = c.Row
.Cells(lz, 2) = TextBox2 'Firma
.Cells(lz, 3) = ComboBox1 'Anrede
.Cells(lz, 4) = TextBox3 ' Anspr Name
.Cells(lz, 5) = TextBox4 ' Anpr Vorname
.Cells(lz, 6) = TextBox5 'Zusatz
.Cells(lz, 7) = TextBox6 'Postfach
.Cells(lz, 8) = TextBox7 'Adresse
.Cells(lz, 9) = TextBox8 'PLZ
.Cells(lz, 10) = TextBox9 'Stadt
.Cells(lz, 11) = ComboBox2 'Land
.Cells(lz, 12) = TextBox10 'Tel
.Cells(lz, 13) = TextBox11 'Fax
.Cells(lz, 14) = TextBox12 'Mail
If OptionButton1.Value = True Then .Cells(lz, 15) = 1 Else .Cells(lz, 15) = ""
If OptionButton2.Value = True Then .Cells(lz, 16) = 1 Else .Cells(lz, 16) = ""
If CheckBox1.Value = True Then .Cells(lz, 17) = 1 Else .Cells(lz, 17) = ""
If CheckBox2.Value = True Then .Cells(lz, 18) = 1 Else .Cells(lz, 18) = ""
If CheckBox3.Value = True Then .Cells(lz, 19) = 1 Else .Cells(lz, 19) = ""
If CheckBox4.Value = True Then .Cells(lz, 20) = 1 Else .Cells(lz, 20) = ""
If CheckBox5.Value = True Then .Cells(lz, 21) = 1 Else .Cells(lz, 21) = ""
If CheckBox6.Value = True Then .Cells(lz, 22) = 1 Else .Cells(lz, 22) = ""
If CheckBox7.Value = True Then .Cells(lz, 23) = 1 Else .Cells(lz, 23) = ""
If CheckBox8.Value = True Then .Cells(lz, 24) = 1 Else .Cells(lz, 24) = ""
If CheckBox9.Value = True Then .Cells(lz, 25) = 1 Else .Cells(lz, 25) = ""
If CheckBox10.Value = True Then .Cells(lz, 26) = 1 Else .Cells(lz, 26) = ""
If CheckBox11.Value = True Then .Cells(lz, 27) = 1 Else .Cells(lz, 27) = ""
If CheckBox12.Value = True Then .Cells(lz, 28) = 1 Else .Cells(lz, 28) = ""
If CheckBox13.Value = True Then .Cells(lz, 29) = 1 Else .Cells(lz, 29) = ""
If CheckBox14.Value = True Then .Cells(lz, 30) = 1 Else .Cells(lz, 30) = ""
If CheckBox15.Value = True Then .Cells(lz, 31) = 1 Else .Cells(lz, 31) = ""
If CheckBox16.Value = True Then .Cells(lz, 32) = 1 Else .Cells(lz, 32) = ""
End With
Application.ScreenUpdating = True
End Sub
Private Sub cbHinzufügen_Click()
Dim lz As Integer
Application.ScreenUpdating = False
With Sheets("Auswertung")
lz = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(lz, 1) = WorksheetFunction.Max(Range(.Cells(8, 1), .Cells(lz, 1))) + 1
.Cells(lz, 2) = TextBox2 'Firma
.Cells(lz, 3) = ComboBox1 'Anrede
.Cells(lz, 4) = TextBox3 ' Anspr Name
.Cells(lz, 5) = TextBox4 ' Anpr Vorname
.Cells(lz, 6) = TextBox5 'Zusatz
.Cells(lz, 7) = TextBox6 'Postfach
.Cells(lz, 8) = TextBox7 'Adresse
.Cells(lz, 9) = TextBox8 'PLZ
.Cells(lz, 10) = TextBox9 'Stadt
.Cells(lz, 11) = ComboBox2 'Land
.Cells(lz, 12) = TextBox10 'Tel
.Cells(lz, 13) = TextBox11 'Fax
.Cells(lz, 14) = TextBox12 'Mail
If OptionButton1.Value = True Then .Cells(lz, 15) = 1
If OptionButton2.Value = True Then .Cells(lz, 16) = 1
If CheckBox1.Value = True Then .Cells(lz, 17) = 1
If CheckBox2.Value = True Then .Cells(lz, 18) = 1
If CheckBox3.Value = True Then .Cells(lz, 19) = 1
If CheckBox4.Value = True Then .Cells(lz, 20) = 1
If CheckBox5.Value = True Then .Cells(lz, 21) = 1
If CheckBox6.Value = True Then .Cells(lz, 22) = 1
If CheckBox7.Value = True Then .Cells(lz, 23) = 1
If CheckBox8.Value = True Then .Cells(lz, 24) = 1
If CheckBox9.Value = True Then .Cells(lz, 25) = 1
If CheckBox10.Value = True Then .Cells(lz, 26) = 1
If CheckBox11.Value = True Then .Cells(lz, 27) = 1
If CheckBox12.Value = True Then .Cells(lz, 28) = 1
If CheckBox13.Value = True Then .Cells(lz, 29) = 1
If CheckBox14.Value = True Then .Cells(lz, 30) = 1
If CheckBox15.Value = True Then .Cells(lz, 31) = 1
If CheckBox16.Value = True Then .Cells(lz, 32) = 1
lz = .Cells(Rows.Count, 1).End(xlUp).Row
ListBox1.RowSource = "Auswertung!a8:af" & lz
Dim Ob As MSForms.Control
For Each Ob In UserForm1.Controls
If TypeOf Ob Is MSForms.TextBox Then Ob.Value = ""
If TypeOf Ob Is MSForms.ComboBox Then Ob.Value = ""
If TypeOf Ob Is MSForms.CheckBox Then Ob.Value = False
Next Ob
End With
Application.ScreenUpdating = True
End Sub
Private Sub cbLöschen_Click()
Dim Mldg, Stil, Titel, Antwort
Mldg = "Datensatz löschen ?"
Stil = vbYesNo + vbCritical + vbDefaultButton2
Titel = "Datensatz löschen"
Antwort = MsgBox(Mldg, Stil, Titel)
If Antwort = vbNo Then Exit Sub
With Sheets("Auswertung")
Set c = .Columns(1).Find(What:=TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then .Rows(c.Row).Delete Shift:=xlUp
lz = .Cells(Rows.Count, 1).End(xlUp).Row
ListBox1.RowSource = "Auswertung!a8:af" & lz
Dim Ob As MSForms.Control
For Each Ob In UserForm1.Controls
If TypeOf Ob Is MSForms.TextBox Then Ob.Value = ""
If TypeOf Ob Is MSForms.ComboBox Then Ob.Value = ""
If TypeOf Ob Is MSForms.CheckBox Then Ob.Value = False
Next Ob
End With
End Sub
Private Sub cbSuchen_Click()
Dim r, z As Integer
Dim arrValues() As Variant
If Len(TextBox2) > 0 Then
ListBox2.Visible = True
ListBox2.Clear
ListBox2.Top = ListBox1.Top
ListBox2.Left = ListBox1.Left
ListBox2.Height = ListBox1.Height
ListBox2.Width = ListBox1.Width
With Sheets("Auswertung")
Wert = TextBox2
For r = 8 To .Cells(65536, 2).End(xlUp).Row
Set c = .Cells(r, 2).Find(Wert & "*", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) _
If Not c Is Nothing Then
ReDim Preserve arrValues(0 To 31, 0 To intRowU)
arrValues(0, intRowU) = .Cells(r, 1)
arrValues(1, intRowU) = .Cells(r, 2)
arrValues(2, intRowU) = .Cells(r, 3)
arrValues(3, intRowU) = .Cells(r, 4)
arrValues(4, intRowU) = .Cells(r, 5)
arrValues(5, intRowU) = .Cells(r, 6)
arrValues(6, intRowU) = .Cells(r, 7)
arrValues(7, intRowU) = .Cells(r, 8)
arrValues(8, intRowU) = .Cells(r, 9)
arrValues(9, intRowU) = .Cells(r, 10)
arrValues(10, intRowU) = .Cells(r, 11)
arrValues(11, intRowU) = .Cells(r, 12)
arrValues(12, intRowU) = .Cells(r, 13)
arrValues(13, intRowU) = .Cells(r, 14)
arrValues(14, intRowU) = .Cells(r, 15)
arrValues(15, intRowU) = .Cells(r, 16)
arrValues(16, intRowU) = .Cells(r, 17)
arrValues(17, intRowU) = .Cells(r, 18)
arrValues(18, intRowU) = .Cells(r, 19)
arrValues(19, intRowU) = .Cells(r, 20)
arrValues(20, intRowU) = .Cells(r, 21)
arrValues(21, intRowU) = .Cells(r, 22)
arrValues(22, intRowU) = .Cells(r, 23)
arrValues(23, intRowU) = .Cells(r, 24)
arrValues(24, intRowU) = .Cells(r, 25)
arrValues(25, intRowU) = .Cells(r, 26)
arrValues(26, intRowU) = .Cells(r, 27)
arrValues(27, intRowU) = .Cells(r, 28)
arrValues(28, intRowU) = .Cells(r, 29)
arrValues(29, intRowU) = .Cells(r, 30)
arrValues(30, intRowU) = .Cells(r, 31)
intRowU = intRowU + 1
End If
Next r
End With
End If
If intRowU <> 0 Then ListBox2.Column = arrValues
End Sub
Private Sub ListBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
On Error GoTo e
TextBox1 = ListBox1.List(ListBox1.ListIndex, 0) 'Nr
TextBox2 = ListBox1.List(ListBox1.ListIndex, 1) 'Firma
ComboBox1 = ListBox1.List(ListBox1.ListIndex, 2) 'Anrede
TextBox3 = ListBox1.List(ListBox1.ListIndex, 3) 'Anspr Vorname
TextBox4 = ListBox1.List(ListBox1.ListIndex, 4) 'Anspr Name
TextBox5 = ListBox1.List(ListBox1.ListIndex, 5) 'Zusatz
TextBox6 = ListBox1.List(ListBox1.ListIndex, 6) 'Postfach
TextBox7 = ListBox1.List(ListBox1.ListIndex, 7) 'Adresse
TextBox8 = ListBox1.List(ListBox1.ListIndex, 8) 'PLZ
TextBox9 = ListBox1.List(ListBox1.ListIndex, 9) 'Stadt
ComboBox2 = ListBox1.List(ListBox1.ListIndex, 10) 'Land
TextBox10 = ListBox1.List(ListBox1.ListIndex, 11) 'Tel
TextBox11 = ListBox1.List(ListBox1.ListIndex, 12) 'Fax
TextBox12 = ListBox1.List(ListBox1.ListIndex, 13) 'Mail
If Not ListBox1.List(ListBox1.ListIndex, 14) = "" Then OptionButton1.Value = True
If Not ListBox1.List(ListBox1.ListIndex, 15) = "" Then OptionButton2.Value = True
If Not ListBox1.List(ListBox1.ListIndex, 16) = "" Then CheckBox1.Value = True Else CheckBox1. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 17) = "" Then CheckBox2.Value = True Else CheckBox2. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 18) = "" Then CheckBox3.Value = True Else CheckBox3. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 18) = "" Then CheckBox4.Value = True Else CheckBox4. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 20) = "" Then CheckBox5.Value = True Else CheckBox5. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 21) = "" Then CheckBox6.Value = True Else CheckBox6. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 22) = "" Then CheckBox7.Value = True Else CheckBox7. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 23) = "" Then CheckBox8.Value = True Else CheckBox8. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 24) = "" Then CheckBox9.Value = True Else CheckBox9. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 25) = "" Then CheckBox10.Value = True Else CheckBox10. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 26) = "" Then CheckBox11.Value = True Else CheckBox11. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 27) = "" Then CheckBox12.Value = True Else CheckBox12. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 28) = "" Then CheckBox13.Value = True Else CheckBox13. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 29) = "" Then CheckBox14.Value = True Else CheckBox14. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 30) = "" Then CheckBox15.Value = True Else CheckBox15. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 31) = "" Then CheckBox16.Value = True Else CheckBox16. _
Value = False
e:
End Sub
Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
On Error GoTo e
TextBox1 = ListBox1.List(ListBox1.ListIndex, 0) 'Nr
TextBox2 = ListBox1.List(ListBox1.ListIndex, 1) 'Firma
ComboBox1 = ListBox1.List(ListBox1.ListIndex, 2) 'Anrede
TextBox3 = ListBox1.List(ListBox1.ListIndex, 3) 'Anspr Vorname
TextBox4 = ListBox1.List(ListBox1.ListIndex, 4) 'Anspr Name
TextBox5 = ListBox1.List(ListBox1.ListIndex, 5) 'Zusatz
TextBox6 = ListBox1.List(ListBox1.ListIndex, 6) 'Postfach
TextBox7 = ListBox1.List(ListBox1.ListIndex, 7) 'Adresse
TextBox8 = ListBox1.List(ListBox1.ListIndex, 8) 'PLZ
TextBox9 = ListBox1.List(ListBox1.ListIndex, 9) 'Stadt
ComboBox2 = ListBox1.List(ListBox1.ListIndex, 10) 'Land
TextBox10 = ListBox1.List(ListBox1.ListIndex, 11) 'Tel
TextBox11 = ListBox1.List(ListBox1.ListIndex, 12) 'Fax
TextBox12 = ListBox1.List(ListBox1.ListIndex, 13) 'Mail
If Not ListBox1.List(ListBox1.ListIndex, 14) = "" Then OptionButton1.Value = True
If Not ListBox1.List(ListBox1.ListIndex, 15) = "" Then OptionButton2.Value = True
If Not ListBox1.List(ListBox1.ListIndex, 16) = "" Then CheckBox1.Value = True Else CheckBox1. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 17) = "" Then CheckBox2.Value = True Else CheckBox2. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 18) = "" Then CheckBox3.Value = True Else CheckBox3. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 18) = "" Then CheckBox4.Value = True Else CheckBox4. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 20) = "" Then CheckBox5.Value = True Else CheckBox5. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 21) = "" Then CheckBox6.Value = True Else CheckBox6. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 22) = "" Then CheckBox7.Value = True Else CheckBox7. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 23) = "" Then CheckBox8.Value = True Else CheckBox8. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 24) = "" Then CheckBox9.Value = True Else CheckBox9. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 25) = "" Then CheckBox10.Value = True Else CheckBox10. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 26) = "" Then CheckBox11.Value = True Else CheckBox11. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 27) = "" Then CheckBox12.Value = True Else CheckBox12. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 28) = "" Then CheckBox13.Value = True Else CheckBox13. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 29) = "" Then CheckBox14.Value = True Else CheckBox14. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 30) = "" Then CheckBox15.Value = True Else CheckBox15. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 31) = "" Then CheckBox16.Value = True Else CheckBox16. _
Value = False
e:
End Sub
Private Sub ListBox2_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
If ListBox2.ListCount = 0 Then ListBox2.Visible = False: Exit Sub
If ListBox2.ListIndex = -1 Then Exit Sub
For i = 0 To ListBox1.ListCount - 1
Li = ListBox2.ListIndex
If CStr(ListBox1.List(i, 0)) = CStr(ListBox2.List(ListBox2.ListIndex, 0)) Then
ListBox1.SetFocus
ListBox1.Selected(i) = True
Else
ListBox1.Selected(i) = False
End If
Next i
'ListBox2.Visible = False
On Error GoTo e
TextBox1 = ListBox1.List(ListBox1.ListIndex, 0)
TextBox2 = ListBox1.List(ListBox1.ListIndex, 1)
ComboBox1 = ListBox1.List(ListBox1.ListIndex, 2)
TextBox3 = ListBox1.List(ListBox1.ListIndex, 3)
TextBox4 = ListBox1.List(ListBox1.ListIndex, 4)
TextBox5 = ListBox1.List(ListBox1.ListIndex, 5)
TextBox6 = ListBox1.List(ListBox1.ListIndex, 6)
TextBox7 = ListBox1.List(ListBox1.ListIndex, 7)
TextBox8 = ListBox1.List(ListBox1.ListIndex, 8)
ComboBox2 = ListBox1.List(ListBox1.ListIndex, 9)
TextBox9 = ListBox1.List(ListBox1.ListIndex, 10)
TextBox10 = ListBox1.List(ListBox1.ListIndex, 11)
TextBox11 = ListBox1.List(ListBox1.ListIndex, 12)
TextBox12 = ListBox1.List(ListBox1.ListIndex, 13)
If Not ListBox1.List(ListBox1.ListIndex, 14) = "" Then OptionButton1.Value = True
If Not ListBox1.List(ListBox1.ListIndex, 15) = "" Then OptionButton2.Value = True
If Not ListBox1.List(ListBox1.ListIndex, 16) = "" Then CheckBox1.Value = True Else CheckBox1. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 17) = "" Then CheckBox2.Value = True Else CheckBox2. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 18) = "" Then CheckBox3.Value = True Else CheckBox3. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 19) = "" Then CheckBox4.Value = True Else CheckBox4. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 20) = "" Then CheckBox5.Value = True Else CheckBox5. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 21) = "" Then CheckBox6.Value = True Else CheckBox6. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 22) = "" Then CheckBox7.Value = True Else CheckBox7. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 23) = "" Then CheckBox8.Value = True Else CheckBox8. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 24) = "" Then CheckBox9.Value = True Else CheckBox9. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 25) = "" Then CheckBox10.Value = True Else CheckBox10. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 26) = "" Then CheckBox11.Value = True Else CheckBox11. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 27) = "" Then CheckBox12.Value = True Else CheckBox12. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 28) = "" Then CheckBox13.Value = True Else CheckBox13. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 29) = "" Then CheckBox14.Value = True Else CheckBox14. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 30) = "" Then CheckBox15.Value = True Else CheckBox15. _
Value = False
If Not ListBox1.List(ListBox1.ListIndex, 31) = "" Then CheckBox16.Value = True Else CheckBox16. _
Value = False
e:
End Sub
Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim Ob As MSForms.Control
For Each Ob In UserForm1.Controls
If TypeOf Ob Is MSForms.TextBox Then Ob.Value = ""
If TypeOf Ob Is MSForms.ComboBox Then Ob.Value = ""
If TypeOf Ob Is MSForms.CheckBox Then Ob.Value = False
Next Ob
cbHinzufügen.Caption = "Hinzufügen"
End Sub
Private Sub UserForm_Initialize()
Dim lz As Integer
With ComboBox1 'Inhalt Anrede
.AddItem "Herr"
.AddItem "Frau"
.AddItem "Herr und Frau"
.AddItem "Monsieur"
.AddItem "Madam"
.AddItem "Signor"
.AddItem "Signora"
.AddItem "Dr."
End With
'Bildschirmanpassung der UserForm
Dim MyControl As Object
For Each MyControl In Controls
MyControl.Top = MyControl.Top * Application.Height / UserForm1.Height
MyControl.Left = MyControl.Left * Application.Width / UserForm1.Width
MyControl.Width = MyControl.Width * Application.Width / UserForm1.Width
MyControl.Height = MyControl.Height * Application.Height / UserForm1.Height
Next
With UserForm1
.Height = Application.Height - 3
.Width = Application.Width - 4
Me.Left = 0
Me.Top = 0
End With
With ComboBox2 'Inhalt Land
.AddItem "Schweiz"
.AddItem "Östereich"
.AddItem "Frankreich"
.AddItem "Deutschland"
End With
ListBox1.ColumnCount = 32
lz = Sheets("Auswertung").Cells(Rows.Count, 1).End(xlUp).Row
ListBox1.RowSource = "Auswertung!a8:af" & lz
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Sheets("Auswertung").Protect "xyz"
End Sub
mfg Andreas