Results 1 to 8 of 8

insert comment = formula result

This is a discussion on insert comment = formula result within the Excel Questions forums, part of the Question Forums category; Can you populate a comments box with the results of a formula?...

  1. #1
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,336

    Default insert comment = formula result

    Can you populate a comments box with the results of a formula?
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default

    Sure: Exactly what do you want to do? What is the formula? Is it in a cell or simply floating about?

    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,336

    Default

    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.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,336

    Default

    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?
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default

    See the VBA help files for ScaleHeight and ScaleWidth methods.

    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,336

    Default

    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
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default

    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
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,336

    Default

    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
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com