automatic Comments

[DanWo]

Is-it possible to insert in cells (in a defined range of cells) automatic comments (from a an other worksheet)
exemple:
In the column D I have to enter a code (1 to 10);the code is corresponding to a description -this desription is mentionned in an other worksheet (column A=code;column B=description)
I would like that these informations are visible as comments (with automatic update if the desriptions change)

 

[xlbo]

Use vlookup
In column E (starting in E2 for example), assuming that your other sheet isd called "Lookup" and code/description data resides in A2:B11
In E2
=if(D2="","",vlookup(D2,'Lookup!'$A$2:$B$11,2,false))
then fill down

HTH Rgds
~Geoff~

 

[DanWo]

Thank you for this explanation , but I would like to insert these commments as "comment" and not in cells (visible only when the mouse is on the code cell)

 

[xlbo]

ooops - try this then:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 4 Then
Exit Sub
Else
myDesc = WorksheetFunction.VLookup((Target.Text) * 1, Sheets(&quot;Sheet1&quot.Range(&quot;I1:J10&quot, 2, False)
On Error GoTo errHandle
With Range(Target.Address).AddComment
.Visible = False
.Text Text:=&quot;Description:&quot; & Chr(10) & myDesc
End With
End If
Exit Sub
errHandle:
With Range(Target.Address).Comment
.Text Text:=&quot;Description:&quot; & Chr(10) & myDesc
End With
End Sub

The line:
myDesc = WorksheetFunction.VLookup((Target.Text) * 1, Sheets(&quot;Sheet1&quot.Range(&quot;I1:J10&quot, 2, False)

produces the comment description
(Target.text) * 1 converts a string to a number - you may not need to do this if your lookup table is a set of strings that look like numbers

Sheets(&quot;Sheet1&quot.Range(&quot;I1:J10&quot needs to be changed to refer to your lookup range of codes and descriptions

Have tested this and it works

Rgds
~Geoff~

 

[DanWo]

Taking in account that the numeric code (0 to 6) is in the column F (F17:F22)and the description in column C (C17:C22),how can I made the vlookup function?

 

[DanWo]

with the function vlookup, I must first enter my choice (code 0 to 6); but,is-it possible that the comment is a list of choices (each code with his own description)?

 

[xlbo]

You can't - you need the description to the right of the code for vlookup to work - ideally as 2 columns next to each other
Code Description
0 blah blah
1 dumdedum

Not sure what you mean by your last question


The code I provided adds / changes a comment in a cell with the description associated with the entered code...isn't that what you asked for ?? Rgds
~Geoff~

 

[DanWo]

Not really.I would like to see a list of choice (with code and description, in order to avoid mistakes in the choice of code!I supposed that a list of choice in a comment box should be a good solution!

 

[xlbo]

Well, you need to be clearer in what you are asking for then:
&quot;In the column D I have to enter a code (1 to 10);the code is corresponding to a description -this desription is mentionned in an other worksheet (column A=code;column B=description)
I would like that these informations are visible as comments (with automatic update if the desriptions change)&quot;
&quot;I would like to insert these commments as &quot;comment&quot; &quot;

You asked for a description of the code to appear in a comment which would change if the code changed or be added when a code was entered.

Now, it seems that you want a list of codes and descriptions to appear in the comment itself....when and how do you want this to happen ??? [confused] and why ?? Rgds
~Geoff~

 

[DanWo]

When the cell is active cell

 

[Adamba]

DanWo,

Be clearer next time.

an example of what you want is:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.ClearComments
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Text Text:= _
&quot;Options:&quot; & Chr(10) & &quot;1. Test1&quot; & _
Chr(10) & &quot;2. Test2&quot; & Chr(10) & _
&quot;3. Test3&quot; & Chr(10) & &quot;&quot;
End Sub

This will make an option comment box appear for the active cell.
all you need to do is change the text command to match what you want it to say or to relate to your cells.

Note: The Chr(10) used makes the comment go down a line.


Adam

 

[xlbo]

Adam - nice, but you need:
If Target.Column <> 4 Then
Exit Sub
Else
'code goes here
End if

as (I think) it should only be done in column D Rgds
~Geoff~

 

[Adamba]

my code always needs tweaking, thats what im finding at the moment , ohh well back to my problems

Have Fun!
Adam