Use cell text as a cell comment of another cell

A

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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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
 
Upvote 0
Solution
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
Click to expand...

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
 
Upvote 0
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
 
Upvote 0
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.:)
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Dynamic Cell Editing from Another Sheet
Replies
3
Views
402
Cubist
Cubist
K
  • Solved
[VBA] How to add Comments to Table column using text from another column
Replies
1
Views
732
kcmuppet
K
A
  • Question Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
508
AlfredDB
A
A
  • Question Question
writable cells limited by dropdown
Replies
1
Views
506
CharlesChuckieCharles
C
F
  • Solved
Data Validation List - Auto update cell values with a twist
Replies
1
Views
1K
Fritz23
F

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top