Hi All,
@Paul_1: Coole Funktion. Kannte ich auch noch nicht. Kann mir evtl. irgendwann nützlich sein. Ich hab die Frage aber etwas anders verstanden. Nämlich, dass nach Eingabe der ersten Buchstaben an gleicher Stelle das erstbeste Wort gezogen wird, das mit diesen Buchstaben beginnt und gleichzeitig die Auswahlliste auf alle Worte gefiltert wird, die mit den Buchstaben beginnen. Und das ganze in einem Dropdownfeld.
@Protonemura: Wie du vielleicht inzwischen bemerkt hast bringt dich deine erste Option nicht weiter. Die Liste wird zwar verkleinert, wenn die Werte aber nicht in sortierter Reihenfolge vorliegen, kann es u.U. sein dass falsche Werte zurückgegeben werden. Option 2 ist also die bessere Wahl:
- Füge eine ActiveX-Combobox (nicht die von MSForms verwenden) in dein Tabellenblatt ein.
- Mit Rechtsklick öffnest du im Kontextmenü den Menüpunkt Eigenschaften.
-
LinkedCell ist lediglich die Adresse der Zelle, die deine Auswahl im Tabellenblatt ausgiebt. z.B. B4. Zum Setzen der Liste musst du den Bereich in der Eigenschaft
ListFillRange angeben. Hier A1:A5000
- Außerdem sollte die Eigenschaft
MatchEntry auf
fmMatchEntryComplete stehen.
Wenn du nun per Klick auf das Symbol mit dem Zeichendreieck den Entwurfsmodus aufhebst kannst du deine Dropdownliste verwenden
- wie du siehst, wird nach Eingabe der ersten Buchstaben das erstbeste Wort gesucht und die Eingabe automatisch vervollständigt.
- Wenn du mit Druck auf Enter dieses Wort übernehmen und die Markierung löschen willst, musst du deinem Tabellenblattmodul des VBA-Editors folgenden kleinen Code schreiben:
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Application.EnableEvents = False
If KeyCode = vbKeyReturn Then
ComboBox1.SelLength = 0
ComboBox1.SelStart = Len(ComboBox1.Value)
end if
Application.EnableEvents = True
End Sub
Soweit so gut. Wenn du nun aber auf die Dropdownliste klickst siehst du noch immer Alle Werte. Diese zu filtern ist etwas kompliziert und bedarf einiger Erklärungen. Das geht nur über VBA.
Ersetze den genannten Code im Tabellenmodul durch diesen hier:
Option Compare Text
'legt fest ob nach jeder Eingabe die Dropdownliste sichtbar aktualisiert wird
Const MitDropDown As Boolean = False
Dim x As String
Dim y As Variant
Dim WshShell As Object
Dim Autoenter As Boolean
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Application.EnableEvents = False
If KeyCode = vbKeyBack Then
ComboBox1.Value = Left(ComboBox1.Value, ComboBox1.SelStart)
End If
If KeyCode = vbKeyBack Or KeyCode = vbKeyDelete Then
If x <> "" Then ComboBox1.ListFillRange = x
End If
Application.EnableEvents = True
End Sub
Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Set WshShell = CreateObject("WScript.Shell")
Application.EnableEvents = False
With ComboBox1
If KeyCode = vbKeyReturn Then
If Autoenter = False Then
DoEvents
ComboBox1.SelLength = 0
ComboBox1.SelStart = Len(ComboBox1.Value)
End If
Autoenter = False
ElseIf KeyCode > vbKeyDown Or KeyCode < vbKeyLeft Then
If .Value <> "" Then
If .ListFillRange <> "" Then
v = .Value
s1 = .SelStart
s2 = .SelLength
x = .ListFillRange
y = Range(x)
.ListFillRange = ""
.List = y
.Value = v
.SelStart = s1
.SelLength = s2
End If
If .ListCount > 0 Then
c = .ListCount
For i = 1 To c
If i > c Then Exit For
If Len(.SelText) > 0 And Len(.Value) > Len(.SelText) Then
If Not .List(i - 1) Like Left(.Value, .SelStart) & "*" Then
.RemoveItem i - 1
i = i - 1
c = c - 1
End If
ElseIf Len(.Value) > 0 Then
If Not .List(i - 1) Like .Value & "*" Then
.RemoveItem i - 1
i = i - 1
c = c - 1
End If
End If
Next i
End If
Else
If x <> "" Then .ListFillRange = x
End If
If MitDropDown Then
Autoenter = True
WshShell.SendKeys "{ENTER}"
DoEvents
ComboBox1.DropDown
End If
End If
End With
Application.EnableEvents = True
End Sub
Wenn du nun Eingaben in der Combobox machst und vor Übernahme des Werts mit Klick auf den Dropdownpfeil die Liste aufklappst siehst du nur die für dich interessanten Werte. Du möchstest stattdessen die Liste aufgeklappt lassen und nach jeder Eingabe sehen, wie sich die Werte verändern? Dann setze ganz oben die Variable MitDropDown auf True.
Einen kleinen Haken hat die Sache allerdings. Um die Liste der Combobox filtern zu können, muss der Code leider die Eigenschaft ListFillRange löschen und die Werte anders in die Combobox importieren. Normalerweise wird die Eigenschaft wieder gesetzt sobald du deine Dateneingabe löschst. Allerdings kann es sein, dass im Fall eines Bugs oder nach Änderung am Code Excel vergisst, welcher Bereich hinterlegt war. Um das zu umgehen solltest du noch im Modul DieseArbeitsmappe diesen Code einfügen. Damit wird beim Öffnen der Datei die Liste neu zugewiesen.
Private Sub Workbook_Open()
Application.EnableEvents = False
ComboBox1.Value = ""
ComboBox1.ListFillRange = "A1:A5000"
Application.EnableEvents = True
End Sub
Ersetze natürlich überall Combobox1 durch den Namen deines Objekts. Am besten mit STRG+H.
So genug geschwafelt. Ich hoffe, ich hab hier keinen Bug mehr drin. Wenn du Fragen oder Probleme hast, einfach melden.
Gruß Mr. K.