Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema Userform
BildScreenshot zu Userform Userform-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema ListBox
BildScreenshot zu ListBox ListBox-Seite mit Beispielarbeitsmappe aufrufen

COMBOBOX distinct liste mit Bedingungen

Betrifft: COMBOBOX distinct liste mit Bedingungen von: nimzowitsch@gmx.de
Geschrieben am: 04.07.2013 10:50:01

Hallo zusammen
ich habe ein Problem mit einer COMBOBOX.

Ich möchte die Auswahl aus einer Tabellenspalte erzeugen. In dieser Stehen einige Werte Doppelt drinnen. Ich brauche aber eine "distinct" Liste.
Dazu habe ich im Internet Lösungen gefunden.
Was mir aber noch fehlt ist: Neben der Auswahlliste wird per Formel ein X erzeugt, falls dieser Wert nicht in die Liste aufgenommen werden darf.

Ich habe das ganze mal in ein Beispiel gepackt. Kann mir jemand helfen?
https://www.herber.de/bbs/user/86200.xls

Viele Grüsse
Aaron

  

Betrifft: AW: COMBOBOX distinct liste mit Bedingungen von: Hajo_Zi
Geschrieben am: 04.07.2013 11:01:48

ich vermisse das Steuerelement.



Tabelle1

 ABCDE
1ElementAusschluss    Disstinct über Spalte A Wäre
2Element 1X    Element 1
3Element 2      Element 2
4Element 3X     
5Element 2       
6Element 3X     
7Element 1       
8Element 1      Grundlage
9        http://www.excelformeln.de/formeln.html?welcher=194

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
E2{=INDEX(A2:A32;MIN(WENN(UND(A2:A31<>"";$B$2:$B$32<>"X") ;ZEILE(2:31) ) ) ) }$E$2 =INDEX(RC[-4]:R[30]C[-4],MIN(IF(AND(RC[-4]:R[29]C[-4]<>"",R2C2:R32C2<>"X") ,ROW(R:R[29]) ) ) )
E3:E7{=WENNFEHLER(INDEX($A$2:$A$32;VERGLEICH(1;(ZÄHLENWENN($E$2:E2;$A$2:$A$31) =0) *($A$2:$A$31<>"") *($B$2:$B$32<>"X") ;0) ) ;"") }$E$3 =IFERROR(INDEX(R2C1:R32C1,MATCH(1,(COUNTIF(R2C5:R[-1]C,R2C1:R31C1) =0) *(R2C1:R31C1<>"") *(R2C2:R32C2<>"X") ,0) ) ,"")
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!


XHTML Tabelle für Darstellung in Foren,
einschl. Bedingter Formatierung ab Version 2007
XHTML-Version 11.30 einschl 64 Bit Version




GrußformelHomepage


  

Betrifft: AW: COMBOBOX distinct liste mit Bedingungen von: nimzowitsch@gmx.de
Geschrieben am: 04.07.2013 11:16:09

Ohh Mist,
sorry Hajo.
Das war selten dämlich.
Ich meinte eine COMBOBOX in einer Userform.
Sorry!

Gruss
Aaron


  

Betrifft: AW: COMBOBOX distinct liste mit Bedingungen von: Hajo_Zi
Geschrieben am: 04.07.2013 11:18:54

schaue hier
http://www.hajo-excel.de/vba_userform.htm
Beispiel 5 und 6

GrußformelHomepage


  

Betrifft: AW: COMBOBOX distinct liste mit Bedingungen von: nimzowitsch@gmx.de
Geschrieben am: 04.07.2013 11:21:33

Hallo Hajo

Auf diser Seite habe ich vorher schon geschaut und mir genau die beiden Beispiele angeschaut.
Gerade das "sortierte" ist super. Aber wie bekomme ich da die Bedingung hinzu?

Gruss
Aaron


  

Betrifft: AW: COMBOBOX distinct liste mit Bedingungen von: Hajo_Zi
Geschrieben am: 04.07.2013 11:22:42

da gibt es Code Listbox ersetzen durch ComboBox.

GrußformelHomepage


  

Betrifft: AW: COMBOBOX distinct liste mit Bedingungen von: nimzowitsch@gmx.de
Geschrieben am: 04.07.2013 11:28:51

Hallo Hajo
also, Listbox durch combobox zu ersetzen war leicht :) Aber wo gebe ich die Bedingung mit "nur nehmen, wenn kein x in der Spalte daneben"? ein?

Gruss
Aaron


  

Betrifft: AW: COMBOBOX distinct liste mit Bedingungen von: Hajo_Zi
Geschrieben am: 04.07.2013 11:33:05

weise doch die Werte ab E2 zu

Gruß Hajo


  

Betrifft: AW: COMBOBOX distinct liste mit Bedingungen von: nimzowitsch@gmx.de
Geschrieben am: 04.07.2013 11:37:43

Hallo Hajo

klar, das könnte ich tun. Aber genau das war ja der Grund, warum ich das ganze im VBA machen möchte. Ich möchte mit möglichst wenig Hilfsspalten auskommen. Die Mappe ist so schon riesig. Da bin ich über jede "unnötige" Spalte weniger froh.

Wenn es aber keine einfache Lösung (für einen VBA Laien wie mich) gibt, werde ich es so machen müssen.

Vielen Dank für die Hilfe.

Viele Grüsse
Aaron


  

Betrifft: AW: COMBOBOX distinct liste mit Bedingungen von: Hajo_Zi
Geschrieben am: 04.07.2013 12:49:16

Private Sub UserForm_Initialize()
    Dim Loletzte As Long                            ' letzte Zeile in Spalte A
    Dim LoI As Long                                 ' Schleifenvariable
    ' unabhängig von Excelversion für Spalte A (1)
    Loletzte = IIf(IsEmpty(Cells(Rows.Count, 1)), _
        Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)
    With ListBox1
        .ColumnCount = 2                            ' Spaltenanzahl 2
        ' Spaltebreite Spalte 2 auf Null setzen
        .ColumnWidths = ";0 Pt"
        ' Werte ab Zeile 2 in ListBox eintragen
        For LoI = 2 To Loletzte
            ' prüfen ob Wert schon vorhanden
            If Application.WorksheetFunction.CountIf(Range(Cells(1, 1), _
                Cells(LoI, 1)), Cells(LoI, 1)) = 1 And Cells(LoI, 2) <> "X" Then
                .AddItem Cells(LoI, 1)                  ' Wert eintragen
                .List(.ListCount - 1, 1) = LoI          ' Zeilennummer eintragen
            End If
        Next LoI
    End With
End Sub
Gruß Hajo


  

Betrifft: getestet? von: Klaus M.vdT.
Geschrieben am: 04.07.2013 13:20:23

Hallo Hajo,

ich meine, das wird nicht laufen ...
Wenn die Liste so aussieht:
1: Element1 x
2: Element1
dann wird aus 1 Element1 nicht eingelesen, weil es ein x hat. Aus 2 wird Element1 aber AUCH nicht eingelesen, weil Worksheetfunction.CountIf es das zweite Mal findet!
Gefordert ist aber: Element1 einlesen, denn es kommt (in 2) ohne x das Erste mal vor!

Ich hatte den Code auch schon so aufgebaut wie du, ihn dann aber aus den o.g. Grunden verworfen.
Mein nächster Ansatz war, die Liste per Autofilter nach "nicht X" zu filtern und dann nur die specialcells(xlCellTypeVisible) zu nehmen. Leider mag Worksheetfunction.Countif auf einen derart gefilterten Bereich nicht anspringen ...

Ich glaube, um das einlesen der Elemente ohne X in eine weitere Liste (Array oder Hilfsspalte auf dem Blatt sei mal dahingestelt) kommen wir hier nicht herum?

Grüße,
Klaus M.vdT.


  

Betrifft: AW: getestet? von: nimzowitsch@gmx.de
Geschrieben am: 04.07.2013 13:29:03

Hallo zusammen
genau, das wollte ich auch gerade schreiben... Ich "bräuchte" die 1 auch.

Egal wie das hier ausgeht. Vielen vielen Dank für Eure Hilfe!! Das ist echt super.

Gruss
Aaron


  

Betrifft: Ein Ansatz, ... von: Klaus M.vdT.
Geschrieben am: 04.07.2013 11:25:02

... Aaron,

gehe in einer Schleife jeden Wert durch. Wenn rechts davon KEIN x steht, schreibe ihn in ein Array.

Gehe in einer Schleife jeden Wert innerhalb des Array durch. Wenn er NICHT doppelt ist, schreibe ihn in ein Array2.

Gehe in einer Schleife jeden Wert von Array2 durch, und setzte ihn AddItem auf die Combobox.

ODER:
nimm die von Hajo vorgeschlagene Formellösung, aber schreibe die Formeln per VBA in eine freie Spalte. Dann ADDITEM für jede Zelle dieser Spalte über eine Schleife, dann die Hilfsspalte wieder löschen.


Direkter und mit Code möchte ich dir nicht zuarbeiten, da in deiner Musterdatei die besagte ComboBox /UF nicht enthalten ist und es dann eh überarbeitet werden müsste.

Grüße,
Klaus M.vdT.


  

Betrifft: AW: Ein Ansatz, ... von: nimzowitsch@gmx.de
Geschrieben am: 04.07.2013 11:41:36

Hallo Klaus

vielen Dank für Deinen Input.
Leider kenne ich mich zu wenig aus um mit Arrays fertig zu werden.
Die Formellösung per VBA und dann wieder löschen... das werde ich mir anschaun.

Vielen Dank

Gruss
Aaron


  

Betrifft: Combobox mit eindeutogen Werten befüllen von: NoNet
Geschrieben am: 04.07.2013 13:16:18

Hallo Aaron,

kopiere folgenden Code in das Klassenmodul des UserForms :

Private Sub UserForm_Initialize()
    Dim lngZ As Long, coll As New Collection
    
    On Error Resume Next
    For lngZ = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        If UCase(Cells(lngZ, 2)) <> "X" Then
            coll.Add Cells(lngZ, 1), Cells(lngZ, 1)
        End If
    Next
    
    With ComboBox1
        .Clear 'Alte Inhalte der Combobox löschen
        For lngZ = 1 To coll.Count
            .AddItem coll(lngZ)
        Next
    End With
End Sub
Hier das angepasste Beispiel :
https://www.herber.de/bbs/user/86206.xlsm

Gruß, NoNet




  

Betrifft: 'eindeutogen' heißt eindeutig 'eindeutigen' ;-)_oT von: NoNet
Geschrieben am: 04.07.2013 13:17:20

_oT = "ohne Text"


  

Betrifft: FTFY von: Klaus M.vdT.
Geschrieben am: 04.07.2013 13:27:55

Hi NoNet,

dein Code funktioniert nicht!

Private Sub UserForm_Initialize()
    Dim lngZ As Long, coll As New Collection
    
    On Error Resume Next
    For lngZ = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        If UCase(Cells(lngZ, 2)) <> "X" Then
            coll.Add Cells(lngZ, 1).Value, Cells(lngZ, 1)
        End If
    Next
    
    With ComboBox1
        .Clear 'Alte Inhalte der Combobox löschen
        For lngZ = 1 To coll.Count
            .AddItem coll(lngZ)
        Next
    End With
End Sub
FTFY!

Ich sehe nicht, wo du auf doppelte prüfst. Andererseits habe ich keine Ahnung, was eine "collection" ist ... kannst du mir eben nahebringen, was hier der Trick17 ist?

Grüße,
Klaus M.vdT.


  

Betrifft: AW: FTFY von: Rudi Maintaire
Geschrieben am: 04.07.2013 13:57:17

Hallo,
das Collection-Objekt ist eine Sammlung von Wert-Schlüssel Paaren. Jeder Schlüssel kann nur 1x vorkommen.
Deshalb auch On Error Resume Next.
Ich bevorzuge das Dictionary-Objekt. Das geht auch ohne On Error, da ein vorh. Schlüssel einfach überschrieben wird. Außerdem kann man die Werte und Schlüssel einfach in Arrays schieben.

Gruß
Rudi


  

Betrifft: Danke sehr! Immer wenn ich ... von: Klaus M.vdT.
Geschrieben am: 04.07.2013 14:07:52

... Dictionary, Collection oder auch RegluarExpression und Array lese wird mir klar, dass mein VBA Level noch sehr sehr lange auf "bescheiden" bleiben wird :-)

Grüße,
Klaus M.vdT.


  

Betrifft: Warum nur, bist doch auch schon fast ... von: Luc:-?
Geschrieben am: 05.07.2013 02:41:19

…8 Jahre dabei, Klaus… ;-)
Gruß Luc :-?

Besser informiert mit …


  

Betrifft: Ironie im Posting? von: Klaus M.vdT.
Geschrieben am: 05.07.2013 08:35:07

Hallo Luc,

ich bin noch nicht ganz wach und mein Ironie-Detektor ist noch nicht ganz auf der Höhe ..

ist dein Posting
a) ironisch gemeint, unter dem Hinweis dass du ein paar Dekaden mehr Programmiererfahrung hast als ich? oder
b) Ernst gemeint, dass ich nach fast einer Dekade diese Kniffe eigentlich drauf haben sollte?

Grüße,
Klaus M.vdT.


  

Betrifft: a/2 + b/2 ;-) Gruß owT von: Luc:-?
Geschrieben am: 05.07.2013 15:23:24

:-?


  

Betrifft: Codekorrekturen = Verschlimmbesserung von: NoNet
Geschrieben am: 04.07.2013 14:25:58

Hallo Klaus,

was eine "Collection" ist, hat Dir Rudi ja bereits erklärt (auch die Alternative des Dictionary-Objektes genannt).

Du hast Recht : Der Code im angehängten Beispiel ist fehlerhaft - das hat man nun davon, wenn man einen einfachen funktionierenden Code postet und danach noch einmal "verschlimmbesserkorigiert" ;-)

Hier aber ein geprüftes und funktionierendes Beispiel :

https://www.herber.de/bbs/user/86208.xlsm

Gruß, NoNet


  

Betrifft: AW: 'eindeutogen' heißt eindeutig 'eindeutigen' ;-)_oT von: nimzowitsch@gmx.de
Geschrieben am: 04.07.2013 13:36:13

Hallo NoNet
dein angespasstes Beispiel funktioniert prima.

Das ist allerdings der Code der im Beispiel ist

Private Sub UserForm_Initialize()
    Dim lngZ As Long, coll As New Collection
    
    On Error Resume Next
    'Collection in Abhängigkeit des "X" in Spalte B befüllen
    For lngZ = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        With Cells(lngZ, 1)
            If UCase(.Offset(, 1)) <> "X" Then
                coll.Add .Value, .Value
            End If
        End With
    Next
    
    With ComboBox1 'Combobox aus der Collection befüllen
        .Clear 'Alte Inhalte der Combobox löschen
        For lngZ = 1 To coll.Count
            .AddItem coll(lngZ)
        Next
    End With
End Sub


der ist anders als Der Code im GText.


Vielen Vielen Dank für die Hilfe
Aaron


 

Beiträge aus den Excel-Beispielen zum Thema "COMBOBOX distinct liste mit Bedingungen"