Comments - populate using vlookup

[Roger on Excel]

I have a column which contains a list of chemicals.

I use vlookup to get data for the chemicals such as density, flash point and
boiling point etc.

What i would like is for a comment box to become available for each
chemical, containing the vlookup data so that the chemical properties are
readily available when the curser goes over each chemical.

Is this possible and would it be possible for the comment box only to become
available if a chemical is actually in the column?

Can anyone help?

Thanks,

Roger

 

[Barb Reinhardt]

Using something like this should get you started. I made some assumptions
on where the vlookup was located and the column you wanted to modify. If a
comment already exists, I didn't account for that. Right click on the
worksheet tab to View Code and paste this in the worksheet where you'll be
making changes.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim myLookupRange As Range
Dim myVal As Variant

'This is the column that you want to add a value
Set myRange = Me.Range("B:B")

'Exits if more than one cell is modified
If Target.Count <> 1 Then Exit Sub

'Exits if target doesn't intersect with range
If Intersect(Target, myRange) Is Nothing Then Exit Sub

'I've assumed that the lookup range is a workbook level range
'If you need more help with this, come back
'Search for Dynamic range and OFFSET
Set myLookupRange = Range("LookupRange")
myVal = Null
On Error Resume Next
myVal = WorksheetFunction.VLookup(Target.Value, myLookupRange, 1, False)
On Error GoTo 0
If myVal = Null Then
MsgBox ("No lookup value found in table")
Else
msg = "Specific Gravity: " & WorksheetFunction.VLookup(Target.Value,
myLookupRange, 2, False)
msg = msg & Chr(10) & "Density: " &
WorksheetFunction.VLookup(Target.Value, myLookupRange, 3, False)
msg = msg & Chr(10) & "Flash Point: " &
WorksheetFunction.VLookup(Target.Value, myLookupRange, 4, False)
With Target
.AddComment (msg)
End With
End If

End Sub

 

[Roger on Excel]

Hi Barb,

Thanks for helping me again.

Im using the code you gave me, but i have an error message

"Method 'Range of object'_Worksheet' failed"

It has a problem on the following line. My data table for vlookup is a named
range called "Materials" which is on a sheet called Chemicals and has all the
data in it, so i substituted that name into the code - hence :

Set myLookupRange = Range("Materials")

What do you think Barb?

Roger