AW: gefilterte Daten in Listbox ändern bzw. löschen
27.02.2019 14:28:44
Nepumuk
Hallo Peter,
ich hab mal alles überflüssige gelöscht:
Option Explicit
Sub ListBox1_fuellen()
Dim lZeile As Long
Dim lLibox As Long
Call ListBox1.Clear
With ThisWorkbook.Worksheets("Kategorien")
For lZeile = 2 To .Cells(.Rows.Count, 3).End(xlUp).Row
If Not .Rows(lZeile).Hidden Then
Call ListBox1.AddItem
ListBox1.List(lLibox, 0) = .Cells(lZeile, 2).Value
ListBox1.List(lLibox, 1) = .Cells(lZeile, 3).Value
ListBox1.List(lLibox, 2) = .Cells(lZeile, 4).Value
ListBox1.List(lLibox, 3) = .Cells(lZeile, 5).Value
ListBox1.List(lLibox, 4) = .Cells(lZeile, 6).Value
ListBox1.List(lLibox, 5) = lZeile
lLibox = lLibox + 1
End If
Next
End With
End Sub
'ListBox1 füllen
Private Sub CommandButton1_Click()
Call ListBox1_fuellen
End Sub
'Beenden
Private Sub CommandButton2_Click()
Call Unload(Object:=Me)
End Sub
'filtern
Private Sub CommandButton3_Click()
Call ThisWorkbook.Worksheets("Kategorien").Rows(1).AutoFilter(Field:=4, Criteria1:=TextBox4.Text)
End Sub
'Filter beenden
Private Sub CommandButton4_Click()
With ThisWorkbook.Worksheets("Kategorien")
If .FilterMode Then Call .ShowAllData
End With
Call ListBox1_fuellen
End Sub
'Filtern und Listbox füllen
Private Sub CommandButton5_Click()
Call ThisWorkbook.Worksheets("Kategorien").Rows(1).AutoFilter(Field:=4, Criteria1:=TextBox4.Text)
Call ListBox1_fuellen
End Sub
'Daten ändern
Private Sub CommandButton6_Click()
Application.EnableEvents = False
With ThisWorkbook.Worksheets("Kategorien")
.Cells(Clng(ListBox1.List(ListBox1.ListIndex, 5)), 2).Value = Val(TextBox1.Text)
.Cells(Clng(ListBox1.List(ListBox1.ListIndex, 5)), 3).Value = TextBox2.Text
.Cells(Clng(ListBox1.List(ListBox1.ListIndex, 5)), 4).Value = TextBox3.Text
.Cells(Clng(ListBox1.List(ListBox1.ListIndex, 5)), 5).Value = TextBox4.Text
.Cells(Clng(ListBox1.List(ListBox1.ListIndex, 5)), 6).Value = TextBox5.Text
End With
Application.EnableEvents = True
TextBox1.Text = vbNullString
TextBox2.Text = vbNullString
TextBox3.Text = vbNullString
TextBox4.Text = vbNullString
TextBox5.Text = vbNullString
Call ListBox1_fuellen
End Sub
'Auswahl aus Listbox in Textbox zur Änderung falls falscher Eintrag
Private Sub ListBox1_Click()
TextBox1.Text = ListBox1.List(ListBox1.ListIndex, 0)
TextBox2.Text = ListBox1.List(ListBox1.ListIndex, 1)
TextBox3.Text = ListBox1.List(ListBox1.ListIndex, 2)
TextBox4.Text = ListBox1.List(ListBox1.ListIndex, 3)
TextBox5.Text = ListBox1.List(ListBox1.ListIndex, 4)
End Sub
Private Sub UserForm_Initialize()
With ListBox1
.ColumnCount = 6
.Font.Size = 8
.ColumnWidths = "1cm;4cm;5cm;4cm;4cm;0cm"
End With
End Sub
Gruß
Nepumuk