2

I have a long list in a sheet in my Excel file:

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

On another sheet, I need to input those numbers. But as I cannot remember which number belongs to which text, I'd like to have some form of auto-complete feature, that shows me which value belongs to which number. For example, if I type 12, I'd like to see a tooltip that shows me the following:

123 - Text 123
127 - Another text

because both 123 and 127 begin with 12.

Is something like that possible? Doesn't matter if it's possible with built-in functionality or by creating an Add-In or VBA script.

2
  • If you use Data Validation, you can create a custom in-cell drop-down list that contains all items in a defined list or range. There is also an option to create a custom pop-up message when the cell is selected but cannot customize the message based on what is being entered into the cell.
    – PFitz
    Jul 10, 2014 at 13:41
  • Thank you. but cannot customize the message based on what is being entered into the cell I need to customize that message. Jul 10, 2014 at 13:51

1 Answer 1

2

Results

This is what can be done with VBA (not VBScript!). It automatically creates the comment whenever the cell value is changed.

Results


Code

Source Range

For the code to function you need to create a named range Source for your source data.

enter image description here

Standard Code Module

You'll need to create a Standard Code Module in the Visual Basic Editor (Alt + F11) for the two functions required to accomplish this. A reference to Microsoft Scripting Runtime must also be set.

References

Paste the following code in your Standard Code Module.

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

This code assumes that the Numbers are found in Column 1 and the Values in Column 2 - it'll also skip the first row. You can tweak it to better suit your needs.

Worksheet Code

Paste the following code in your Worksheet Code

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

You can change the Me.Range to any single cell you want.


Usage

Simply enter a value in the cell you specified and the comment containing all matches will be added automatically.


Concerns

This code creates the dictionary every time it searches for matches - it's not a big deal with small to medium sized Source ranges (I tested it up to 10.000 and it still executed in a few milliseconds).

The comment tends to flow off the screen if there are a lot of matches - the only real way to remedy that is to enter a more specific value.

Blank cells in the Source range are ignored by using xlCellTypeConstants, this will not work for formulas - you'll need to either switch to xlCellTypeFormulas or find another way of only selecting the cells with values.

1
  • As a side note - I really enjoyed solving that problem, thank you for the excellent question!
    – natancodes
    Jul 10, 2014 at 18:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .