Results
This is what can be done with VBA (not VBScript!). It automatically creates the comment whenever the cell value is changed.
Code
Source Range
For the code to function you need to create a named range Source
for your source data.
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.
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.
but cannot customize the message based on what is being entered into the cell
I need to customize that message.