Formula in comment box

Jay59 · #1 (**permalink**) 09-19-06, 23:41

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

michaeldavid · #2 (**permalink**) 09-20-06, 06:57

No there isn't

Amazing_Spiderman · #3 (**permalink**) 09-30-06, 08:08

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

Jay59 · #4 (**permalink**) 09-30-06, 08:49

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

Amazing_Spiderman · #5 (**permalink**) 09-30-06, 09:57

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

Jay59 · #6 (**permalink**) 09-30-06, 12:45

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.

norie · #7 (**permalink**) 09-30-06, 13:21

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

Jay59 · #8 (**permalink**) 10-02-06, 17:51

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.

norie · #9 (**permalink**) 10-02-06, 19:20

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.

Jay59 · #10 (**permalink**) 10-02-06, 19:44

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

Jay59 · #11 (**permalink**) 10-06-06, 11:06

Any ideas on it.

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

Jay59 · #12 (**permalink**) 10-06-06, 11:38

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.

monteau · #13 (**permalink**) 10-09-06, 09:05

nop.......

norie · #14 (**permalink**) 10-09-06, 09:27

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

Internet Services

Web Development

Digital Marketing

Consumer Tech