VBA - Auswahl in Combobox mittels Wortteil

[LB1972]

Schönen Sonntag zusammen,
ich suche eine Lösung für folgende Situation:
Aus einer laaaangen Liste von gültigen Eingabewerten soll der Benutzer so auswählen können,
dass die Anwahl durch einen Wortteil ausreicht.
... also z.B. "deu", um "Bundesrepublik Deutschland" zu erhalten
Am liebsten wäre mir gewesen, Excel würde bei der Auswahl im Rahmen der Datengültigkeit so etwas unterstützen;
da ist mir allerdings keine Möglichkeit bekannt...
Also Plan B:
Ich lasse den Nutzer  via Doppelclick eine Userform starten und füttere die Combobox darin mit meiner Liste "Staatsnamen"; 
wenn der Benutzer einen Eintrag in das Textfeld der Combobox macht/ ändert, dann passe ich die verfügbare Auswahlliste so an, dass nur noch diese verbleiben, die den eingegebenen Wortteil beinhalten...

  20201018_LB1972_Form_Combobox_Auswahl mit Teilstring.xlsm (Größe: 30,77 KB / Downloads: 8)
Das funktioniert bei mir so einigermaßen....
Unschön:
Bei Eingaben im Textfeld der Combobox - bei ausgeklappter Auswahlliste- wird die aktualisierte Auswahlliste nur als eine Zeile mit Bildlaufleiste dargestellt.
2 Fragen:
    a) Hat jemand einen Rat, wie die aktualisierte (ausgeklappte) Auswahlliste besser dargestellt werden kann?
    b) Denke ich hier viel zu kompliziert und es gibt für mein Anliegen eine einfache  Lösung? 
       Z.B. verändertes Verhalten der Combobox - Schließlich möchte ich ja eigentlich "nur", dass sich meine Combobox 
       hier ähnlich verhält, wie ein Excel-Autofilter, bei dem ich ja auch durch die Eingabe eines Textteils meine Filterliste eingrenze
Vielen Dank für eure Unterstützung
Gruß Ludwig

Code:

Private Sub UserForm_Initialize()
   
    Me.ComboBox1.Clear                                      'Combobox leeren
    str = Selection.Value                                   'evt. vorhanden Zellwert erfassen
   
    ReDim arr1(1 To Range("Staatsnamen").Rows.Count, 1 To 1)    'arr1 erhält (für die "Lebensdauer" der Userform) die Werte aus dem Range"Staatsnamen"
    arr1 = Range("Staatsnamen")
        For i = 1 To UBound(arr1)
            Me.ComboBox1.AddItem arr1(i, 1)                     'Die Auswahlliste der Combobox wird mit der kompletten Liste "Staatsnamen" befeuert
            Debug.Print arr1(i, 1)
        Next i
        With Me.ComboBox1                                       'Combobox
            .Text = str                                         'erhält als Starttext den Wert auas der gewählten Zelle
            .SetFocus                                           'den Focus in der Userform
            .MatchEntry = fmMatchEntryNone                      'die Eigenschaft, bei Benutzung nicht gleich einen passenden Listenwert anzunehmen
        End With

    eintraege = UBound(arr1)                                    'Anzahl der Listenfelder aus der Auswahlliste
    AuswahllisteAnpassen                                        'Die Auswahlliste wird auf einen evtl bereits vorhandenen Wert in der gewählten Zelle angepasst
End Sub

Code:

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If str <> Me.ComboBox1.Text Then        'wenn eine Veränderung der Texteingabe vorliegt.. - also keine Aktion auf Pfeiltasten usw.
            AuswahllisteAnpassen                'Anpassung der Auswahlliste
        End If
End Sub

Code:

Sub AuswahllisteAnpassen()
        str = Me.ComboBox1.Text             'Eingabetext wird gemerkt
        k = 0
            For i = UBound(arr1) To 1 Step -1   'Für die Länge der Ursprungsliste
            On Error Resume Next
            bool = True
                If IsError(WorksheetFunction.Search(str, arr1(i, 1))) Then
                    bool = False
                    Debug.Print Err.Number
                    Err.Clear
                Else
                    k = k + 1                   'wird gezählt, in wie vielen Elementen der eingegebene Wert beinhaltet ist
                End If
            On Error GoTo 0
            Next i
            If k > 0 Then                               'wenn noch gültige Listenwerte gefunden wurden
                ReDim arr2(1 To k, 1 To 1)              'erhält arr2 die Länge der noch gültigen Einträge
            Else
                ReDim arr2(1 To 1, 1 To 1)              'ansonsten erhält arr2 die Länge 1, um nicht 0 zu sein
            End If
        k = 0
            For i = UBound(arr1) To 1 Step -1   'Für die Länge der Ursprungsliste
            On Error Resume Next
            bool = True
                If IsError(WorksheetFunction.Search(str, arr1(i, 1))) Then
                    bool = False
                    Debug.Print Err.Number
                    Err.Clear
                Else
                    k = k + 1                   'werden die verbleibenden gültigen Elemente im arr2 eingebracht
                    arr2(k, 1) = arr1(i, 1)
                End If
            On Error GoTo 0
            Next i
                For i = eintraege - 1 To 0 Step -1  'für die Anzahl der Elemente im Drop-Down wird jeweils gelöscht
                Me.ComboBox1.RemoveItem (i)
            Next i
                For i = 1 To UBound(arr2)           'DRop-DownElemente werdenmit arr2 neu bestückt                      #
                Me.ComboBox1.AddItem arr2(i, 1)
            Next i
        eintraege = UBound(arr2)                    'Die Anzahl der Dropdown-Element wird als Größe arr2 neu festgelegt
        Me.ComboBox1.Text = str                     'die Combobox erhält den gemerkten Wert wieder
End Sub

[Elex]

Hi

In dem Thema findet sich was zum anpassen.
https://www.clever-excel-forum.de/Thread...definieren

Gruß Elex

[LB1972]

Hallo Elex,
einmal mehr schnelle Hilfe von dir... 

Interessanter Ansatz, das nicht über Combobox zu lösen, sondern eine Textbox;
ich hab deine Codezeile

Code:

If InStr(1, w, TextBox1.Text, vbTextCompare) >= 1 Then

von "=1" auf ">=1" abgeändert, um das Suchergebnis an beliebiger Stelle des Auswahlwertes  zuzulassen.
Da hab ich jetzt was zum Nachdenken, ob ich auf deine Textbox-Version umschwenke;
@Forum,
gerne darf sich aber der eine oder die andere berufen fühlen, meinen Ansatz mit Combobox weiterzuspinnen...
Gruß Ludwig

[Elex]

Hier ist noch was von @ atllia mit Combo.
https://www.clever-excel-forum.de/Thread...eld?page=3&highlight=combobox

[Kuwer]

Hallo Ludwig,

mal mit Deiner Datei:

' **************************************************************
'  Modul:  UserForm1  Typ = Userform
' **************************************************************

Option Explicit

Dim arr1 As Variant, arr2 As Variant
Dim i As Long
Dim str As String

Private Sub ComboBox1_Change()
  With ComboBox1
    arr2 = Filter(Application.Transpose(arr1), .Value, True)
    If UBound(arr2) > -1 Then
      .List = arr2
    Else
      .Clear
    End If
    .DropDown
  End With
End Sub

Private Sub CommandButton1_Click()
    Selection = Me.ComboBox1.Text                      'Die gewählte Zelle übernimmt den Textwert der Combobox
    Unload Me
End Sub

Private Sub UserForm_Activate()
  ComboBox1.Text = str
End Sub

Private Sub UserForm_Initialize()
    str = Selection.Value                                'evt. vorhanden Zellwert erfassen
    arr1 = Range("Staatsnamen")
    With Me.ComboBox1
      For i = 1 To UBound(arr1)
          .AddItem arr1(i, 1)                            'Die Auswahlliste der Combobox wird mit der kompletten Liste "Staatsnamen" befeuert
      Next i
      .SetFocus                                          'den Focus in der Userform
      .MatchEntry = fmMatchEntryNone                      'die Eigenschaft, bei Benutzung nicht gleich einen passenden Listenwert anzunehmen
    End With
End Sub

Gruß Uwe

[LB1972]

Guten Morgen,

sorry, hat etwas gedauert, mich wieder zu melden.

@ Elex,
wenn ich deinen Verschlag von attila richtig angewendet habe, ist  ist es bei dieser Lösung erforderlich, den Begriffsanfang zu verwenden.
(Oder hab ich da was falsch gemacht?)
Das wollte ich eigentlich vermeiden. Erklärung: In meiner geplanten Anwendung wird die Auswahlliste Produktionswerke beinhalten, der Name in etwa so aufgebaut sein wird:
"1234 Produktion Elektronik Hintertupfing".
Nichtsdestotrotz, natürlich vielen Dank für den Vorschlag.

@ Uwe, (auch von deiner Seite ist es nicht das erste Mal, dass ich Hilfe erhalte  :28: )
das ist zu 98% das Gesuchte und wesentlich griffiger als mein Ansatz.
Die verbleibenden 2 Prozent:
In deiner Lösung muss ich Groß-/ Kleinschreibung beachten.
Gibt's da noch einen Kniff, der die dahingehende Nutzerschlampigkeit ausbügelt?

Schönen Tag und viele Grüße
Ludwig

[Kuwer]

Hallo Ludwig,

In deiner Lösung muss ich Groß-/ Kleinschreibung beachten.
Gibt's da noch einen Kniff, der die dahingehende Nutzerschlampigkeit ausbügelt?

das lässt sich mit dem Compare-Argument der Filterfunktion einstellen.
So wird die Groß-/ Kleinschreibung nicht beaachtet:

arr2 = Filter(Application.Transpose(arr1), .Value, True, vbTextCompare)

Gruß Uwe

[LB1972]

Hallo Uwe, 

passt, sitzt und hat Luft...

:78: :78:

Gruß Ludwig