Zoekfunctie in dropdownlist calculatiesheet

[Anoniem: 617800]

Mijn vraag
De calculatiesheet op mijn werk bevat meerdere dropdownlist. Deze dropdownlist bestaan uit een 3000-tal materialen die staan weergegeven op een ander tablad. Telkens bij het selecteren van het materiaal moet er door de hele lijst gescrold worden voor het juiste materiaal. Is het mogelijk om hier een zoekfunctie in te maken waardoor het me resultaten verkleind. Dus als ik paar letters typ excel automatisch alleen de materialen laat zien met die letters erin.




Relevante software en hardware die ik gebruik
Excel

Wat ik al gevonden of geprobeerd heb
https://trumpexcel.com/ex...-with-search-suggestions/
Dit heb ik ook wel weten na te maken in Excel. Maar dit is handig als je maar een dropdownlist nodig hebt en niet zoveel als mij.. Ook al krijg ik dit niet met mijn sheet voor mekaar.

[MAX3400]

Suggestie en tip: Google eens op "fuzzy" of "wildcard" search maar mogelijk is het veeeel efficienter als je je materiaallijst (desnoods in een extra sheet) op alfabet sorteert en op basis daarvan je dropdown genereert.

Ik zie nu bijvoorbeeld witregels in je dropdown; dat is eenvoudigweg "niet netjes"

[breew]

Ik kan dit alleen met een vba userform.. het is mij nog nooit gelukt met een combobox in een worksheet (maar ik heb het ook nog nooit geprobeerd )... Het is vast mogelijk om middels vba met een OLEobject daarheen te verwijzen.

Met een vba userform:
POC:
Het userform laadt bij initialisatie de combobox met waarden uit A2:A7


Zodra de gebruiker tekst opgeeft in de combobox, wordt de combobox gefilterd met wildcard voor en achter deze tekst.


VBA
userform:

Visual Basic:

Option Explicit

'doe dit elke keer als de gebruiker een toets heeft ingedrukt,
'terwijl het userform 'focus' heeft
Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Call Combobox1_Populate
End Sub

'doe dit als het userform wordt geladen
Private Sub UserForm_Initialize()
    Call Combobox1_Populate
End Sub

'match de waarden uit het bereik van de combobox met de userinput
Sub Combobox1_Populate()

    Dim arrIn As Variant, arrOut As Variant 'brondata en daardwerkelijke data voor ind e combobox
    Dim i As Long, j As Long  'lustellers
    
    'brondata voor in de combobox
    arrIn = Blad1.Range("A2:A7")
    'leeg de array met gegevens voor de combobox
    ReDim arrOut(1 To UBound(arrIn), 1 To 2)

    'loop door de brondata
    For i = 1 To UBound(arrIn)
        'lijkt een enrty uit de brondata op de ingevoerde tekst: *tekst*
        If arrIn(i, 1) Like "*" & ComboBox1.Text & "*" Then
            j = j + 1
            'mik hem dan in de array voor de combobox
            arrOut(j, 1) = arrIn(i, 1)
        End If
    Next
    
    'vul de combobox
    ComboBox1.List = arrOut
    
End Sub

Het is slechts een proof-of-concept, dus verbetering is zeker mogelijk. Succes!!

[Anoniem: 617800]

Ik begrijp wat je hierboven beschrijft maar niet helemaal hoe ik dit moet toepassen. Enkel kopiëren en plakken in de VBA is niet voldoende. Sorry ik ben in dit soort dingen nogal een leek.. Zou je mij hier misschien in kunnen helpen hoe ik dit kan toepassen op mijn werksheet? Bij elke selectie van een materiaal zal er dan een userform verschijnen?

[A13X]

Ik heb hier ooit mee gestoeid, maar hier moet je inderdaad naar VBA gaan.

link in theorie zou je het met deze link moeten kunnen maken. Echter kreeg ik het maar moeizaam voor elkaar, mijn lijst was echter "maar" 1000 artikelen welke ook nog eens gesorteerd was.

Uiteindelijk was het effectiever om het artikel (dat je niet makkelijk kon vinden) in de lijst op te zoeken en even te copy-paste in de rekensheets van de calculatie.

[ Voor 18% gewijzigd door A13X op 23-04-2018 09:56 ]

[breew]

Anoniem: 617800 schreef op maandag 23 april 2018 @ 09:21:
Ik begrijp wat je hierboven beschrijft maar niet helemaal hoe ik dit moet toepassen. Enkel kopiëren en plakken in de VBA is niet voldoende. Sorry ik ben in dit soort dingen nogal een leek.. Zou je mij hier misschien in kunnen helpen hoe ik dit kan toepassen op mijn werksheet? Bij elke selectie van een materiaal zal er dan een userform verschijnen?

Dat kan.. je kunt een worksheet_change event schrijven. Dat is een aparte macro waarmee je, elke keer als je een wijziging tikt in een cel onder "omschrijving", je het userform opstart en dus uiteindelijk een product selecteert. De geselecteerde tekst uit de combobox moet je dan in de geselecteerde cel op je werkblad terecht laten komen...

Als je er nog weinig ervaring mee hebt, dan zal het vooral een reis van trail&error worden.. maar daar leer je van ;-).

Als je beschikt over een beetje inzicht/aanleg/interesse voor programmeren, dan gaat het met onderstaand stappenplan vast lukken:

1. Lees je in mbt de basics van vba in excel (of kijk een paar tutorials).
2. Google net zo lang tot je van elke regel code uit mijn voorbeeld begrijpt wat hij doet.
3. Ga aan de slag met dummy-data en bovenstaande code..
4. Gebruik Engelstalige zoektermen van wat je wilt in Google... Stackoverflow is vaak een bron van inspiratie.
5. Zorg er voor dat je code van het internet pas overneemt als je het (min-of-meer) begrijpt.
6. Loop je vast? Post je code + screenshot data + gerichte vraag

[Anoniem: 617800]

Raym09 schreef op maandag 23 april 2018 @ 09:55:
Ik heb hier ooit mee gestoeid, maar hier moet je inderdaad naar VBA gaan.

link in theorie zou je het met deze link moeten kunnen maken. Echter kreeg ik het maar moeizaam voor elkaar, mijn lijst was echter "maar" 1000 artikelen welke ook nog eens gesorteerd was.

Uiteindelijk was het effectiever om het artikel (dat je niet makkelijk kon vinden) in de lijst op te zoeken en even te copy-paste in de rekensheets van de calculatie.

Kijk is aan dit is precies wat ik nodig heb en werkt ook nog! Enig minpuntje is dat er nu alleen op de eerste letter gezocht kan worden en niet op volledige trefwoorden midden in het artikel. Dit helpt al zeker bij het zoeken! Als ik dit laatste minpuntje ook nog kan verhelpen dan ben ik helemaal tevreden! @Raym09 Enig idee hoe dat voor elkaar kan krijgen?

@breew Thanks voor tip maar ik ben niet van plan het wiel opnieuw uit te vinden als er al kant en klare scrips aanwezig zijn. Ik ga me er zeker verder in verdiepen maar om dit nu compleet zelf in mekaar te gaan knutselen is me een stap te ver. En nee het is niet zo dat ik andere het werk wil laten doen, maar als ik al zie wat bovenstaande script mij oplevert zal een kleine aanpassing eraan mij helemaal naar het gewenste resultaat brengen .

[ Voor 20% gewijzigd door Anoniem: 617800 op 23-04-2018 15:16 ]

[A13X]

Anoniem: 617800 schreef op maandag 23 april 2018 @ 12:18:
[...]
Als ik dit laatste minpuntje ook nog kan verhelpen dan ben ik helemaal tevreden! @Raym09 Enig idee hoe dat voor elkaar kan krijgen?

Eerlijk gezegd niet, ik heb zo'n zelfde soort sheet gebouwd bij mijn vorige werkgever om mijn eigen calculatiewerk te vereenvoudigen. En toen ook met eenzelfde VBA code aan het stoeien geweest, echter moest ik dat in de verloren uurtjes op het werk doen(zonder enige programmeer achtergrond). Ik heb er toen niet heel veel aandacht aan gespendeerd om de simpele rede dat ik de meeste materialen vrij snel kon vinden in de lijst en ik het maar moeizaam aan de gang kreeg. Toen mijn aandacht op andere delen van de sheet gericht, en dit laten zitten voor wat het was. Uiteindelijk van baan veranderd waardoor ik deze sheet niet meer nodig had en daarmee ook met de ontwikkeling ervan gestopt.

Wat @breew zegt, verdiep je in VBA en de code die je gebruikt. Ik zou zelf niet snel code in een belangrijke sheet gebruiken die ik zelf niet begrijp. Voor vragen zijn er genoeg die verstand van VBA hebben om je erbij te helpen. Ik heb eventueel nog wel een pdf voor VBA te leren voor je.

[ Voor 13% gewijzigd door A13X op 23-04-2018 15:36 ]

[Lustucru]

*knip* geen wildcards

[ Voor 104% gewijzigd door Lustucru op 23-04-2018 20:23 ]

[breew]

Lustucru schreef op maandag 23 april 2018 @ 20:20:
*knip* geen wildcards

ja maar, ja maar...
Inderdaad.. geen wildcards.. ik dacht al.. word ik nou gek?
Al zal het niet de eerste keer zijn dat ik me uit de naad werk voor een feature die al blijkt te bestaan

[ Voor 0% gewijzigd door breew op 23-04-2018 20:40 . Reden: wordt ik = word ik ]

[Lustucru]

Raym09 schreef op maandag 23 april 2018 @ 09:55:
Ik heb hier ooit mee gestoeid, maar hier moet je inderdaad naar VBA gaan.

link in theorie zou je het met deze link moeten kunnen maken.

Die link is een beetje onzinnig: eerst schakelen ze de autocomplete functie uit om er vervolgens een na te bouwen. Autocomplete is out-of-the box functionaliteit van de active-x control; zoeken met wildcards is idd wat lastiger.

[A13X]

Lustucru schreef op maandag 23 april 2018 @ 20:27:
[...]
Die link is een beetje onzinnig.

Raym09 schreef op maandag 23 april 2018 @ 15:34:
[...]
(zonder enige programmeer achtergrond).

Ik geloof je meteen zou ook verklaren waarom ik het nooit aan de gang kreeg. Net als scrollen met het muiswiel in de combobox.

Excel formules ben ik best handig mee geworden, echter nooit genoeg tijd en aandacht aan vba gegeven.

Ook zijn niet alle functies beschikbaar in elke versie van Excel. Zo kan een van mijn collega's een nieuwe sheet van mij niet gebruiken omdat zijn versie als.nb niet kent.

[Lustucru]

breew schreef op vrijdag 20 april 2018 @ 10:06:
Ik kan dit alleen met een vba userform.. het is mij nog nooit gelukt met een combobox in een worksheet (maar ik heb het ook nog nooit geprobeerd )... Het is vast mogelijk om middels vba met een OLEobject daarheen te verwijzen.

Zonder een userform werkt dit ook prima. Plaats het activeXcontrol op het sheet, rechtermuisklik, programmacode weergeven en dan naar keuze het change event of een keypress event selecteren.

[Anoniem: 617800]

Lustucru schreef op maandag 23 april 2018 @ 20:27:
[...]

Die link is een beetje onzinnig: eerst schakelen ze de autocomplete functie uit om er vervolgens een na te bouwen. Autocomplete is out-of-the box functionaliteit van de active-x control; zoeken met wildcards is idd wat lastiger.

Lustucru schreef op maandag 23 april 2018 @ 21:52:
[...]

Zonder een userform werkt dit ook prima. Plaats het activeXcontrol op het sheet, rechtermuisklik, programmacode weergeven en dan naar keuze het change event of een keypress event selecteren.

Ik kan het even niet meer volgen. De link die Raym09 heeft geplaatst heb ik toegepast en dit werk nu aardig. Enige probleem is dat het alleen maar zoekt naar de eerste letter en niet naar trefwoorden in de zin. Dit zou met een kleine aanpassing toch wel mogelijk moeten zijn?

[breew]

Anoniem: 617800 schreef op dinsdag 24 april 2018 @ 09:16:
Ik kan het even niet meer volgen. De link die Raym09 heeft geplaatst heb ik toegepast en dit werk nu aardig. Enige probleem is dat het alleen maar zoekt naar de eerste letter en niet naar trefwoorden in de zin. Dit zou met een kleine aanpassing toch wel mogelijk moeten zijn?

Nope... dat is (volgens mij) geen kleine aanpassing... Die functionaliteit (wildcards) zit (volgens mij) gewoonweg niet in het standaard-element, en zul je dus zelf moeten bouwen.

[Lustucru]

Anoniem: 617800 schreef op dinsdag 24 april 2018 @ 09:16:
[...]


[...]


Ik kan het even niet meer volgen. De link die Raym09 heeft geplaatst heb ik toegepast en dit werk nu aardig. Enige probleem is dat het alleen maar zoekt naar de eerste letter en niet naar trefwoorden in de zin. Dit zou met een kleine aanpassing toch wel mogelijk moeten zijn?

De functionaliteit om te filteren op beginletters zit standaard in het activeX control. Daar heb je dus geen VBA voor nodig.
De code die @breew geeft doet precies wat je wilt, met als aanvulling mijn opmerking hoe je de code direct aan t control kunt verbinden. Een userforn is overbodig.

[Anoniem: 617800]

Lustucru schreef op dinsdag 24 april 2018 @ 17:48:
[...]

De functionaliteit om te filteren op beginletters zit standaard in het activeX control. Daar heb je dus geen VBA voor nodig.
De code die @breew geeft doet precies wat je wilt, met als aanvulling mijn opmerking hoe je de code direct aan t control kunt verbinden. Een userforn is overbodig.

Aah inderdaad, Thanks! Maar hoe krijg ik deze gekoppeld dan aan al mijn dropdownlists in mijn sheet? Nu plaats ik namelijk een nieuwe activex dropdownlist en kopieer ik de code van @breew in de programmacode en verander de blad.range en dan werkt het prima voor alleen die dropdownlist. Maar hoe zorg ik er nu voor dat dit in een keer werkt voor al mijn bestaande dropdownlist in het bestand. Bij de VBA code van @Raym09 werkte dit automatisch voor alle dropdownlist op hetzelfde tablad?

Sorry voor al mijn vragen maar we zijn er bijna .

[ Voor 24% gewijzigd door Anoniem: 617800 op 02-05-2018 09:22 ]

[breew]

Anoniem: 617800 schreef op dinsdag 1 mei 2018 @ 13:33:
[...]
Aah inderdaad, Thanks! Maar hoe krijg ik deze gekoppeld dan aan al mijn dropdownlists in mijn sheet? Nu plaats ik namelijk een nieuwe activex dropdownlist en kopieer ik de code van @breew in de programmacode en verander de blad.range en dan werkt het prima voor alleen die dropdownlist. Maar hoe zorg ik er nu voor dat dit in een keer werkt voor al mijn bestaande dropdownlist in het bestand.

Precies daarvoor had ik het userform in opgenomen in mijn code.
Mijn idee daarachter was dat je geen comboboxes meer nodig had, die staat dan in het userform.
Bij selectie van één van de cellen voor input, wordt de macro automatisch gestart en het userform geladen.
De geselecteerde waarde uit het userform kan dan in de geselecteerde cel op het werkblad worden gezet.

Op die manier hoef je bij een lange lijst geen 100-en comboboxes in je sheet op te nemen.

Visual Basic:

'deze macro draait elke keer als de geselecteerde cel wijzigt
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim rngWaarden As Range    'bereik waarvan je het gebruik wilt monitoren

    'stel het bereik van de te monitoren cellen in, in dit voorbeeld A1:A10
    Set rngWaarden = Range("A1:A10")
    
    'kijk of de nieuw geselecteerde cel valt binnen (intersection) het bereik van te monitoren cellen
    If Not Application.Intersect(rngWaarden , Range(Target.Address)) Is Nothing Then
       'start de macro
    End If

End Sub

Het makkelijkst is om de in de userform selectecteerde waarde even op te slaan in een globale variabele.

[ Voor 4% gewijzigd door breew op 02-05-2018 10:21 ]

[Lustucru]

breew schreef op woensdag 2 mei 2018 @ 09:34:
[...]
Precies daarvoor had ik het userform in opgenomen in mijn code.


Het makkelijkst is om de in de userform selectecteerde waarde even op te slaan in een globale variabele.

En dan link naar je naar GlobalsAreBad? Met alle respect, ik blijf het onhandig vinden. Ook een dropbox kun je hergebruiken. Inderdaad in het selection change event controleer je of er één cel in het 'dropdown bereik' is geselecteerd. Als dat het geval is, plaats je de dropbox op de goede plek, toon je hem, en koppel je de actieve cel. In het andere geval verberg je hem.

Voor het vullen gebruik je de code van @breew . Tot slot is het nog fijn als je met bv de tabtoets de dropbox weer kunt verlaten. Daar is dan het key_up event voor. De volledige code wordt dan:

Visual Basic:

Option Explicit


Private Sub ComboBox1_Change()
    Dim arrIn As Variant, arrOut As Variant, v As Variant, j As Integer
    arrIn = Blad1.Range("A1:A10")
    ReDim arrOut(UBound(arrIn), 1)
    For Each v In arrIn
        If v Like "*" & ComboBox1.Text & "*" Then
            arrOut(j, 0) = v
            j = j + 1
         End If
    Next v
    ComboBox1.List = arrOut
    ComboBox1.DropDown
End Sub

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
    Case vbKeyTab
        Selection.Offset(0, 1).Select
    End Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If (Not Intersect(Target, Range("B2:b100")) Is Nothing) And (Target.Rows.Count = 1) And (Target.Columns.Count = 1) Then
       With ActiveSheet.Shapes("ComboBox1")
            .Top = Target.Top
            .Left = Target.Left
            .Visible = True
        End With
        With ComboBox1
            .LinkedCell = Target.Address
            .Activate
        End With
    Else
        ActiveSheet.Shapes("ComboBox1").Visible = False
    End If
End Sub

[breew]

Lustucru schreef op woensdag 2 mei 2018 @ 11:15:
[...]
En dan link naar je naar GlobalsAreBad?

Ja, want ik ben van mening dat je globale variabelen zoveel mogelijk moet vermijden.. maar soms zijn ze gewoon verrekte handig.. net als goto's.