Onderwerp


De functionaliteit van het Auto filter in Excel is prachtig, de indicatie dat een filter actief is, is dat niet. In Excel 2003 kun je een actief filter herkennen aan het feit dat het pijltje van de keuzelijst blauw in plaats van zwart is. In Excel 2007 is daar verandering in gebracht. Een actief filter wordt weergegeven door een trechter te plaatsen op de keuzelijst. Dat is beter, maar nog niet optimaal. Met VBA kun je echter een oplossing bedenken waardoor een actief filter wel duidelijk in beeld komt.

De oplossing betreft het gebruik van de functie Subtotaal, in combinatie met een tweetal VBA procedures.


Je kunt een werkboek met het onderstaande voorbeeld hier downloaden.


Informatie


Kopieer de volgende procedures en zet ze in de VBA Editor in de module van het werkblad dat het Auto filter bevat.

Private Sub Worksheet_Calculate()
If ActiveSheet.AutoFilterMode = True Then
Call Kleur_Actief_Filter
End If
End Sub

Private Sub Kleur_Actief_Filter()
Dim flt As Filter
Dim cCol As Integer
Dim lRow As Long
cCol = 1
lRow = ActiveSheet.AutoFilter.Range.Row
Application.EnableEvents = False

For Each flt In ActiveSheet.AutoFilter.Filters
cCol = cCol + 1
If flt.On = True Then
With Cells(lRow, cCol)
.Interior.Color = RGB(192, 0, 0)
.Font.ColorIndex = 2
End With
Else
With Cells(lRow, cCol)
.Interior.Color = RGB(191, 191, 191)
.Font.Color = RGB(51, 102, 255)
End With
End If
Next flt

Application.EnableEvents = True

Cells(7, 8).Select
End Sub


Naast deze twee procedures
moet je de functie Subtotaal opnemen in het werkblad dat het filter bevat. Je kunt de functie opnemen in een cel die je verbergt, maar dat laatste is niet noodzakelijk.



Hoe het werkt.
Het event Worksheet_Calculate wordt geactiveerd nadat een berekening op het werkblad is uitgevoerd. Dit maakt duidelijk waarom de functie Subtotaal op het werkblad is ingevoegd. Elke keer als een filter wordt ingeschakeld, rekent de functie Subtotaal een nieuwe waarde uit. Er vindt dus een nieuwe berekening plaats.
Het event Worksheet_Calculate roept dus, elke keer als het Auto filter wordt gewijzigd, de procedure Kleur_Actief_Filter op.

De procedure Kleur_Actief_Filter controleert voor elke filter (For Each flt In ActiveSheet.AutoFilter.Filters) of het filter actief is (On).

Als het filter actief is (If flt.On = True), worden de achtergrondkleur en de kleur van het lettertype van de Veldnaam aangepast.

With Cells(lRow, cCol)
.Interior.Color = RGB(192, 0, 0)
.Font.ColorIndex = 2
End With


De opmaak van het filter dat niet actief is
(Else), wordt teruggedraaid.

With Cells(lRow, cCol)
.Interior.Color = RGB(191, 191, 191)
.Font.Color = RGB(51, 102, 255)
End With