Use cell text as a cell comment of another cell

AusSteelMan

Board Regular
Joined
Sep 4, 2009
Messages
208
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
HI
Insert the following codes as sheet macro(right click on sheet tab and choose view code)
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

Board Regular
Joined
Sep 4, 2009
Messages
208
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
Code:
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

Board Regular
Joined
Sep 4, 2009
Messages
208
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:
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("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

Board Regular
Joined
Sep 4, 2009
Messages
208
I knew if asked for help, I would figure it out on my next attempt.

I have ended up with the following code:

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

Board Regular
Joined
Sep 4, 2009
Messages
208
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,250
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top