Kan dit ook met VBA (Excel) ?

Status
Niet open voor verdere reacties.
W

willema

Gebruiker
Lid geworden
26 aug 2005
Berichten
319
Dag allemaal,

Op http://www.gratiscursus.be/Excel_Tips/Excel_Tip_200.html staat er manier beschreven om een "Filterende Drop-Down Lijst" te maken.

Ergens in een kolom (bvb. D vanaf rij 2 t.e.m. rij 250) worden alfabetisch een aantal cellen aansluitend gevuld met gegevens.

In een aantal andere kolommen staan dan formules zoals:
in kolom C
=ALS(ISGETAL(VIND.ALLES($B$2;D2));MAX($C$1:C1)+1;0)
en kolom F
=ALS.FOUT(VERT.ZOEKEN(RIJEN($F$2:F2);$C$2:$D$250;2;0);"")

De lijst in kolom F wordt dan aangepast aan de invoer in cel B2.

Met
=VERSCHUIVING($F$2;;;AANTAL.ALS($F$2:$F$500;"?*"))
wordt een nieuwe Naam gemaakt die dient als bron van een Gegevensvalidatie van het type Lijst in cel B2.


Allemaal heel mooi en het werkt ook goed, maar je hebt al gauw twee of meer extra kolommen nodig bovenop de kolom D met de basislijst.

Mijn vraag is nu: Kan dat ook met VBA ?
Iets als...
-Ergens op een apart werkblad een bereik (een aansluitend deel van een kolom) met de basisgegevens.
-Dat bereik in VBA omzetten naar een Array.
-Met deze Array de functies uitvoeren
-Om tenslotte een gefilterde Array te bekomen die kan dienen als bron van een Gegevensvalidatie van het type Lijst in de Target-cel.

Grote verschil met wat ik vind op bovenstaande site (en ook op soortgelijke voorbeelden op dit forum) en met wat ik eigenlijk wil maken is dat in deze voorbeelden de zoekwaarde wordt ingetikt in één cel (B2). Ik wil in één kolom 75 cellen onder elkaar voorzien van dezelfde gegevensvalidatie. De ActiveCell.Value is dan de zoekwaarde.
Ik heb een workaround zoals in de voorbeelden, maar dan komen er nog twee kolommen bij. Ik vermoed dat het met VBA eenvoudiger moet kunnen.
Weet iemand hoe ?
 
Plaats je voorbeeldbestand eens met weergave van het gewenste resultaat.
De VBA methode is nl. erg afhankelijk van de basisstruktuiur van de gegevens.
 
Alstublief snb,

ik heb eerst mijn bestand wat opgekuist (mijn rommel van allerhande mislukte testen:o) en tot de essentiële werkbladen/kolommen/VBA herleid.
Ook wat commentaar bijgeschreven in werkblad Basis01.
En vertrouwelijke informatie :cool: in werkblad lijst SETS vervangen door namen van Vlaamse gemeenten.

Hoop dat het zo duidelijk is wat ik wil. Dus:
-alles met VBA zodat ik de kolommen A:G niet moet gebruiken (en dus ook niet moet verbergen).
-dat dit ook lukt in een beveiligd document: werkmap en -bladen (als ik de bladbeveiliging nu aanzet kan ik de lijst niet meer selecteren in kolom H, nochtans zijn die cellen niet geblokkeerd en staat de beveiliging op UserInterfaceOnly:=True)
 

Bijlagen

Dag Elsendoorn2134,

ik ga dit morgen met zeer veel interesse bestuderen en kom er later via deze weg op terug.

Alvast bedankt !
 
Eveneens dankjewel snb,

ook deze ga ik vandaag met interesse bestuderen.
Wel al een tussentijds vraagje: u gebruikt een xlsb-formaat ipv een xlsm-. Heeft dat een reden ?
 
Dag Elsendoorn2134 en snb,

ik heb jullie beide voorbeelden bekeken, maar het geeft jammer genoeg nog niet de antwoorden op mijn vragen.

snb,
ik snap niet goed wat dit voorbeeldsjabloon doet. Er is maar één werkblad Basis01. Lijst_SETS is verdwenen (ook niet verborgen, maar in de VBA-editor zie ik hiervan wel een Worksheet-module), maar er wordt wel naar verwezen in de bron van de gegevensvalidatie Basis01!A1:A74.
De enige VBA-code die ik zie staat in de module ThisWorkBook

Private Sub Workbook_Open()
Blad8.Cells.SpecialCells(-4174).Validation.Modify 3, , , "=Lijst_sets!" & Blad3.Cells(1).CurrentRegion.Columns(1).Address
End Sub

Ondanks die verwijzing naar Blad3 start het bestand toch op op mijn laptop, maar komt er een VBA-fout op mijn desktop voor die (enige) regel.

En na invoer in kolom A wordt op dezelfde rij in kolom B de rijwaarde berekend.
Het zal zeker aan mij liggen :) maar ik begrijp geen snars van wat dit zou moeten doen.

Elsendoor 2134,
de klassieke manier met formules werkt.
Na invoer van enkele karakters in cel B2 en een klik op het dropdown-driehoekje verschijnt een aangepaste lijst.
Maar dan heb je nog altijd hulpkolommen C en F nodig (dat wou ik vermijden door met VBA deze gegevens in een Array te berekenen)

Ook Cel I2 heeft een hulpkolom H nodig, maar daar lukt het niet mee een gefilterde dropdown uit te rollen.
De VBA-code verwijst in regel 8 ook naar B2. Maar ook als ik dit wijzig naar I2 geeft dit niet het verhoopte resultaat.

Toch bedankt voor het meezoeken.
 
Marnik,

Ik denk dat ik je probleem eindelijk begrepen hebt, je wilt helemaal geen hulptabellen, alleen een lijst
een cel om een masker in te kunnen voeren en een cel met een drop-down box waarin de uitkomst
direct wordt geladen, dit was wat lastiger maar hierbij mijn versie van je vraag.
Dit wordt wel wat lastig als je een lange lijst met drop-downlijsten wil hebben, maar dan heb je in ieder geval een start.

Bekijk bijlage HelpMijFilterendeDropDown.xlsm

Veel Succes
 
Dankjewel Elsendoorn2134 en excuses voor mijn late reactie,

het is niet helemaal wat ik in gedachten had, maar je geeft wel een interessante workaround/andere invalshoek om het probleem te benaderen.

Ik werkte verder op andere voorbeelden waarin de lettercombinatie in de cel met de lijstvalidatie zelf wordt gezet en bij klikken op het pijltje (=Worksheet_Change) de lijst wordt aangepast op het moment dat het zich open plooit.
Dat werkt maar: met een reeks extra kolommen, problemen met foutafhandeling en met bescherming van het werkblad.

Met jouw voorbeeld zou je inderdaad een één extra cel kunnen voorzien waarin je een lettercombinatie typt, die vervolgens dient om met VBA een lijst te maken die dient als bron van meerdere andere cellen met lijstvalidatie elders op het werkblad.
Ik ga hier verder op werken, met jouw code en als er iets bruikbaars uit komt geef ik hier verslag.

Mocht intussen iemand wel een methode weten met Arrays in VBA, dan hoor ik het heel graag.
 
Laatst bewerkt:
Hey Elsendoorn2134,

met jouw voorbeeld heb ik een perfect werkend sjabloon kunnen maken. Waarvoor dank.
Alhoewel het werkt, vraag ik me toch het volgende af:

In jouw code worden de geselecteerde waarden (sResultaat) in een andere kolom gezet. Ik gebruik die andere kolom dan vervolgens om er een bereiknaam aan te geven.
Mijn vraag is dan ook, kan je van sResultaat meteen een bereiknaam maken (zonder die eerst te "printen" in een kolom) ?
Zodat je die bereiknaam meteen kan gebruiken als bron in cellen met lijstvalidatie ?

Groeten,
Marnik
 
Marnik,

De dropdown list kan op verschillende manieren worden opgebouwd, zo kun je bijvoorbeeld een range toewijzen waar de gegevens worden
opgehaald. Maar je kan ook een tekst invoeren waarin je de keuzemogelijkheden opgeeft, zolang je tussen iedere keuzemogelijkheid een
puntkomma zet zal de dropdown list deze onder elkaar weergeven.
Ga maar eens staan op cel E2 en klik op gegevensvalidatie, dan kun je zien dat er gewoon een aantal keuzemogelijkheden zijn opgegeven
en geen gebruik wordt gemaakt van een range.
In mijn macro wordt van de laatste mogelijkheid gebruikt. Mijn macro loopt door de lijst in kolom C en kijkt welke voldoet aan de
opgegeven tekst in B2. Als dit zo is wordt deze toegevoegd aan sResultaat. Daarna wordt sResultaat direct aan de dropdown list toegevoegd.
sResultaat wordt dus niet in een andere kolom geplaatst.

Veel Succes.
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan