|
Hallo,
ich habe eine Useform für eine Aktionsliste erstellt, hat grundsätzlich alles gut funktioniert.
die aktionsliste kann gefiltert werden - nach status zu aktionen - offen, in arbeit, pausiert, abgeschlossen.
das filter hat ohne probleme funktioniert, nu ist der wunsch nach einem zweiten filter gekommen - und zwar nach bereichen --> Fertigung, einkauf, usw.
nach was gefiltert wird, wird über userform3 gesteuert..
die 4 Status werden über optionbuttons angeklickt(das hat funktioniert) und die Bereiche über eine multiselect-Listbox (jetzt gehts nichtmehr)
unten der ziemlich hässliche Code, zum zweiten Call Eintrag_laden (der befehll befüllt userform 1 mit Daten wenn die filter zutreffen) springt der debugger..
vielleicht kann mir wer helfen
danke, thomas :)
Sub Eintrag_Laden()
Dim X As Integer
Dim Y As Integer
Dim Z As Integer
Dim presel1 As Integer
Dim lngPos As Long
Dim rngItems As Range
Dim oDictionary As Object
With Worksheets("AIL")
For Z = 0 To UserForm3.ListBox1.ListCount - 1
If UserForm3.ListBox1.List(Z) = .Cells(i, 3).Value Then
If .Cells(i, 10).Value = filter1 Or .Cells(i, 10).Value = filter2 Or .Cells(i, 10).Value = filter3 Or .Cells(i, 10).Value = filter4 Then
If .Cells(i, 3).Value <> "" Then UserForm1.ComboBox3.Value = .Cells(i, 3).Value
Set rngItems = Range("C8:C" & lrow1)
Set oDictionary = CreateObject("Scripting.Dictionary")
With UserForm1.ComboBox3
For Each cel In rngItems
If oDictionary.exists(cel.Value) Then
'Do Nothing
Else
oDictionary.Add cel.Value, 0
.AddItem cel.Value
End If
Next cel
End With
UserForm1.TextBox6.Value = Worksheets("AIL").Cells(i, 1).Value
History = 1
With Worksheets("Projektteam")
UserForm1.ComboBox1.List = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Value
End With
UserForm1.ComboBox1.ListIndex = -1
text1 = Cells(i, 6).Value
textboxin = Format(Now, "dd.mm.yyyy") & " - " & Application.UserName & ": "
UserForm1.TextBox1.Value = Cells(i, 6).text
UserForm1.TextBox5.Value = textboxin
text1 = textboxin & Cells(i, 6).Value
With UserForm1.TextBox5
lngPos = InStr(1, .Value, vbCr) - 1
If lngPos > 0 Then
.SelStart = lngPos
Else
.SelStart = .TextLength
End If
Call .SetFocus
End With
UserForm1.TextBox3.Value = Cells(i, 5).Value
UserForm1.TextBox4.Value = Cells(i, 8).Value
date1 = Cells(i, 8).Value
UserForm1.TextBox2.Value = Cells(i, 7).Value
Prio_A = Worksheets("AIL").Cells(i, 4).Value
status_B = Worksheets("AIL").Cells(i, 10).Value
If Cells(i, 4).Value = "A" Then
UserForm1.CommandButton10.BackStyle = fmBackStyleOpaque
UserForm1.CommandButton11.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton12.BackStyle = fmBackStyleTransparent
Prio_B = "A"
ElseIf Cells(i, 4).Value = "B" Then
UserForm1.CommandButton10.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton11.BackStyle = fmBackStyleOpaque
UserForm1.CommandButton12.BackStyle = fmBackStyleTransparent
Prio_B = "B"
ElseIf Cells(i, 4).Value = "C" Then
UserForm1.CommandButton10.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton11.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton12.BackStyle = fmBackStyleOpaque
Prio_B = "B"
Else
UserForm1.CommandButton10.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton11.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton12.BackStyle = fmBackStyleTransparent
Prio_B = ""
End If
If Cells(i, 10).Value = "open" Then
UserForm1.CommandButton1.BackStyle = fmBackStyleOpaque
UserForm1.CommandButton2.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton3.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton4.BackStyle = fmBackStyleTransparent
status_B = "open"
ElseIf Cells(i, 10).Value = "in work" Then
UserForm1.CommandButton1.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton2.BackStyle = fmBackStyleOpaque
UserForm1.CommandButton3.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton4.BackStyle = fmBackStyleTransparent
status_B = "in work"
ElseIf Cells(i, 10).Value = "on hold" Then
UserForm1.CommandButton1.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton2.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton3.BackStyle = fmBackStyleOpaque
UserForm1.CommandButton4.BackStyle = fmBackStyleTransparent
status_B = "on hold"
ElseIf Cells(i, 10).Value = "closed" Then
UserForm1.CommandButton1.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton2.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton3.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton4.BackStyle = fmBackStyleOpaque
status_B = "closed"
ElseIf neuerEintrag = 1 Then
UserForm1.CommandButton1.BackStyle = fmBackStyleOpaque
UserForm1.CommandButton2.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton3.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton4.BackStyle = fmBackStyleTransparent
status_B = "open"
Else
UserForm1.CommandButton1.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton2.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton3.BackStyle = fmBackStyleTransparent
UserForm1.CommandButton4.BackStyle = fmBackStyleTransparent
status_B = ""
End If
Else
i = i + 1
Call Eintrag_Laden
End If
Else
i = i + 1
Call Eintrag_Laden
End If
Next Z
End With
End Sub
|