☰

Benutzerdefinierte Autovervollständigung in Excel

tag-icon • tag-icon microsoft-excel autocomplete
Benutzerdefinierte Autovervollständigung in Excel

Ich habe eine lange Liste in einem Blatt in meiner Excel-Datei:

Number   Value
123      Text 123
127      Another text
131      Yet another text
...      ...

Auf einem anderen Blatt muss ich diese Zahlen eingeben. Da ich mir aber nicht merken kann, welche Zahl zu welchem ​​Text gehört, hätte ich gern eine Art Autovervollständigungsfunktion, die mir zeigt, welcher Wert zu welcher Zahl gehört. Wenn ich beispielsweise eingebe, möchte ich 12einen Tooltip sehen, der mir Folgendes anzeigt:

123 - Text 123
127 - Another text

weil sowohl als auch 123mit 127beginnen 12.

Ist so etwas möglich? Dabei spielt es keine Rolle, ob es mit integrierter Funktionalität oder durch die Erstellung eines Add-Ins oder VBA-Skripts möglich ist.

Antwort1

Ergebnisse

Dies ist mit VBA möglich (nicht mit VBScript!). Der Kommentar wird automatisch erstellt, wenn der Zellenwert geändert wird.

Ergebnisse

Code

Quellbereich

Damit der Code funktioniert, müssen Sie einbenannter Bereich Sourcefür Ihre Quelldaten.

Bildbeschreibung hier eingeben

Standardcodemodul

Für die beiden hierfür erforderlichen Funktionen müssen Sie im Visual Basic-Editor ( Alt+ ) ein Standardcodemodul erstellen . Außerdem muss ein Verweis auf gesetzt werden.F11Microsoft Scripting Runtime

Verweise

Fügen Sie den folgenden Code in Ihr Standardcodemodul ein.

Option Explicit

Function GetMatches(strInput As String) As String
    Dim dict As Scripting.Dictionary
    Dim key As Variant
    Dim strOutput As String

    strOutput = "Matches found:" & vbCrLf

    Set dict = GenerateDictionary()

    For Each key In dict.Keys
        If key Like strInput & "*" Then strOutput = _
            strOutput & vbCrLf & key & " - " & dict(key)
    Next

    GetMatches = strOutput
    Set dict = Nothing
End Function

Private Function GenerateDictionary() As Scripting.Dictionary
    Dim source As Range
    Dim cell As Range
    Dim dict As New Scripting.Dictionary
    Dim number As Integer
    Dim value As String

    Set source = Range("Source").SpecialCells(xlCellTypeConstants)

    For Each cell In source
        If cell.Row < 2 Then GoTo PassRow
        If cell.Column = 1 Then number = cell.value
        If cell.Column = 2 Then value = cell.value
        If number <> 0 And value <> "" And cell.Column = 2 Then _
            dict.Add number, value
PassRow:
    Next

    Set GenerateDictionary = dict
    Set dict = Nothing
End Function

Dieser Code geht davon aus, dass die Zahlen in Spalte 1 und die Werte in Spalte 2 zu finden sind. Er überspringt auch die erste Zeile. Sie können ihn Ihren Anforderungen entsprechend anpassen.

Arbeitsblattcode

Fügen Sie den folgenden Code in Ihren Arbeitsblattcode ein

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strInput As String

    If Not Intersect(Target, Me.Range("D1")) Is Nothing Then
        strInput = Target.value
        Target.ClearComments
        Target.AddComment (GetMatches(strInput))
        Target.Comment.Shape.TextFrame.AutoSize = True
    End If
End Sub

Me.RangeSie können es in jede gewünschte einzelne Zelle ändern .

Verwendung

Geben Sie einfach einen Wert in die von Ihnen angegebene Zelle ein und der Kommentar mit allen Übereinstimmungen wird automatisch hinzugefügt.

Anliegen

Dieser Code erstellt das Wörterbuch jedes Mal, wenn er nach Übereinstimmungen sucht. Bei kleinen bis mittelgroßen SourceBereichen ist das kein großes Problem (ich habe es bis 10.000 getestet und es wurde trotzdem in wenigen Millisekunden ausgeführt).

Bei vielen Übereinstimmungen verschwindet der Kommentar tendenziell vom Bildschirm. Dies lässt sich nur durch die Eingabe eines spezifischeren Wertes beheben.

Leere Zellen im SourceBereich werden durch die Verwendung ignoriert xlCellTypeConstants. Dies funktioniert nicht für Formeln – Sie müssen entweder zu wechseln xlCellTypeFormulasoder eine andere Möglichkeit finden, um nur die Zellen mit Werten auszuwählen.

verwandte Informationen