Guten Morgen Peter,
habe gestern Abend noch eine Beispieldatei von Beni erhalten, funktioniert, muß noch ein wenig anpassen, funktioniert.
Würde mich trotzdem freuen von Dir ein Beispiel zu erhalten,
bis dann Walter
Hier das Makro kompl.:
Private Sub OptionButton2_Click()
Dim c As Range
Dim Zeile As Integer
Dim z
Dim ze
ActiveSheet.Unprotect (getStrPasswort)
Range("A3:AB3").Select
If Not ActiveSheet.AutoFilterMode Then
Selection.AutoFilter
End If
ComboBox1.ListIndex = -1
Range("F3").Select
Selection.AutoFilter Field:=5, Criteria1:="01"
If OptionButton2 = True Then
OptionButton2.ForeColor = &HFF& 'Rot
OptionButton1.ForeColor = &H80000012 'Grün
OptionButton6.ForeColor = &H80000012 'Schwarz
OptionButton3.ForeColor = &H80000012 'Schwarz
OptionButton4.ForeColor = &H80000012 'Schwarz
OptionButton5.ForeColor = &H80000012 'Schwarz
End If
ActiveWindow.ScrollRow = 3 '8 Zeile
ActiveWindow.ScrollColumn = 2 '2 Spalte
Label6.Caption = ActiveSheet.Range("J2").Value
z = Range("a3").End(xlDown).Row
ze = FindFirstRow_in_Filter(Range("A4:U" & z))
ActiveSheet.Unprotect (getStrPasswort)
'------------------- funktioniert aber nur bis 9 Spalten --------------------------
'Dim c As Range
'Dim Zeile As Integer
'ListBox1.Clear
''ListBox1.RowSource = ""
'' For Each c In Range("E3:E65000")
'' If c.Value = "1" Then
'' With ListBox1
'' .ColumnCount = 21
' .ColumnWidths = "2cm;2cm;3cm;3cm;2cm" "K" "L" "M"
'' .ColumnWidths = "0,8cm;0cm;2,5cm;0,8cm;0,8cm;3,5cm;2,3cm;2,3cm;2,5cm;2cm;0cm;0cm;0cm;1,8cm;0cm;0cm;2cm;0cm;0cm;0cm;2cm;"
'' .AddItem c.Offset(0, -4).Value ' 4auf lf.Nr.
'' .List(Zeile, 1) = c.Offset(0, -3).Value 'ist Spalte "b" setzen
'' .List(Zeile, 2) = c.Offset(0, -2).Value 'Nutzer
'' .List(Zeile, 3) = c.Offset(0, -1).Value 'Kennung
'' .List(Zeile, 4) = Format(c.Offset(0, 0).Value, "00") 'Center
'' .List(Zeile, 5) = c.Offset(0, 1).Value 'Typ
'' .List(Zeile, 6) = c.Offset(0, 2).Value 'Auftragsnummer
'' .List(Zeile, 7) = c.Offset(0, 3).Value 'Kennzeichen
'' .List(Zeile, 8) = c.Offset(0, 4).Value 'Sonstiges
'' .List(Zeile, 9) = c.Offset(0, 5).Value 'Erstzulassung
' .List(Zeile, 10) = c.Offset(0, 6).Value 'K=
' .List(Zeile, 11) = c.Offset(0, 7).Value 'L
' .List(Zeile, 12) = c.Offset(0, 8).Value 'M
' .List(Zeile, 13) = Format(c.Offset(0, 9).Value, "0,000") 'KM
' .List(Zeile, 14) = c.Offset(0, 10).Value 'Erstzulassung
' .List(Zeile, 15) = c.Offset(0, 11).Value 'Erstzulassung
' .List(Zeile, 16) = c.Offset(0, 12).Value 'Erstzulassung
'' Zeile = Zeile + 1
'' End With
'' End If
'' Next c
'-------------- das ist von Beni 14-3-06 ---------------------------------------
With ListBox1
' .ListStyle = fmListStyleOption 'Orginal sind die Kreise vorn weg
Dim arrValues() As Variant
'Dim i, intRow As Integer, intRowU, z As Integer 'Orginal
Dim i, intRow As Integer, intRowU As Integer
'ListBox1.Clear
ListBox1.RowSource = ""
z = ActiveSheet.Range("a3").End(xlDown).Row
For intRow = 4 To z 'war 2 da war kompl.Überschriften
If ActiveSheet.Rows(intRow).EntireRow.Hidden = False Then
ReDim Preserve arrValues(0 To 20, 0 To intRowU)
arrValues(0, intRowU) = ActiveSheet.Cells(intRow, 1) 'lf.Nr.
arrValues(1, intRowU) = ActiveSheet.Cells(intRow, 2)
arrValues(2, intRowU) = ActiveSheet.Cells(intRow, 3)
arrValues(3, intRowU) = ActiveSheet.Cells(intRow, 4)
arrValues(4, intRowU) = Format(ActiveSheet.Cells(intRow, 5), "00") 'Center
arrValues(5, intRowU) = ActiveSheet.Cells(intRow, 6)
arrValues(6, intRowU) = ActiveSheet.Cells(intRow, 7)
arrValues(7, intRowU) = ActiveSheet.Cells(intRow, 8)
arrValues(8, intRowU) = ActiveSheet.Cells(intRow, 9)
arrValues(9, intRowU) = ActiveSheet.Cells(intRow, 10)
arrValues(10, intRowU) = ActiveSheet.Cells(intRow, 11)
arrValues(11, intRowU) = ActiveSheet.Cells(intRow, 12)
arrValues(12, intRowU) = ActiveSheet.Cells(intRow, 13)
arrValues(13, intRowU) = Format(ActiveSheet.Cells(intRow, 14), "0,000") 'KM
arrValues(14, intRowU) = Format(ActiveSheet.Cells(intRow, 15), "0,000.00")
arrValues(15, intRowU) = ActiveSheet.Cells(intRow, 16)
arrValues(16, intRowU) = Format(ActiveSheet.Cells(intRow, 17), "0.00") ' Kulanz%
arrValues(17, intRowU) = ActiveSheet.Cells(intRow, 18)
arrValues(18, intRowU) = ActiveSheet.Cells(intRow, 19)
arrValues(19, intRowU) = ActiveSheet.Cells(intRow, 20)
arrValues(20, intRowU) = Format(ActiveSheet.Cells(intRow, 21), "0,000.00")
intRowU = intRowU + 1
End If
Next intRow
.ColumnCount = 21
.ColumnWidths = "0,8cm;0cm;2,5cm;0,8cm;0,8cm;3,5cm;2,3cm;2,3cm;2,5cm;2cm;0cm;0cm;0cm;1,8cm;0cm;0cm;2cm;0cm;0cm;0cm;2cm;"
If intRowU <> 0 Then ListBox1.Column = arrValues
End With
End Sub