Filteren tijdens het typen (VBA)

Geplaatst in VBA door Exhelp | Tags: , , , , ,
Van toepassing op: Excel 2003, Excel 2007, Excel 2010, Excel 2013

Het filteren van een lijst is een eenvoudige maar krachtige manier om gegevens te analyseren. Echter hier gaat heel wat typ- en klikwerk aan vooraf. In sommige bestanden of dashboard zou het leuker zijn als we gewoon al typend kunnen filteren, zoiets dus:

20151028_filter_tijdens_typen

Laten we eens kijken hoe we dit kunnen doen, met wat héél eenvoudige VBA code.

Stap 1: maak een tabel waarop je wil filteren

Neem je gegevens en maak er een tabel van (CTRL+L).

20151028_filter_001

Geef deze tabel de naam ‘Gemeentes’ door op de tabel te gaan staan, in het lint te kiezen voor het tabblad ‘Ontwerpen’ en vervolgens de Tabelnaam te wijzigen:

20151028_filter_002

Stap 2: een ActiveX tekstvak invoegen

Ga in het lint naar het tabblad ‘Ontwikkelaars’, klik op ‘Invoegen’ en kies voor het ‘ActiveX Tekstvak’.

Plaats het tekstvak op je werkblad, bij voorkeur boven de tabel.

20151028_filter_003

Stap 3: het tekstvak koppelen aan een lege cel

Klik met je rechtermuisknop op het tekstvak en kies voor ‘Eigenschappen’.

Vul bij LinkedCell een lege cel naar keuze in op je werkblad (ik heb gekozen voor D5)

20151028_filter_004

Stap 4: een Change() event toevoegen aan het Tekstvak

We willen dat onze filter reageert telkens er iets wijzigt in het tekstvak.

Klik met je rechtermuisknop op het tekstvak en kies voor ‘Programmacode weergeven’.

Je komt terecht in de VBA editor. Het event (TextBox1_Change() ) wordt alvast voor je gecreëerd.

Hier moeten we wat eenvoudige VBA toevoegen, namelijk:

ActiveSheet.ListObjects("Gemeentes").Range.AutoFilter Field:=1, Criteria1:="*" & [D5] & "*", Operator:=xlFilterValues

Vervang de woorden Gemeentes en D5 door jouw eigen tabelnaam en het adres van de gelinkte cel.

De volledige VBA code ziet er dan zo uit:

Private Sub TextBox1_Change()

ActiveSheet.ListObjects("Gemeentes").Range.AutoFilter Field:=1, _
    Criteria1:="*" & [D5] & "*", Operator:=xlFilterValues

End Sub

Stap 5: een ‘Reset’ knop voorzien

VBA invoegen

Om het allemaal nog wat gebruiksvriendelijker te maken, voorzien we een reset knop. Dit doen we opnieuw met een stukje VBA. Gezien we toch nog in de VBA editor bezig zijn, zetten we deze code onder onze vorige code:

Sub ExhelpClearFilter()

    [D5] = ""
    
    ActiveSheet.ListObjects("Gemeentes").Range.AutoFilter _
        Field:=1
        
    TextBox1.Activate
    
End Sub

Deze code gaat:

  • de gelinkte cel leegmaken
  • de filter resetten zodat de rijnummers niet blauw blijven
  • opnieuw het tekstvak selecteren, zodat de gebruiker meteen terug kan beginnen typen

Knop invoegen

Sluit de VBA editor. Zo kom je terug op je werkblad terecht.

We gaan opnieuw een besturingselement invoegen, deze keer een knop. Ga in het lint naar het tabblad ‘Ontwikkelaars’, klik op ‘Invoegen’ en kies voor de ‘Knop’. Plaats de knop op je werkblad, bij voorkeur boven de tabel.

20151028_filter_005

Klik met je rechtermuisknop op de knop en kies voor ‘Macro toewijzen’. Kies vervolgens voor onze macro ‘ExhelpClearFilter’ en druk op ‘OK’.

Stap 6: testen maar!

Ga in het lint naar het tabblad ‘Ontwikkelaars’ en zet de ‘Ontwerpmodus’ uit.

Je kan nu je filterveld gebruiken!

 

 

 

 

11 reacties op “Filteren tijdens het typen (VBA)”

  • Even een melding
    het voorbeeld bestand is niet gekoppeld aan cel D5 maar aan D8
    dit moet je in de macro aanpassen anders werkt het niet

    wel een mooie tip
    bedankt

  • Mooi, opgelost , is er een mogelikheid op na filtering een keuze te maken door er op te dubbelklikken en dan de keuze ergens in een cel te zetten

    • Ik heb het zelf kunnen oplossen, werkt nu na filteren op keuze dubbelkik en ik krijg de naam die ik wil hebben op ander blad

    • Hoe is de code om dmv dubbelklikken op cel deze in andere cel wordt weergegeven

  • Hoi, goede feature. Maar mij lukt het nog niet bij ‘bredere’ tabellen. De tabel uit het voorbeeld is één kolom breed. Kun je ook een zoekcode maken dat er op meerdere kolommen wordt gezocht?
    Groetjes en alvast bedankt, BHU

    • Beste BC

      Ervaring leert mij dat werken met meerdere kolommen in VBA voor problemen zorgt.
      Zie nu wel niet in waarom je meerdere kolommen nodig zou hebben als je deze ook kan voorzien in één kolom.

      Grts
      Marc

  • Bedankt voor deze info. Ik zit met het probleem hoe geef ik in VBA code weer dat ik in een tabel maar één of enkele kolom(men) wil gebruiken om filteren.

    Gr en bedankt.

    J Hardy

    • Beste JHardy,

      Ik dacht dat je dit kunt aanpassen door “Field:=1” te veranderen naar “Field:=2” etc.

      Hierbij staat 1 voor de eerste kolom en 2 voor de tweede etc. etc.

  • Bedankt voor deze info! Geweldig.

    Het gekke is echter is dat als ik de code zelf overschrijf hij een foutmelding geeft maar als ik de code plak hij het wel doet. (de code is exact het zelfde als de code zoals ik het zo plakken).

    Wat doe ik dan fout?

    (met ctrl + spatie kan ik wel ActiveSheet selecteren maar de “.” erachter pakt hij niet)

  • Kun je de tekstbox ook op een ander blad plaatsen?
    en wat is dan de code bv blad familie.
    neem aan dat er voor de cel nog iets moet

    Private Sub TextBox1_Change()

    ActiveSheet.ListObjects(“Familie”).Range.AutoFilter Field:=1, _
    Criteria1:=”*” & [D5] & “*”, Operator:=xlFilterValues

    End Sub

Plaats een reactie

U moet ingelogd zijn om te reageren.

Login

Forums

Exhelp

Meest recente berichten

TAGS



Creative Commons Licentie
Op dit werk is een Creative
Commons Licentie
van toepassing.