Combobox Liste prüfen

[Hans.Alborg]

Hallo,

<Excel 2007>

ich fülle Comboboxen mit einer Liste (Zellbereich z.B. A1.....A20).
Vorher befindet sich bereits ein Eintrag in der Box (also sichtbar, und
ggf. aus einer "alten" Liste).

Ich möchte jetzt prüfen, ob der Eintrag in der neuen Liste vorkommt.
Wenn nicht, dann Listindex = 4 (Beispiel), sonst kein Listindex, um den
Eintrag stehen zu lassen.

Da es viele Range- Bereiche für diese Listen gibt möchte ich direkt
immer die Combobox- Liste abfragen.
Aber mit InStr geht das scheinbar nicht oder ich bekomme die Syntax
nicht hin.

a = InStr(Fm.CB_SZa.List,"Eintrag")
IF a > 0 THEN...

Gibt es da eine Methode?

TIA,
Hans

[Claus Busch]

Hallo Hans,

ich würde die Werte der Combobox in dem zu übergebenden Zellbereich
zählen:

Private Sub UserForm_Initialize()
Dim strRS As String
Dim myRng As Range
Dim i As Integer

With Sheets("Tabelle1")
Set myRng = .Range("A1:A20")
strRS = .Name & "!" & myRng.Address
End With
With Me.ComboBox1
For i = 0 To .ListCount - 1
If Application.CountIf(myRng, .List(i)) > 0 Then
'Dein Code
MsgBox "Schon vorhanden"
End If
Next
.RowSource = strRS
End With
End Sub


Mit freundlichen Grüßen
Claus
--
Windows10
Office 2016

[Ulrich Möller]

Hallo Hans,

Comboboxen und Listboxen mußt du sequentiell durchsuchen, deshalb
funktioniert "instr" hier so nicht. Als Ersatz eine kleine VBA-Funktion:

Function GetIndex( _
ByVal SearchList As Variant, _
ByVal SearchItem As String, _
Optional ByVal SearchColumn As Long = 0) As Long
Dim cnt As Long, idx As Long, i As Long

idx = -1
cnt = UBound(SearchList) + 1

For i = 0 To cnt
If StrComp(SearchList(i, SearchColumn), SearchItem,
vbBinaryCompare) = 0 Then
idx = i
Exit For
End If
Next

GetIndex = idx
End Function

Aufruf z.B.:

if GetIndex(Listbox.List, "Hallo") = -1 then
' nicht gefunden
else
' Rückgabewert gleich Listindex in der Combo- oder Listbox
end if

Ulrich

[Ulrich Möller]

Am 29.07.2017 um 15:58 schrieb Ulrich Möller:
> cnt = UBound(SearchList) + 1

fehlerhafte Zeile bitte korrigieren nach

cnt = UBound(SearchList)

Ulrich

[Hans.Alborg]

Hi Claus,

Claus Busch schrieb:

> ich würde die Werte der Combobox in dem zu übergebenden Zellbereich
> zählen:
>

> ...

>
> With Sheets("Tabelle1")
> Set myRng = .Range("A1:A20")
> strRS = .Name & "!" & myRng.Address
> End With
> With Me.ComboBox1
> For i = 0 To .ListCount - 1
> If Application.CountIf(myRng, .List(i)) > 0 Then
> 'Dein Code
> MsgBox "Schon vorhanden"
> End If
> Next
> .RowSource = strRS
> End With
> End Sub

Ich glaube Du vergleichst hier den Zellbereich mit der alten Comboboxliste.

Es gibt einen "noch vorhandenen" Eintrag in der Combobox und wenn die
neue Liste erstellt ist bleibt der ja erstmal stehen.
Kommt er auch in dieser Liste vor soll er bleiben, wenn nicht weise ich
der Combobox per Listindex einen anderen Text zu der dann sofort in der
Box erscheint. Das ist eigentlich alles!

Immerhin interessant daß Du die Combobox mit ".RowSource = strRS" füllst.
Leider finden die Range- Zuweisungen auch an mehreren Stellen im Modul
statt (8x?). Da wäre es besser eine Subroutine oder wie Ulrich
vorschlägt, eine Funktion zu nutzen.

Ich hab im Netz ein Beispiel gefunden, ".ComboBox1.List" direkt per
Schleife abzufragen.

Halt leider immer ne Schleife.

Hans

[Hans.Alborg]

Hallo Ulrich,

Ulrich Möller schrieb:

> Function GetIndex( _
> ByVal SearchList As Variant, _
> ByVal SearchItem As String, _
> Optional ByVal SearchColumn As Long = 0) As Long
> Dim cnt As Long, idx As Long, i As Long
>
> idx = -1
> cnt = UBound(SearchList)
>

> For i = 0 To cnt
> If StrComp(SearchList(i, SearchColumn), SearchItem, vbBinaryCompare)
> = 0 Then
> idx = i
> Exit For
> End If
> Next
>
> GetIndex = idx
> End Function
>
> Aufruf z.B.:
>
> if GetIndex(Listbox.List, "Hallo") = -1 then
> ' nicht gefunden
> else
> ' Rückgabewert gleich Listindex in der Combo- oder Listbox
> end if

Ich muß das erstmal probieren, kann mir aber jetzt nicht vorstellen wie
ich den Aufruf durchführe ohne die wechselnden Rangebereiche einzugeben.
Ist zwar immer dieselbe Spalte, dort aber verschiedene Zeilen.

Nochmal: in der Combobox steht ein Text, i.d.R. aus einer Liste gewählt,
egal.
Es wird eine neue Liste zugewiesen. Enthält auch diese den Text passiert
nichts weiter, der Text wird weiter angezeigt.
Gibt's den Text in der neuen Liste nicht, bestimme ich einen List-Index
meiner Wahl, was dann diesen "Text Nr.x" in die Box schreibt.

Um die wechselnden Range- Bereiche außen vor lassen zu können, werde ich
versuchen den aktuellen Text in der Combobox mit deren Liste direkt zu
vergleichen. Und mir fällt es leichter, das in eine Unterroutine zu packen.
Ist denn eine Funktion schneller (beides Schleifen)?

Was Anderes wäre es, wenn ich die Texte des Range- Bereichs in eine
Stringkette bekäme (ohne Schleife). Dann wäre doch InStr möglich?

Hans

[Ulrich Möller]

Wie hatten an anderer Stelle mal beispielhaft mit einem Dictionary
Object einen "Unique Store" realisiert. Vielleicht wäre es einfachen,
diesen "Unique Store" zu verwenden, in diesen deine Texte zu speichern
und dann dann die Listbox damit zu synchronisieren. Hört sich jetzt
kompliziert an, wäre aber relativ einfach umzusetzen.

>
> Um die wechselnden Range- Bereiche außen vor lassen zu können, werde
> ich versuchen den aktuellen Text in der Combobox mit deren Liste
> direkt zu vergleichen. Und mir fällt es leichter, das in eine
> Unterroutine zu packen.
> Ist denn eine Funktion schneller (beides Schleifen)?
>
> Was Anderes wäre es, wenn ich die Texte des Range- Bereichs in eine
> Stringkette bekäme (ohne Schleife). Dann wäre doch InStr möglich?

Mir ist keine Möglichkeit bekannt, für diese Art von Listboxen ohne
Schleife mit einem Vergleich auf die Existenz eines vorhandenen Wertes
zu prüfen. Um eine String für den Vergleich mit Instr() zu bilden, wäre
ebenso eine Schleife notwendig, also kein Vorteil.

Ulrich

[Hans.Alborg]

Ulrich Möllerschrieb :
> Hans.Alborg:

>> Nochmal: in der Combobox steht ein Text, i.d.R. aus einer Liste
>> gewählt, egal.
>> Es wird eine neue Liste zugewiesen. Enthält auch diese den Text
>> passiert nichts weiter, der Text wird weiter angezeigt.
>> Gibt's den Text in der neuen Liste nicht, bestimme ich einen
>> List-Index meiner Wahl, was dann diesen "Text Nr.x" in die Box schreibt.

Das soll passieren, eigentlich was ganz Einfaches :-(

> Wie hatten an anderer Stelle mal beispielhaft mit einem Dictionary
> Object einen "Unique Store" realisiert. Vielleicht wäre es einfachen,
> diesen "Unique Store" zu verwenden, in diesen deine Texte zu speichern
> und dann dann die Listbox damit zu synchronisieren. Hört sich jetzt
> kompliziert an, wäre aber relativ einfach umzusetzen.

Ich weis, und wenn ich jetzt daran ginge müßte ich mich _wieder_
einlesen. Bin halt kein Profi. Da ist es für mich gut, einfachen Code zu
nutzen den ich später sofort kapieren kann.

>> Um die wechselnden Range- Bereiche außen vor lassen zu können, werde

>> ich versuchen...

>> Was Anderes wäre es, wenn ich die Texte des Range- Bereichs in eine
>> Stringkette bekäme (ohne Schleife). Dann wäre doch InStr möglich?

> Mir ist keine Möglichkeit bekannt, für diese Art von Listboxen ohne

> Schleife...

Ich schrieb nicht "in den Listboxen". Wenn es bei der Range- Zuweisung
ginge einen String aus allen Zellinhalten zu machen ginge InStr.
Das würde es wieder attraktiv machen die Rangezuweisungen mit in die
Unterroutine (bzw. als Parameter in die Funktion) zu transportieren.
Dazu müßte dann noch der Listindex- Wert kommen, also 2 Parameter.
Wie gesagt, wenn das ohne Schleife ginge -mit dem Rangefeld.

So, jetzt -früher Morgen, Tabletten wg Erkältung wirken (das sind
_meine_ Parameter) mach' ich mal meine Mappe auf...

Hans

[Claus Busch]

Hallo Hans,

Am Sun, 30 Jul 2017 07:06:36 +0200 schrieb Hans.Alborg:

> Ich schrieb nicht "in den Listboxen". Wenn es bei der Range- Zuweisung
> ginge einen String aus allen Zellinhalten zu machen ginge InStr.
> Das würde es wieder attraktiv machen die Rangezuweisungen mit in die
> Unterroutine (bzw. als Parameter in die Funktion) zu transportieren.
> Dazu müßte dann noch der Listindex- Wert kommen, also 2 Parameter.
> Wie gesagt, wenn das ohne Schleife ginge -mit dem Rangefeld.

probiere mal:

vardata = Range("A1:A20")
With Application
vartmp = .Transpose(.Index(vardata, 0, 1))
myStr = Join(vartmp, ",")
End With

[Claus Busch]

Hallo Hans,

Am Sun, 30 Jul 2017 09:26:27 +0200 schrieb Claus Busch:

> vardata = Range("A1:A20")
> With Application
> vartmp = .Transpose(.Index(vardata, 0, 1))
> myStr = Join(vartmp, ",")
> End With

du kannst alle Werte einer ComboBox auf einen Rutsch in ein
eindimensionales Array einlesen mit z.B.:
varCombo = ComboBox1.List
Ebenso kannst du einer ComboBox auf einen Rutsch ein Array zuweisen:
ComboBox1.List=MeinArray

Du könntest also das Array varCombo mit InStr durchlaufen und wenn du
einen Wert behalten möchtest varTmp neu dimensionieren und diesen Wert
hinzufügen und nach Durchlauf der Schleife der Combobox diese Werte
zuweisen mit
ComboBox1.List = varTmp

[Hans.Alborg]

Hallo Claus,

Claus Busch schrieb:

> Claus Busch:
>
>> vardata = Range("A1:A20")
>> With Application
>> vartmp = .Transpose(.Index(vardata, 0, 1))
>> myStr = Join(vartmp, ",")
>> End With
>
> du kannst alle Werte einer ComboBox auf einen Rutsch in ein
> eindimensionales Array einlesen mit z.B.:
> varCombo = ComboBox1.List
> Ebenso kannst du einer ComboBox auf einen Rutsch ein Array zuweisen:
> ComboBox1.List=MeinArray
>

> Du könntest also das Array varCombo mit InStr durchlaufen...

Das ist sehr interessant und könnte tatsächlich die Lösung ohne Schleife
sein!

> ...und wenn du

> einen Wert behalten möchtest varTmp neu dimensionieren und diesen Wert
> hinzufügen und nach Durchlauf der Schleife der Combobox diese Werte
> zuweisen mit
> ComboBox1.List = varTmp

Klingt wieder nicht nach dem was ich beabsichtige.

Wenn ein Combobox.text aus einer alten Liste in der Box steht und diese
Box eine neue Liste bekommt die diesen Text AUCH enthält, soll er in der
Box stehenbleiben.
Ansonsten soll ein Text meiner Wahl (aus der neuen Liste) per ListIndex
in der Combobox erscheinen.

Ich habe nicht vor, einen Wert/Text zu behalten und in die neue Liste
neu aufzunehmen.

Trotzdem ist der Ansatz mit dem Array sehr spannend.
Im Moment baue ich erstmal meine Idee mit der Schleife weiter. Dann habe
ich erstmal ein Schema wie der Code allgemein ablaufen soll.
Leider hackt es z.Z. mit dem Laufzeitfehler 1004:
' ----------------------------------------------------
Set RnList = shAu.Range(Cells(i, 22), Cells(i, 30))

geht ohne Fehlermeldung, ist aber sinnlos, weil falsches Blatt,

test1 = shPrg.Name

"shPrg" ist das richtige Blatt, der Name wird richtig erkannt, und

Set RnList = shPrg.Range(Cells(i, 22), Cells(i, 30))

bringt den Laufzeitfehler. "i" ist immer 44. In den Zellen um Spalte 30
könnte evtl. nichts enthalten sein, kann das der Grund sein?
' --------------------------------------------------
Da bin ich jetzt am suchen...

Hans

[Ulrich Möller]

Hallo Hans,

Am 30.07.2017 um 07:06 schrieb Hans.Alborg:
> Ulrich Möller schrieb :

>> Hans.Alborg:
>
>>> Nochmal: in der Combobox steht ein Text, i.d.R. aus einer Liste
>>> gewählt, egal.
>>> Es wird eine neue Liste zugewiesen. Enthält auch diese den Text
>>> passiert nichts weiter, der Text wird weiter angezeigt.
>>> Gibt's den Text in der neuen Liste nicht, bestimme ich einen
>>> List-Index meiner Wahl, was dann diesen "Text Nr.x" in die Box
>>> schreibt.
>
> Das soll passieren, eigentlich was ganz Einfaches :-(
>
>> Wie hatten an anderer Stelle mal beispielhaft mit einem Dictionary
>> Object einen "Unique Store" realisiert. Vielleicht wäre es einfachen,
>> diesen "Unique Store" zu verwenden, in diesen deine Texte zu speichern
>> und dann dann die Listbox damit zu synchronisieren. Hört sich jetzt
>> kompliziert an, wäre aber relativ einfach umzusetzen.

ich habe dir mal eine Beispielmappe zum herunterladen erstellt: cbliste.xlms
https://1drv.ms/f/s!Ank2PUnOf8USghPSfXtJQKKS2-56
<https://1drv.ms/f/s%21Ank2PUnOf8USghPSfXtJQKKS2-56>

Als Beispiel habe ich eine Kombobox gewählt und beim Click auf
aktualisieren werden die Werte des benannten Bereiches in die Kombobox
eingetragen, wobei nur eindeutige Werte übernommen werden.
In diesem Beispiel werden nicht mehr vorhandene Werte nicht gelöscht!
Hierfür müßte man das Beispiel entsprechend erweitern.

Ulrich

[Hans.Alborg]

Hallo Ulrich,

Ulrich Möller schrieb:

> ich habe dir mal eine Beispielmappe zum herunterladen erstellt:
> cbliste.xlms
> https://1drv.ms/f/s!Ank2PUnOf8USghPSfXtJQKKS2-56
> <https://1drv.ms/f/s%21Ank2PUnOf8USghPSfXtJQKKS2-56>
>
> Als Beispiel habe ich eine Kombobox gewählt und beim Click auf
> aktualisieren werden die Werte des benannten Bereiches in die Kombobox
> eingetragen, wobei nur eindeutige Werte übernommen werden.
> In diesem Beispiel werden nicht mehr vorhandene Werte nicht gelöscht!
> Hierfür müßte man das Beispiel entsprechend erweitern.

Das ist doch aber einiger Aufwand (viele "Baustellen"), und eine
Schleife gibts auch. Außerdem etliche Sachen mit denen ich nicht umgehen
kann. Namen für Rangebereiche mag ich nicht weil ich die später nicht
mehr wiederfinde!

Mein ganzes Konstrukt besteht aus 2 abhängigen Komboboxen, die Starts
und Ziele darstellen. Gegeben sind pro Combobox je eine Liste. Per
ListIndex sind anfangs mein -äh- Lieblingsstart und -ziel gesetzt.

Wähle ich z.B. einen anderen Start aus wird in die andere Box eine Liste
plaziert die nur die erreichbaren Ziele enthält.
Umgekehrt kann ich ein anderes Ziel wählen und es dürfen nur passende
Starts einstellbar sein. Hätte ich also 20 Starts bedeutet das 20
versch. Listen mit Zielen!
Die Boxen liegen auf der Form nebeneinander, und ich kann die
Fahrtrichtung auch ändern, dann ist alles umgekehrt und wieder mit
anderen Listen.
Zudem kann ich noch zwei Fahrweisen wählen, was nochmal andere Listen
erfordert.

Wenn also z.B. nach Eingabe eines neuen Starts das alte Ziel noch
erreichbar ist (in der neuen Zielliste vorkommt) soll es stehenbleiben.

Bei so einem Umfang sind eher Zweizeiler gefragt um das zu überprüfen.
Bei Deinem Beispiel würde ich die Übersicht garantiert verlieren.

Morgen werde ich mal den Vorschlag von Claus mit dem Array testen, das
scheint für mich verständlicher zu sein und kompakter...

Hans

[Ulrich Möller]

Hallo Hans,

Am 30.07.2017 um 22:56 schrieb Hans.Alborg:
> Hallo Ulrich,
>
> Ulrich Möller schrieb:
>
>> ich habe dir mal eine Beispielmappe zum herunterladen erstellt:
>> cbliste.xlms
>> https://1drv.ms/f/s!Ank2PUnOf8USghPSfXtJQKKS2-56
>> <https://1drv.ms/f/s%21Ank2PUnOf8USghPSfXtJQKKS2-56>
>>
>> Als Beispiel habe ich eine Kombobox gewählt und beim Click auf
>> aktualisieren werden die Werte des benannten Bereiches in die Kombobox
>> eingetragen, wobei nur eindeutige Werte übernommen werden.
>> In diesem Beispiel werden nicht mehr vorhandene Werte nicht gelöscht!
>> Hierfür müßte man das Beispiel entsprechend erweitern.
>
> Das ist doch aber einiger Aufwand (viele "Baustellen"), und eine
> Schleife gibts auch. Außerdem etliche Sachen mit denen ich nicht
> umgehen kann. Namen für Rangebereiche mag ich nicht weil ich die
> später nicht mehr wiederfinde!
>

Da hast du etwas falsch verstanden. Den benannten Bereich habe ich nur
benutzt, weil ja irgendwie die Beispieldaten in die Combobox geladen
werden sollen. Da dieser benannte Bereich nicht zusammenhängend ist,
geht das nur mit einer Schleife. Das ist nur im Beispiel so und kann
auch anders gemacht werden. Die Klasse ist eine sogenannte
"Wrapperklasse" für die vorhanden Combobox und ergänzt die AddItem
Methode so, daß keine doppelten Einträge mehr vorkommen können.

> Mein ganzes Konstrukt besteht aus 2 abhängigen Komboboxen, die Starts
> und Ziele darstellen. Gegeben sind pro Combobox je eine Liste. Per
> ListIndex sind anfangs mein -äh- Lieblingsstart und -ziel gesetzt.
>
> Wähle ich z.B. einen anderen Start aus wird in die andere Box eine
> Liste plaziert die nur die erreichbaren Ziele enthält.
> Umgekehrt kann ich ein anderes Ziel wählen und es dürfen nur passende
> Starts einstellbar sein. Hätte ich also 20 Starts bedeutet das 20
> versch. Listen mit Zielen!
> Die Boxen liegen auf der Form nebeneinander, und ich kann die
> Fahrtrichtung auch ändern, dann ist alles umgekehrt und wieder mit
> anderen Listen.
> Zudem kann ich noch zwei Fahrweisen wählen, was nochmal andere Listen
> erfordert.
>
> Wenn also z.B. nach Eingabe eines neuen Starts das alte Ziel noch
> erreichbar ist (in der neuen Zielliste vorkommt) soll es stehenbleiben.
>
> Bei so einem Umfang sind eher Zweizeiler gefragt um das zu überprüfen.
> Bei Deinem Beispiel würde ich die Übersicht garantiert verlieren.

Mit einem Zweizeiler ist das wohl nicht zu machen, aber ich lasse mich
gerne überraschen.
Wenn du in meinem Beispiel die Klasse als "Blackbox" betrachtest, dann
bleibt nicht mehr viel über:

1. Klassenvariable anlegen und neue Klasse zuweisen
2. Bestehende Combobox zuweisen
3. Beim Schließen der Userform vorher die Klasse wieder freigeben.

fertig!

Danach kann man nach belieben mit <Klassenvariable>.AddItem Texte in die
Combobox schreiben, ohne vorher prüfen zu müssen, ob der Eintrag schon
vorhanden ist oder nicht. Viel einfacher geht es im Prinzip nicht mehr.
Wenn man das Verhalten ändern oder erweitern möchte, braucht man nur
noch die Klasse entsprechend anpassen bzw. erweitern.
Das dient ja gerade dazu, eben nicht die Übersicht zu verlieren.

Ulrich

[Hans.Alborg]

Hallo Ulrich

Ulrich Möller schrieb:

> ...

> 1. Klassenvariable anlegen und neue Klasse zuweisen
> 2. Bestehende Combobox zuweisen
> 3. Beim Schließen der Userform vorher die Klasse wieder freigeben.
>
> fertig!
>

> ...Viel einfacher geht es im Prinzip nicht mehr.

Naja von Deiner Warte aus...

Ich bin meinen Code zZt. total am Umkrempeln, statt Abhängigkeit von
Cbox_a gegen Cbox_b behandle ich nur noch Start vs. Ziel. Welche der
Userboxen es gerade betrifft liegt dann an der Variablenvergabe.
So lassen sich viele Sachen zusammenfassen was ca. 3/4 Code einspart
(dieser Prozedur).
Wie dann mein oben angesprochenes Problem da eingearbeitet wird muß sich
erst noch zeigen. Es gibt z.B. eine Plausiblitätskontrolle von Start zu
Ziel (Fehler = Cbox- Hintergrund rot), die komplett entfallen könnte
wenn ich die Zuordnungen fehlerfrei bekomme.

Übrigens:

> Danach kann man nach belieben mit <Klassenvariable>.AddItem Texte in
die Combobox schreiben, ohne vorher prüfen zu müssen, ob der Eintrag
schon vorhanden ist oder nicht.

Meine Listen sind statisch! Lediglich wenn der Cbox-Text (also was man
in der Box sieht) auch in der neuen Liste existiert, soll er stehenbleiben.

Das ist alles wass ich wollte.

Hans