VBA - Listbox ohne Duplikate aus Tabelle befüllen

Moderator: ModerationP

VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon fuhri1 » 03. Aug 2021, 12:33

Hallo liebe Excel-Experten,

es ist mal wieder soweit :P - Hilfe - ich brauche Eure Hilfe!

Infos zu Excel:
Ich arbeite mit Excel 2013

Info zum Tabellenaufbauen:
Ich habe eine Tabelle ("Zufahrten" = Tabelle8) welche aktuell 9 Spalten hat, diese aber ggf. sich um ein bis zwei Spalten erweitert (daher bevorzuge ich das befüllen über eine Array, da ich dann das befüllen größer 10 Spalten ermöglichen kann). Mein Tabellenkopf geht bis Zeile 7, sprich erste Einträge werden ab Zeile 8 vorgenommen. Die sichtbaren Daten die ich in der Listbox "ohne Duplikate" aufgelistet habe möchte, befinden sich in den Spalten B und C.

Info zur Datei
Alle Daten werden über Formulare ausgewählt und dann in die verschiedenen Tabellen eingetragen.

Jetzt zum Problem:
In der Tabelle ("Zufahrten" = Tabelle8) werden Daten von wiederkehrenden Einfahrten von Fahrzeugen erfasst. In der Spalte B stehen die Kennzeichen der Fahrzeuge. Da Einfahrten gleicher Fahrzeuge mehrmals täglich vorkommen, möchte ich gerne das in der Listbox "Liste_reg_Kennzeichen" alle Kennzeichen allerdings ohne Duplikate aufgelistet bekommen. Zusätzlich gibt es im Formular ein Textfeld "txt_Suche_Zufahrten" in dem ein Kennzeichen eingetragen werden kann und die Listbox sich entsprechend der Treffer reduziert.

mit folgenden Code, wird die Listbox derzeit gefüllt - allerdings mit Duplikaten :( :
Code: Alles auswählen
Sub Listbox_registrierte_Kennzeichen_laden()
Dim arr() As Variant
Dim iRow As Integer, iRowU As Integer
Dim lDatum As String
Dim i As Integer

With frm_Verwaltung.Liste_reg_Kennzeichen
    .ColumnCount = 10
    .Font.Size = 10
    .ColumnWidths = "0;80;80;0;0;0;0;0;0;0"
    .Clear
End With

If Tabelle8.Cells(Rows.Count, 1).End(xlUp).Row > 7 Then
    For iRow = 8 To Tabelle8.Cells(Rows.Count, 1).End(xlUp).Row Step 1
        ReDim Preserve arr(0 To 10, 0 To iRowU)
            If UCase(Tabelle8.Cells(iRow, 2)) Like "*" & UCase(frm_Verwaltung.txt_Suche_Zufahrten) & "*" Then
                arr(0, iRowU) = Tabelle8.Cells(iRow, 1)                                     'Datum Zufahrt
                arr(1, iRowU) = Tabelle8.Cells(iRow, 2)                                     'Kennzeichen
                arr(2, iRowU) = Tabelle8.Cells(iRow, 3)                                     'Fahrzeugtyp
                arr(3, iRowU) = Tabelle8.Cells(iRow, 4)                                     'Fahrername
                arr(4, iRowU) = Tabelle8.Cells(iRow, 5)                                     'Firma
                arr(5, iRowU) = Tabelle8.Cells(iRow, 6)                                     'Einfahrt Uhrzeit
                arr(6, iRowU) = Tabelle8.Cells(iRow, 7)                                     'Ausfahrt Uhrzeit
                arr(7, iRowU) = Tabelle8.Cells(iRow, 8)                                     'Bemerkung
                arr(8, iRowU) = Tabelle8.Cells(iRow, 9)                                     'Status Zufahrt
                arr(9, iRowU) = iRow                                             'Eintrag in Zeile (Tabelle "Zufahrten")
                iRowU = iRowU + 1
            End If
    Next iRow
frm_Verwaltung.Liste_reg_Kennzeichen.Column = arr
frm_Verwaltung.Liste_reg_Kennzeichen.Height = 280
End If
End Sub


Wie bekomme ich es hin, dass keine Duplikate in der Liste stehen ???????????????????????????????

Freue mich auf Eure Inspirationen
:roll: :roll: :roll: :roll: :roll:
fuhri1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 45
Registriert: 20. Mär 2009, 13:40
Wohnort: Neuried

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon 1Matthias » 03. Aug 2021, 12:54

Moin!
Du könntest über die Daten die Excelinteren Funktion Duplikateentfernen drüberlaufen lassen. Bin mir nicht sicher, ob man das danach wieder rückgängig machen kann. Deshalb ggf. vorher noch in einen Temp Zellbereich kopieren. Dann hast du nur individuelle Werte.

Oder du "merkst" dir, welche Kennzeichen du schon erfasst hast. Das könnte man mit einem dictionary oder einem string machen. Nach dem Eintragen in die Listbox, die Werte in den "Speicher" packen. Vor dem redim Preserve prüfst du dann, ob der nächste Wert nicht schon erfasst ist. Beim dictionary prüft du einfach mit .exists, ob der Wert schon da ist. Beim string mit instr().

VG
1Matthias
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 942
Registriert: 15. Aug 2017, 18:36

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon Ralf A » 03. Aug 2021, 13:01

...oder schau mal hier: https://www.thespreadsheetguru.com/blog ... excel-data

Also, Unikate in separaten Bereich schreiben und diesen dann auslesen....
Ciao, Ralf
Wer glaubt, für ihn persönlich würde der Bremsweg nicht als Funktion proportional zum QUADRAT der Geschwindigkeit steigen, der ist halt nicht „frei“, sondern ein Narr.
Ralf A
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1098
Registriert: 17. Mär 2018, 11:47

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon fuhri1 » 03. Aug 2021, 13:02

Hallo 1Matthias,

Danke für deine Ideen.

Das ich die Daten in eine Hilfstabelle packe habe ich auch schon gedacht, habe es aber verworfen, da die Listbox aber bei dem Change Ereignis des Textfeldes sich aktualisieren soll, habe ich bedenken, dass das so schnell problemlos funktioniert. Sprich bei jedem Buchstaben oder Zahl eines Kennzeichen, sollen die Treffer in der Listbox sich reduzieren.

Habe mich auch an einer dictionary probiert bin leider als Laie kläglich gescheidert, dass ich nicht weder den Befehl zusammen bekomme, noch weiß wo ich den einbinden soll.
:roll: :roll: :roll: :roll: :roll:
fuhri1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 45
Registriert: 20. Mär 2009, 13:40
Wohnort: Neuried

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon fuhri1 » 03. Aug 2021, 13:08

Ralf A hat geschrieben:...oder schau mal hier: https://www.thespreadsheetguru.com/blog ... excel-data

Also, Unikate in separaten Bereich schreiben und diesen dann auslesen....



Dank Dir

Wie ich Matthias gerade geschrieben habe, soll bei der Dateneingabe (nach jedem Wert Buchstabe oder Zahl = String) im Textfeld der Userform sich die Listbox entsprechend der Ergebnisse reduzieren. Ich befürchte (habe es nicht getestet) zu langsam sein wird. Werde es wohl mal testen.
:roll: :roll: :roll: :roll: :roll:
fuhri1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 45
Registriert: 20. Mär 2009, 13:40
Wohnort: Neuried

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon fuhri1 » 03. Aug 2021, 13:13

Ich habe gerade eine andere Idee :P

Eine Combobox ohne Duplikate bekomme ich hin.
Kann ich die Werte einer Combobox in eine Listbox übertragen?

Hat da jemand einen Code für mich?
:roll: :roll: :roll: :roll: :roll:
fuhri1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 45
Registriert: 20. Mär 2009, 13:40
Wohnort: Neuried

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon 1Matthias » 03. Aug 2021, 17:10

Moin!
So auf die schnelle sollte:
Me.listbox1.list = me.combobox1.list
klappen. Du müssten nur die Namen der List-/Comboboxen anpassen.

Für deine fortlaufenden Aktualisierung der Listbox bietet es sich an, die Daten am Anfang beim Laden der Userform auszulesen und in einer globalen Variabel oder einer ausgeblendeten List/Combox zu speichern. Wenn du die Daten dann brauchst, hast du sie schon da und musst sie nicht neu erstellen. Wenn sich an den Daten aber was ändert, musst du sie in deinen Speicher neu einlesen.

Falls du zum string / dictionary was brauchst, einfach melden.
VG
1Matthias
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 942
Registriert: 15. Aug 2017, 18:36

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon slowboarder » 03. Aug 2021, 17:24

fuhri1 hat geschrieben:Ich habe gerade eine andere Idee :P

Eine Combobox ohne Duplikate bekomme ich hin.
Kann ich die Werte einer Combobox in eine Listbox übertragen?

Hat da jemand einen Code für mich?

Combobox und Listbox ist im Prinzip das gleiche, zumindest was die Befüllung der Vorgabewerte (das .List) betrifft.
die Combobox kombiniert die Listbox mit einem Textfeld, so dass du nicht nur die Möglichkeit hast, einen vorgegebenen Wert aus der Liste auszuwählen, sondern auch einen Wert eingeben kannst, der nicht in der Liste steht.
wenn man diese Möglichkeit nicht nutzt, ist die Combobox eine Listbox zum Ausklappen.
dh Code, den du zum Befüllen einer Combobox verwendest, kannst du auch genauso zum Befüllen einer Listbox verwenden.

Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 28335
Registriert: 18. Apr 2009, 13:33

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon knobbi38 » 03. Aug 2021, 18:52

Hallo

sag mal, was möchtest du eigentlich mit der Bedingung
Code: Alles auswählen
If UCase(Tabelle8.Cells(iRow, 2)) Like "*" & UCase(frm_Verwaltung.txt_Suche_Zufahrten) & "*" Then

erreichen? Like "*" ist doch Nonsense, weil immer WAHR.
knobbi38
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 3405
Registriert: 02. Jul 2015, 14:23

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon fuhri1 » 03. Aug 2021, 18:58

1Matthias hat geschrieben:Moin!
So auf die schnelle sollte:
Me.listbox1.list = me.combobox1.list
klappen. Du müssten nur die Namen der List-/Comboboxen anpassen.

Für deine fortlaufenden Aktualisierung der Listbox bietet es sich an, die Daten am Anfang beim Laden der Userform auszulesen und in einer globalen Variabel oder einer ausgeblendeten List/Combox zu speichern. Wenn du die Daten dann brauchst, hast du sie schon da und musst sie nicht neu erstellen. Wenn sich an den Daten aber was ändert, musst du sie in deinen Speicher neu einlesen.

Falls du zum string / dictionary was brauchst, einfach melden.
VG


Danke Matthias 8-) werde es testen und mich gegebenenfalls noch einmal melden.
:roll: :roll: :roll: :roll: :roll:
fuhri1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 45
Registriert: 20. Mär 2009, 13:40
Wohnort: Neuried

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon fuhri1 » 03. Aug 2021, 18:59

slowboarder hat geschrieben:
fuhri1 hat geschrieben:Ich habe gerade eine andere Idee :P

Eine Combobox ohne Duplikate bekomme ich hin.
Kann ich die Werte einer Combobox in eine Listbox übertragen?

Hat da jemand einen Code für mich?

Combobox und Listbox ist im Prinzip das gleiche, zumindest was die Befüllung der Vorgabewerte (das .List) betrifft.
die Combobox kombiniert die Listbox mit einem Textfeld, so dass du nicht nur die Möglichkeit hast, einen vorgegebenen Wert aus der Liste auszuwählen, sondern auch einen Wert eingeben kannst, der nicht in der Liste steht.
wenn man diese Möglichkeit nicht nutzt, ist die Combobox eine Listbox zum Ausklappen.
dh Code, den du zum Befüllen einer Combobox verwendest, kannst du auch genauso zum Befüllen einer Listbox verwenden.

Gruß Daniel


Auch dir Danke für die Info, Daniel 8-)
Auf die Community ist verlass
:roll: :roll: :roll: :roll: :roll:
fuhri1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 45
Registriert: 20. Mär 2009, 13:40
Wohnort: Neuried

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon fuhri1 » 03. Aug 2021, 19:04

knobbi38 hat geschrieben:Hallo

sag mal, was möchtest du eigentlich mit der Bedingung
Code: Alles auswählen
If UCase(Tabelle8.Cells(iRow, 2)) Like "*" & UCase(frm_Verwaltung.txt_Suche_Zufahrten) & "*" Then

erreichen? Like "*" ist doch Nonsense, weil immer WAHR.


Hey Knobbi,

Eine berechtigte Frage :roll:

Habe diese Anweisung auch in anderen Datei, wie z.B. einem Telefonbuch.
Beim ersten aufrufen der Anweisung sollen alle Datensätze geladen werden, da ja kein String im Suchfeld eintragen ist.
Sobald Werte im Suchfeld eingetragen werden, wird die Anweisung erneut ausgeführt und des werden auch Teilstring, die im Ergebnis enthalten sind gesucht, da die * gesetzt sind. Um so genauer die Sucheingaben gemacht werden um so geringe werden die Suchergebnisse in der Listbox.

Wenn du mit aber sagst, dass das LIKE die Sternchen automatisch ersetzt, hab ich wieder was gelernt :lol:
:roll: :roll: :roll: :roll: :roll:
fuhri1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 45
Registriert: 20. Mär 2009, 13:40
Wohnort: Neuried

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon slowboarder » 03. Aug 2021, 19:07

knobbi38 hat geschrieben:Hallo

sag mal, was möchtest du eigentlich mit der Bedingung
Code: Alles auswählen
If UCase(Tabelle8.Cells(iRow, 2)) Like "*" & UCase(frm_Verwaltung.txt_Suche_Zufahrten) & "*" Then

erreichen? Like "*" ist doch Nonsense, weil immer WAHR.


da ist nicht Like "*" sondern Like "*" & UCase(frm_Verwaltung.txt_Suche_Zufahrten) & "*"
also zuerst wird der "*" mit dem nachfolgenden Text verkettet und das LIKE dann mit diesem Gesamttext gebildet.

es wird hier geprüft, ob der Text aus dem Steuerelement frm_Verwaltung in der Zelle enthalten ist, dh die Zelle darf neben diesem Text noch weitere Zeichen davor oder danach enthalten, damit der Ausdruck wahr wird.

und nein, LIKE setzt die Sternchen nicht automatisch. Ohne die Sternchen entspricht das LIKE einem "="

Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 28335
Registriert: 18. Apr 2009, 13:33

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon fuhri1 » 03. Aug 2021, 19:17

slowboarder hat geschrieben:
knobbi38 hat geschrieben:Hallo

sag mal, was möchtest du eigentlich mit der Bedingung
Code: Alles auswählen
If UCase(Tabelle8.Cells(iRow, 2)) Like "*" & UCase(frm_Verwaltung.txt_Suche_Zufahrten) & "*" Then

erreichen? Like "*" ist doch Nonsense, weil immer WAHR.


da ist nicht Like "*" sondern Like "*" & UCase(frm_Verwaltung.txt_Suche_Zufahrten) & "*"
also zuerst wird der "*" mit dem nachfolgenden Text verkettet und das LIKE dann mit diesem Gesamttext gebildet.

es wird hier geprüft, ob der Text aus dem Steuerelement frm_Verwaltung in der Zelle enthalten ist, dh die Zelle darf neben diesem Text noch weitere Zeichen davor oder danach enthalten, damit der Ausdruck wahr wird.

und nein, LIKE setzt die Sternchen nicht automatisch. Ohne die Sternchen entspricht das LIKE einem "="

Gruß Daniel



Na dann hab ich doch alles richtig gemacht, wenn ich Dich recht verstehe?! :badgrin:
:roll: :roll: :roll: :roll: :roll:
fuhri1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 45
Registriert: 20. Mär 2009, 13:40
Wohnort: Neuried

Re: VBA - Listbox ohne Duplikate aus Tabelle befüllen

Beitragvon knobbi38 » 03. Aug 2021, 19:18

Hallo,

sorry, da hatte ich übersehen, daß in der Bedingung direkt auf eine Textbox zugegriffen wird. Trotzdem würde ich das erste * nicht setzen, was dann aber die Logik etwas verändert.

Wenn du so den Programmablauf gestaltest, muß du natürlich immer wieder aus der Tabelle neu einlesen und auch das Array neu aufbauen. Stellt sich die Frage, welche Matthias auch schon angeschnitten hat, ob während die UF angezeigt wird, überhaupt Änderungen in der Quelltabelle gemacht werden. Normalerweise würde man die UF modal betreiben und nur noch aus dem Array heraus die Listbox nach jeder neuen Eingabe neu laden.

Gruß Ulrich
knobbi38
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 3405
Registriert: 02. Jul 2015, 14:23

Nächste

Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: snb und 7 Gäste