Use cell text as a cell comment of another cell

[AusSteelMan]

Hi there,

I have another question that I hope someone can help me with this time.
(It appears that my first question was a real stumper! See http://www.mrexcel.com/forum/showthread.php?t=421823)


My spreadsheet has a dynamic range dropbox validation in Sheet 1 Col T.
What I would like to do is to add a comment to the cell when a value from the dropbox is selcted.
I think a vlookup may be the way to go. So in another sheet I have all the possible entries listed in Sheet 2 Col A and the text I want as the comment in Sheet 2 Col B.
I use this vlookup to add the text into Sheet 1 Col U, but I don't really want this column, rather would prefer it to be inserted as a comment in Sheet 1 Col T.


I have Google searched this site and help files but am unable to find an answer. The following post, http://www.mrexcel.com/forum/showthread.php?t=182721 , seemed to indicate that it could be done, but there was no detail and the message was from 2006, so thought I should start a new thread.


Look forward to, and thankyou for your reply.

Darren

 

[ravishankar]

HI
Insert the following codes as sheet macro(right click on sheet tab and choose view code)

Private Sub worksheet_change(ByVal target As Range)
Dim x As String
Dim y As Long
    If Not Intersect(target, Range("T2")) Is Nothing Then
    y = Sheets("Sheet2").Range("B65536").End(xlUp).Row
    Range("T2").Comment.Visible = False
    x = Application.WorksheetFunction.VLookup(Cells(2, 20), Sheets("Sheet2").Range("A2:B" & y), 2, False)
    Range("T2").Comment.Text Text:=x
    MsgBox "Updated"
    End If
End Sub

Now everytime you choose a value in dropdown from T2, it pulls value from sheet2 and updates as comment to T2
Ravi

 

[AusSteelMan]

Hi Ravi,

Thanks for your fast help, but I am unfortunately getting an error.

Run-time error '91':
Object variable or With block variable not set

When I press Debug the following line is highlighted

Range("T2").Comment.Visible = False

Additionally, will I need to change the "T2" range so that as I work down the the sheet the code will execute everytime I make a selection in Col T

I am still reasonably new to VBA. I have studied and adapted other code succesfully but cannot yet write code from scratch.

Thanks again,
Darren

 

[AusSteelMan]

Thanks to Ravi for a great springboard.

I have modified the code slightly to allow selection of a different value from the drop down box, so now the cell comment will be cleared and the new comment from the vlookup will be inserted.

The new code is:

Private Sub worksheet_change(ByVal target As Range)
Dim x As String
Dim y As Long
 
    If Not Intersect(target, Range("T2")) Is Nothing Then
    y = Sheets("LookupData").Range("B65536").End(xlUp).Row
    Range("T2").ClearComments
    Range("T2").AddComment.Visible = False
    x = Application.WorksheetFunction.VLookup(Cells(2, 20), Sheets("LookupData").Range("A2:B" & y), 2, False)
    Range("T2").Comment.Text Text:=x
    'MsgBox "Updated"
    End If
End Sub

At his request, I have sent Ravi a copy of the sheet due to the initial bug. Since I fixed it up I have sent him the updated sheet.

But a new question is how I get the code to apply for all cells used in Column T.
I am thinking that the range needs to be the selected cell instead of T2. As simple as this sound, for some reason (still reasonably new to VBA in the scheme of things you guys show on this board everyday!!) I cannot get the cell being edited to be the selected cell.
Can anyone help by modifying the code above.

Much thanks to Ravi for his efforts and to anyone who can help me.
If the work book is required/helpful, please private message me with your contact details.

Regards,
Darren

 

[AusSteelMan]

I knew if asked for help, I would figure it out on my next attempt.

I have ended up with the following code:

Private Sub worksheet_change(ByVal target As Range)
Dim x As String
Dim y As Long
 
    If Not Intersect(target, ActiveCell) Is Nothing Then
    y = Sheets("LookupData").Range("B65536").End(xlUp).Row
    ActiveCell.ClearComments
    ActiveCell.AddComment.Visible = False
    x = Application.WorksheetFunction.VLookup(ActiveCell, Sheets("LookupData").Range("A2:B" & y), 2, False)
    ActiveCell.Comment.Text Text:=x
    ActiveCell.Comment.Shape.TextFrame.AutoSize = True
    'MsgBox "Updated"
    End If
End Sub

Additionally, I have added a line of code to autosize the comment box.


Thanks everyone for all your posts, I have learned a great deal.

Thanks Ravi for getting me going on this one. I'm usually not an icon kind of guy, but this time i will make an exception.

 

[AusSteelMan]

Too good to be true. I'm trying at least - just not quite there.

I will need some help please to fix what I got wrong.

Of course, since I used "ActiveCell", now every time I use a dropdown box in a column other than T or press delete on a cell to clear the entry, I get a run time error 1004. Of course there is no vlookup table for the other columns as I don't have a comment to insert to those columns.

I need the range to be applicable to column T only.

Can anyone offer a fix please?

Many thanks in advance.
Darren

 

[AusSteelMan]

For posterity purposes:

I started a new thread for a follow-up problem, where my final answer code is displayed.

Please see:
http://www.mrexcel.com/forum/showthread.php?t=422929

Thanks again Ravi for the first part of help and pgc01 for helping me finish it off.

Darren