Userform Listbox vullen en filteren

[Hela1966]

Beste forumleden,

Ik zit zelf in een "loop" geloof ik.....heb dringend jullie hulp nodig.

Wat ik probeer te bereiken is:

1 - het vullen van een listbox bij Initialize met alle Naam/adres/pc/plaats - waardes welke voldoen aan de geselecteerde option button (default waarde is Active)

2 - Listbox te filteren op data van kolom "city" obv hetgeen in textbox Search wordt ingevoerd (al is het enkel 1 letter)

3 - bij dubbelklikken op een waarde uit de listbox de bijbehorende data in de overige textboxen te plaatsen (details dus)

Ik stuur het bestand mee, de data staat op tabblad "Joined Data" en wordt samengesteld uit de 2 tabellen op de andere bladen.

Kunnen jullie me verder helpen?

Alvast dank voor het meedenken.

 

[Jack Nouws]

Heb alvast een stukje gedaan

 

[VenA]

Met dezelfde methode maar iets uitgebreider.

 

[Hela1966]

Dag Jack en VenA,

Dank jullie alvast hiervoor, ik ga dit eerst even analyseren voordat ik met meer vragen kom.

Wordt vervolgd....

Gr,
Hela

 

[Hela1966]

Oke, ik heb me even vastgebeten in de variant van VenA.

Het bestand dat ik voor het testen gebruik bevat maar een gedeelte van alle kolommen die in werkelijkheid in een (idioot-groot) bestand staan welke ik via een externe bron dagelijks krijg aangeleverd.

Ik zal waarschijnlijk dynamische namen moeten gaan creëren om het volledige bereik te kunnen bevatten (dat was ook de reden waarom ik de for/next loop met wsLR had bedacht)

Het gaat in totaal om meer dan 160 kolommen en 1700 rijen. De posities van de kolommen kunnen plots wijzigen (dus vandaag staat het ID in kolom B en maar dat kan morgen in kolom F staan)
En dat is nog niet alles....sommige kolommen die ik nodig heb komen op alle tabbladen voor, andere maar op bijv 3.

Vandaar dat ik het tabblad Joined Data gebruik. Hierop staan alle kolommen die ik nodig heb voor het userform en voor wat draaitabellen.
Het gaat dus wel om zo'n 1700 rijen en bijna 100 kolommen.

Dat gezegd hebbende (hoop niet dat jullie nu denken....had dat maar EERDER gezegd) wil ik graag begrijpen wat de code doet en zie dat de Listbox nu alle 13 aanwezige kolommen uit het bestand weergeeft.
Een aanpassing naar het gewenst aantal (7 kolommen) kon ik uiteraard bewerkstelligen in de code:

Huidig:

ar = Sheets("Joined Data").Columns(21).SpecialCells(2).Offset(1).SpecialCells(2).Resize(, 13)

Na aanpassing:

ar = Sheets("Joined Data").Columns(21).SpecialCells(2).Offset(1).SpecialCells(2).Resize(, 7)

Dat heeft gevolgen voor het lbSitenac_Click Event, daar die daarnaar verwijst:

Private Sub lbSitenac_Click()
With lbSitenac
  tbPhone = .Cells(7)
  tbEmail = .Column(8)
  tbID = .Column(0)
  tbEv = .Column(10)
  tbReg = .Column(12)
  tbType = .Column(11)
End With
End Sub

Nu ik besef dat het misschien beter is met Named Ranges te gaan werken zal de verwijzing ook aangepast moeten worden (lees: help help help)

En dan is daar nog die verdraaide seach-textbox! Nu is het zo dat de gevonden "city" in de Listbox wordt aangeduid, is het mogelijk dat alleen de cities welke voldoen aan de search-waarde verschijnen?
Ook weer filtering dus, maar dan op de waarde uit de searchbox.

Als ik dit allemaal naar wens werkend krijg, is het de bedoeling om het Userform uit te breiden met Multipages waarop per page een aantal textboxes staan met specifieke gegevens uit een aantal kolommen.
Ik hoop dat dat me wel gaat lukken zodra ik dit onder de knie heb...

Hopelijk zijn jullie bereid me verder te helpen, ik leer hier zoveel van.

Weer dank,

Hela

 

[snb]

Dit lijkt me typisch een dictionary klusje.

Verwijder eerst werkblad 4
Verwijder eerst de lege rij 1 in ieder werkblad (die daar überhaupt niet had mogen staan )

Currentregion is een van de meest dynamische concepten in VBA (in tegenstelling tot named ranges)

Gegevens in tekstvakken plaatsen kan met:

Private Sub lbSitenac_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  If lbSitenac.ListIndex > -1 Then
    For j = 1 To 6
      Me("tb" & Choose(j, "phone", "email", "ID", "EV", "Reg", "Type")) = lbSitenac.Column(Choose(j, 7, 8, 0, 10, 12, 11))
    Next
  End If
End Sub

Maar als we de columnwidths van de listbox aanpassen krijgen we daarin hetzelfde te zien, zodat die dubbelklik overbodig wordt.

 

[Hela1966]

Dag SNB,

Ik zie je reactie net pas en ja....je hebt gelijk... die eerste regel zou er niet moeten staan maar stond al in het feitelijke bestand dus daar heb ik mee zitten fröbelen.

Leuk stukje code geef je weer weg: kort maar krachtig.

Het is nu te laat (of te vroeg?) om ermee aan de slag te gaan, maar dat doe ik zeker morgenavond.

Dank weer en ik laat van me horen (euh lezen)

Goeienacht!

Hela

 

[snb]

Het lijkt me handig de volledige 'database' op te slaan in een niet-zichtbare listbox in het userform.
De gefilterde database kan steeds getoond worden in lbsitenac.
De namen van de velden heb ik ook maar toegevoegd aan lbsitenac.

 

[Hela1966]

Dag SNB,

Ik heb al heeeel wat uren (lees dagen) zitten puzzelen op je code, maar het gaat m'n pet(je) echt te boven hoor.

Ik zie wat de code doet door met de debugger stap voor stap te doorlopen en te hooveren (hoe heet dat in NL?) over de code en heb voor mezelf de 2e listbox, waarin de index getransponeerd wordt gebruikt, even zichtbaar gemaakt.

Aan de andere kant heb ik het op m'n werk ook voor elkaar gekregen dat ik via een sql-server de beschikking heb over de volledige database! Joepie, alles op 1 tabblad en easy om te verversen.
Gelijk maar een studie (want dat is dit voor mij) gemaakt van het maken een query zodat ik nu beschik over 1 nieuw tabblad met daarin alle gewenste kolommen (82 tot nu toe), in een praktische volgorde EN logisch gesorteerd.

On with the Userform....hoe kan ik nu het best verder gaan?

Mijn doel is nog steeds om een Userform te maken dat bij Initialize de Listbox vult met alle waardes uit 10 kolommen (niet allemaal opeenvolgend, dus bijv kolom B, C, D, F, G, I, K, A, M, E)

- Bij het klikken op 1 van de regels in de Listbox alle textboxen vullen met de waardes uit alle kolommen (euh ja ik zal er 82 aanmaken...)
- Ook een mogelijkheid om dmv het selecteren van 1 van 5 optionbuttons aan te geven op welke kolom (bijvoorbeeld op City) de Searchbox moet zoeken zodat het aantal regels in de Listbox wordt beperkt.

Het laatste kan wellicht ook met een Combobox, maar ik ben daar niet zo'n fan van. Zie persoonlijk liever de opties op het scherm en scheelt een klik.

Ooh...voor ik het vergeet. Rowsource, Listfillrange, additem....etc zal ik niet meer gebruiken na het lezen van diverse nadelen daarvan op diverse fora EN iets met VBA voor Smarties! Verhelderend!

Wil/kun je me nog verder helpen?

Gr,
Hela

 

[snb]

Mijn doel is nog steeds om een Userform te maken dat bij Initialize de Listbox vult met alle waardes uit 10 kolommen (niet allemaal opeenvolgend, dus bijv kolom B, C, D, F, G, I, K, A, M, E)

Als je de gegevens van alle kolommen zichtbaar wil maken in tekstvakken, lijkt het me voor de hand liggend eerst alle gegevens, zoals ze in het werkblad staan in een verborgen listbox te zetten.
De kolomvolgorde kun je instellen met de methode Index.
De gegevens met aangepaste kolomvolgorde kun je in een tweede, zichtbare listbox tonen.

Private Sub Userform_Initialize()
   listbox1.list=sheet1.cells(1). currentregion.value     ' 86 kolommen zoals in het werkblad
   [COLOR="#FF0000"]listbox2.[/COLOR]list=application.index(listbox1.list,evaluate("row(1:" & listbox1.listcount &")"),array(2,3,4,6,7,9,11,1,13,5))  ' kolom B, C, D, F, G, I, K, A, M, E
End Sub

Stel bij de eigenschappen van listbox2 in:
columncount 10
columnwidth 30;30;30;30;30;30;30;30;30;30

Als vervolgens in listbox2 een selectie wordt gemaakt, worden de overeenkomstige gegevens uit listbox1 ingelezen in de 86 tekstvakken.:

Private sub Listbox2_Change()
  if listbox2.listindex>-1 then 
     listbox1.listindex=listbox2.listindex

     for j=0 to ubound(listbox1.list,2)
       me("tekstvak" & j)=listbox1.column(j)   ' tekstvak0 t/m tekstvak85
     next
  end if
End Sub

PS. Gelukkig vraag je niet wat ik van 86 tekstvakken vind....

 

[Hela1966]

Hoi SNB,

Oke, daar ga ik mee aan de slag. Als ik het zo lees klinkt het zo heel logisch, moet lukken.

Haha, al die tekstvakken staan verspreid op het formulier maar keurig geordend in diverse tabbladen hoor.

Ik laat weer van me horen!

Bedankt weer!

 

[snb]

Ik had 1 s teveel in de code staan:

Private Sub Userform_Initialize()
   listbox1.list=sheet1.cells(1). currentregion.value     ' 86 kolommen zoals in het werkblad
   listbox2.list=application.index(listbox1.list,evaluate("ro[COLOR="#FF0000"]w([/COLOR]1:" & listbox1.listcount &")"),array(2,3,4,6,7,9,11,1,13,5))  ' kolom B, C, D, F, G, I, K, A, M, E
End Sub

 

[jkpieterse]

Dit klinkt als een project dat in MSAccess duizend keer eenvoudiger is

 

[snb]

Die formule ken ik nog niet

 

[Hela1966]

Goedeavond experts!

@jkpieterse:
Daar heeft u eigenlijk helemaal gelijk in. Als ik OOIT vba onder de knie krijg, wil ik me daar ook graag in gaan verdiepen.
Tot die tijd....is Excel m'n grote vriend. Er is nog zoveel te ontdekken voor me.
Uw website heb ik ook regelmatig geraadpleegd. Dank voor uw reactie en het delen van uw kennis (slicers en pivottables zijn zoveel leuker geworden )

@snb:
Allememachhies, met je code heb je je lijfspreuk "dat kan korter' volgens mij weer eer aangedaan. Knap staaltje inzicht. Ik heb de code geprint (ja dat doen mensen soms nog wel eens...) en ben nu bezig met het "ontleden".

Het werkt in ieder geval helemaal zoals ik het bedoelde dus ik zal ook maar beginnen met dat stapeltje tekstvakken erin te zetten en wat beveiliging voor de gebruikers.

Mezelf (en de gebruikers kennende) zal dit ook wel weer een typische "kerstboom" worden. De lichtjes hangen er nu in, geheid vragen ze volgende week om "slingers" en kan ik als gevolg mijn volgende vraag op dit behulpzame forum posten.

Enorm bedankt voor het geduld en de bijdrage aan dit Userform (mijn eerste)....hoewel er weinig van code meer van mij inzit!

Hartelijke groet,

Hela

 

[snb]

@hela

Wellicht ten overvloede: zorg ervoor dat de namen van de tekstvakken overeenkomen met de nummers van de kolommen van listbox1 ( = de range van het werkblad met de gegevens).

 

[Hela1966]

Daar ben ik best blij mee, scheelt me wat tesktvakken-hernoemen.

Mag ik nog een toelichting vragen?

Waarom "16" in lblRefrDate, waarvan de caption property "updated on" is

lblRefrDate.Caption = Left(lblRefrDate.Caption, 16) & Date

Moet ik dat lezen als: het aantal karakters van dit label, beginnend vanaf links + een paar spaties voor leesbaarheid & Date?

 

[snb]

Exact.
Daarmee zorgen we ervoor dat achter de vaste tekst (van 16 karakters) de huidige datum komt te staan.

Mij leek hiervoor een label (dat door de gebruiker niet te wijzigen is) voor de hand liggender dan een tekstvak.

NB de lbSitenac_Change procedure kan natuurlijk veel eenvoudiger:

Private Sub lbSitenac_Change()
  ListBox1.Value = lbSitenac.Column(7)

  For j = 0 To UBound(ListBox1.List, 2)
    Me("textbox" & j) = ListBox1.Column(j)
  Next
End Sub

Deze code staat nu ook in het aangepaste bestand.

Ook heb ik ter illustratie een gebeurtenis-klassemodule ingevoegd voor de opties in Frame1.

 

[snb]

In plaats van de arraylistmethode om een unieke gesorteerde lijst met plaatsnamen te krijgen heb ik een draaitabel in de verborgen kolom 105 (DA) gezet.

 

[Hela1966]

Owowow SNB,

Ik zie nu dat mijn reactie van vlak daarna niet geplaatst is. Excuses. Het was iets in de trend van...mij hoor je even niet, dit moet ik gaan ontleden.

Sinds die tijd heb ik me gestort op het begrijpen van dit alles. Ben nu eenmaal een type dat het graag snapt. En ole daar kwam voor het eerst een klassemodule voorbij.
Welja die gooien we er ook nog even in.....beetje rustig het boeltje onder de knie krijgen is er niet echt bij.
Enfin....ik snap wat het doet en heb wat geoefend. Dat is verdraaid handig!

Ondertussen zijn de wensen voor deze "kerstboom" gegroeid en heb ik zowaar zelf het meeste voor elkaar gekregen.
Op m'n werk heb ik ook wat ondersteuning gekregen in de vorm van een hapklare database dus het aan elkaar knopen van alle elementen kan ik nu gaan beginnen.

Ik zal vast terugkomen met meer vragen, maar ga hiermee eerst even werken om te zien waaraan vervolgens behoefte is.

Hartelijk dank voor alle suggesties en de code, het levert me dagelijks tijdwinst op.

Ik zal deze sluiten en nieuwe vragen in een nieuwe post plaatsen.

Groetjes,

Hela