insert comment = formula result

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Can you populate a comments box with the results of a formula?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sure: Exactly what do you want to do? What is the formula? Is it in a cell or simply floating about?

lenze
 
Upvote 0
Thanks for looking,

I have vlookup formula that gets text(user comments that can be quite lengthy) from another sheet. Since the comments can be quite lengthy, it doesn't fit in the cell. And since it's a formula, the formula bar shows the formula, not the text. I would like to be able to put those comments into a comment box (or anything similar that pops up when the cursor goes over it).

My current solution is a macro that copy/paste special - values. So now that formula bar does show the text. But I would prefer to have the text in a comments box.


Here is the formula

Code:
=IF(VLOOKUP($G$4,Results!$A:$BI,26,FALSE)="","",VLOOKUP($G$4,Results!$A:$BI,26,FALSE))

Formula is located in Cell L7 in tab "Calculator"

It returns the text I want fine, but you can't read it all because it doesn't fit in the cell. I don't want to change the size of the cell, because of other stuff around it.
 
Upvote 0
Cool, I've gotten close with this

Code:
Private Sub Worksheet_Activate()
Worksheets(1).Range("l7").ClearComments
With Worksheets(1).Range("l7").AddComment
    .Visible = False
    .Text "Comments are :  " & Range("l7")
End With

End Sub

That does exactly what I wanted. I suppose I should have tried harder before asking the board....sorry. But I still need a little more...

The size of the comments box defaults to a fairly small size. But the comments can be very lengthy, how can I change the size of the comments box?
 
Upvote 0
Thanks for the help,

Sorry, but I guess I don't understand the VBA language very well yet. I can't figure out how to impliment those methods to set the size of comment boxes. Could you help me.

Say for the size, I want it to be litterally about the size of a credit card.

Thanks
 
Upvote 0
The code is fairly straight forward
Code:
Target.AddComment "Test"
Target.Comment.Shape.ScaleHeight 2, msoFalse
Target.Comment.Shape.ScaleWidth 2, msoFalse

The number 2 in the example code scales the comment by a factor of 2

lenze
 
Upvote 0
Excellent, Thank you very much Lenze.

Here's the end result of the code I'm using to get comments from a cell on a different sheet via vlookup into a comments box, with a specific size.

Code:
Private Sub Worksheet_Calculate()

Worksheets(1).Range("l7").ClearComments
With Worksheets(1).Range("l7").AddComment
    .Visible = False
   .Text "" & Range("l7")
   Set Target = Range("l7")
   Target.Comment.Shape.ScaleHeight 2, msoFalse
   Target.Comment.Shape.ScaleWidth 2, msoFalse
End With
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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