Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2006
    Posts
    162

    Unanswered: Formula in comment box

    Is there a way to put a formula in a comment box????

  2. #2
    Join Date
    Dec 2004
    Posts
    37

    Unhappy Unfortunately

    No there isn't

  3. #3
    Join Date
    Aug 2006
    Posts
    21

    Exclamation Well I might be prone to agree However .......

    Code:
    
        Range("A1").AddComment
        Range("A1").Comment.Visible = False
        Range("A1").Comment.Text Text:="Amazing Spiderman:" & Chr(10) & _ 
       "Typing text here"
    If you know some VBA I hope this peaks your creativity. You might not be able to do that directly in Excel but the code above can help you in VBA.

    Hope this Helps Jay59.

    -----Amazing Spiderman

  4. #4
    Join Date
    Sep 2006
    Posts
    162
    I was looking at running vba to fill in the comments. That will definalty get me started, I assume i can reference to a cell in excel instead of Typing text here.

    I would need to pull the forumula out of the box add to it then put in coment box.

    Like the cell will be =Sheet1!B3 and I would want the comment box to pull Sheet!B5 so I would have to add 2 to the number

  5. #5
    Join Date
    Aug 2006
    Posts
    21
    Quote Originally Posted by Jay59
    I assume i can reference to a cell in excel instead of Typing text here.
    Exactly, now that you know its possible you can have lets say all comments launched whn the workbook opens or in any other procedure. For specific calculations you could reference whatever cells you need. In addition there might be a way of detailing the comments themselves in color font etc.

    Enjoy
    Last edited by Amazing_Spiderman; 09-30-06 at 11:00.

  6. #6
    Join Date
    Sep 2006
    Posts
    162
    Awsome, i'll have work on that this weekend. I'll only set it to calc when i run an update. The info only updates once a day, but theres 62,500 cels :P but not all wil have a comment. Thanks for the info bro.

  7. #7
    Join Date
    Mar 2006
    Posts
    163
    Jay

    The following code will put the formula in B3 in a comment on B5.
    Code:
    Sub InsertComment()
        With Range("b3")
            If .HasFormula Then
                .Offset(, 2).AddComment Text:=.Formula
            End If
        End With
    End Sub

  8. #8
    Join Date
    Sep 2006
    Posts
    162
    Lets say B4 has this formula in it.

    ='Data'!E356

    So its pulling from sheet Data and cell E356

    I want the comment to pull 'Data'!358,'Data'!359,'Data'!360

    I would need vba to do this for a range of cells on 25 differant sheets. Each sheet has 2500 cells so theres a total of 62,500 cells this would run thru.

  9. #9
    Join Date
    Mar 2006
    Posts
    163
    Could you explain further what exactly you want to do?

    Did you try the code I posted?

    It won't do exactly what you want to do, but it's not actually clear what you want to do.

  10. #10
    Join Date
    Sep 2006
    Posts
    162
    I get error on this line

    .Offset(, 3).AddComment Text:=.Formula


    the formula in b3 is =TRANSPOSE('Data 1'!B25006:B25010)


    Not sure if the transpose if messing it up

    I did the transpose across 5 cells. keept me from coding each one

  11. #11
    Join Date
    Sep 2006
    Posts
    162
    Any ideas on it.

    Whats the basic script to add a comment on a given range?

  12. #12
    Join Date
    Sep 2006
    Posts
    162
    Quote Originally Posted by norie
    Jay

    The following code will put the formula in B3 in a comment on B5.
    Code:
    Sub InsertComment()
        With Range("b3")
            If .HasFormula Then
                .Offset(, 2).AddComment Text:=.Formula
            End If
        End With
    End Sub

    What this does is inserts a comment 2 cells over from the b3.

  13. #13
    Join Date
    Oct 2006
    Posts
    1
    nop.......

  14. #14
    Join Date
    Mar 2006
    Posts
    163
    Quote Originally Posted by Jay59
    What this does is inserts a comment 2 cells over from the b3.
    My bad, getting columns and rows mixed up.

    Just remove the comma from the offset.
    Code:
    Sub InsertComment()
        With Range("b3")
            If .HasFormula Then
                .Offset(2).AddComment Text:=.Formula
            End If
        End With
    End Sub

Posting Permissions

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