Comments with Lookup Formulas??

[John Gilchrist]

Is there anyway to insert a formula into a comment box??

I have one sheet with three columns which contain defect code numbers.

I have another worksheet tab in the same excel file which contains a list of
defect code numbers and defect descriptions.

I want to display the defect description in a yellow comment box when I move
the cursor over the cell which contains the defect code number.

I looked at Excel's comment feature, but it appears that these comment boxes
can contain only fixed text. Is there any way to incorporate a lookup
function into the comment box so that it can look up the corresponding list
in the other worksheet tab??

Thanks for any ideas,
John

 

[Dave Peterson]

You could write some code that could populate the comment with the results of an
=vlookup() function.

But if you change that part number, then the comment will be wrong until that
macro runs again.

This sounds like a nice idea until you start getting down to how many things can
go wrong (my opinion only).

If I were you, I'd just use an adjacent cell to contain that description.

But if you want a macro to do that:

Option Explicit
Sub AddCommentsToSelection()

Dim myRng As Range
Dim myCell As Range
Dim myLookupRng As Range
Dim lArea As Double
Dim res As Variant

Set myRng = Selection
Set myLookupRng = Worksheets("sheet2").Range("a:b")

For Each myCell In myRng.Cells
If myCell.Comment Is Nothing Then
'do nothing
Else
myCell.Comment.Delete
End If

res = Application.VLookup(myCell.Value, myLookupRng, 2, False)
If IsError(res) Then
'don't put anything there?
Else
myCell.AddComment Text:=res
With myCell.Comment
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
.Shape.Height = (lArea / 200) * 1.3
End If
End With
End If
Next myCell

End Sub

Just select the range to add the comments and run the macro.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

and I borrowed the resizing comment code from Debra Dalgleish's site:
http://www.contextures.com/xlcomments03.html#Resize