Microsoft Excel - Data Validation List with Comments

Hi All,

I have a list of items on a spreadsheet each with their own comment.

When I select an item in the drop down list I want the corresponding comment to also appear.

Anyone have any ideas as to how I can achieve this (I know it can be done because I have seen it done elsewhere, I just dont kow how to do it)?

Example of my list;

Cell   Ingredient
A1    Banana    Comment: 1 Banana = 100g, 1/2 Banana = 50g
A2    Apple    Comment: 1 Apple = 80g, 1/2 Apple = 40g
A3    Orange   Comment: 1 Orange = 90g, 1/2 Orange = 45g

Data Validation List is located in cell B1.

Any help on this is appreciated.

Thanks,

Torey

Hi Torey,
The only possibility that I can think of would be to trigger a macro to write the appropriate comment into the cell for the selected item.
I will see if I can work something out.
Regards
Harry

Hello Torey,
Macros to do what you want are:
On the worksheet (right click the tab and select View  Code) add the following:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("B1")) Is Nothing Then AddComment
End Sub

In a Module add the following:

Option Explicit

Sub AddComment()
    Dim strComment As String
    Dim rngCell As Range
    Dim rngList As Range
    Set rngList = Range("A1:A3")
    Range("B1").ClearComments
    For Each rngCell In rngList
      If rngCell.Value = Range("B1").Value Then
        strComment = rngCell.Comment.Text
      End If
    Next
    Set rngCell = Sheet1.Range("B1")
    MyAddComment rngCell, strComment
End Sub
 
Sub MyAddComment(ToCell As Range, Text As String)
     
    Dim objComment As Comment
    Dim strHeader As String
    Dim varSplit As Variant
    varSplit = Split(Text, ":")
    With ToCell
      strHeader = varSplit(0) & ":" & vbLf
      .ClearComments
      Set objComment = .AddComment(strHeader & varSplit(1))
      With objComment.Shape.TextFrame
        .Characters.Font.Bold = False
        .Characters(1, Len(strHeader)).Font.Bold = True
      End With
    End With
     
End Sub

The code could be tidied up a bit and made more general but at least it works.

Regards
Harry