Hi all,
I've got a sheet with two listbox'. Depending on what was chosen in the first one, the second one changes its value:
If Worksheets("Dashboard").ComboBox1.ListIndex = 0 Then
Worksheets("Dashboard").ComboBox2.ListIndex = -1
Worksheets("Dashboard").ComboBox2.Enabled = False
Sheets("Calc").Range("B13:B14") = "Alles"
Else
Worksheets("Dashboard").OLEObjects("ComboBox2").ListFillRange = ComboBox1.Value
Worksheets("Dashboard").ComboBox2.ListIndex = 0
Sheets("Calc").Range("B13") = ComboBox1.Value
Sheets("Calc").Range("B14") = ComboBox2.Value
End If
Both of them execute another code:
Sub FilterBy()
Dim Cell, rSelect As Range
Dim sKampagne, sTeam As String
Sheets("Dashboard").Range("C15:E300").ClearContents
sKampagne = Sheets("Calc").Range("sKampagne")
sTeam = Sheets("Calc").Range("sTeam")
Set rSelect = Nothing
For Each Cell In Sheets("Data").Range("Employees")
If Cell.Offset(0, -1) = sKampagne And Cell.Offset(0, 1) = sTeam _
Or sKampagne = "Alles" Or sTeam = "Alles" And Cell.Offset(0, -1) = sKampagne Then
If rSelect Is Nothing Then
Set rSelect = Cell.Resize(1, 3)
Else
Set rSelect = Union(rSelect, Cell.Resize(1, 3))
End If
End If
Next Cell
rSelect.Copy
Sheets("Dashboard").Range("C15").PasteSpecial Paste:=xlPasteValues
End Sub
The code itself is working perfectly. If I change the "Combobox2_Change" event to "Combobox2_LostFocus" and leave everything else unchanged, everything works. But when it's set to "Change" there is always an error.
Anyone can make out something of this? I'll try to desensibilize the workbook in a few mins.
Bookmarks