Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments - populate using vlookup
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments - populate using vlookup
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 -- HTH, Barb Reinhardt "Roger on Excel" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comments - populate using vlookup
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 "Barb Reinhardt" wrote: 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 -- HTH, Barb Reinhardt "Roger on Excel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup to populate drop-down | Excel Discussion (Misc queries) | |||
Vlookup? populate 3 columns of data when match a common value, | Excel Worksheet Functions | |||
vlookup comments | Excel Discussion (Misc queries) | |||
cell comments in a vlookup table | New Users to Excel | |||
Macro to Add Comments with vlookup | Excel Programming |