Solved Result of formula to comment

Microsoft corporation Office 2010 profes...
December 20, 2014 at 11:24:04
Specs: Windows 7
Hello all

I have some pretty big text strings in a lot of cells and I am on the edge of losing the big picture. I have searched for a solution and what seems to be the right way for me to do it, is to move the result of those formulas that are too big to be readable in the cells to a comment to that cell.

I have found some approaches to this, but I find them very hard to adapt to my needs. The requirements I have:

1. Comment has to be updated when the result changes. Either if the change is made by a user directly inputting data in that cell or if the user alters a cell that changes the result in the cell where the comment is needed.

2. I need the code either to check some specified columns or to check for changes in every cell in the sheet, but only if there are more than 8-10 characters in the result. The last part is only needed if all cells in the sheet is checked for changes. I would prefer the first solution to be able to specify which columns should be checked.

3. I can see a lot of people are having trouble with the size of those commentboxes, so maybe an option to resize it either manually in the code or automaticly done by the code.

4. The result needs to be left untouched in the cell the comment is attached to. And if I click the cell I need to be able to modify the formula as normal.

See More: Result of formula to comment

Report •

December 20, 2014 at 17:31:28
Instead of in a Comment, how about in a text box?

Not sure how it would have to be arraigned and
I'm on vacation with no Excel

But to get your result in a text box
Create the Text Box,
then with the cursor blinking in the box,
click your mouse in the formula bar.
Then type, =A1 where A1 contains your formula result.
This should display the contents of cell A1 in your text box.

See here:


Report •

December 21, 2014 at 09:08:41
As I see it, the text box is visible all the time right?

I can't have a lot of text-boxes showing all the time, so I think the way for me to go on this is by putting the result into a comment, that is attached to the cell which is displays the result of.

But I can see, that it is possible to put the result into a text box and I will keep that in mind if such a need should come. Thanks for your input.

Report •

December 21, 2014 at 19:58:32
✔ Best Answer
I believe that this code will do what you ask.

As written, it checks for changes in Columns B & E only. Change the Select Case line to match the columns (by number) that you want to monitor.

First off, I have to give credit to Dave Hawley in this thread for the portion of the code that checks to see if a cell has a Comment.

I didn't write that part, I just modified it and then added the section that writes the cell value into the Comment.

Let me know if this works for you:

Private Sub Worksheet_Change(ByVal Target As Range)
'Dimension variable as Boolean (True/False)
Dim celHasComment As Boolean, myCom As String
'Only run if change occurs in Column B or E
  Select Case Target.Column
   Case 2, 5
    For Each cel In UsedRange
     'Prevent any Run Time Error if cell has no Comment.
        On Error Resume Next
     'Will pass True to celHasComment if cel has a Comment.
     'This is because the Parent Address of the Cel Comment
     'will always be the same as the Address housing the Comment
     'If no Comment, "cel.Comment.Parent.Address"
     'would *normally* cause a Run Time Error and leave the
     'Boolean variable at its default value False.
      celHasComment = _
       cel.Comment.Parent.Address = cel.Address
'If the cell has a Comment, then set the Comment Text
'to be the cell Value and AutoSize the frame
        If celHasComment Then
           myCom = cel.Value
           cel.Comment.Text Text:=myCom
           cel.Comment.Shape.TextFrame.AutoSize = True
           celHasComment = False
        End If
     'Reset Run Time Errors
        On Error GoTo 0
  End Select
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

January 6, 2015 at 00:48:17
I can't seem to get this working. The comments should only be attached to the cells that change and only to cells in the specified columns. Somehow when I run this code, it does change the other comments in the sheet, but doesnt attach the comment to the cell that has changed. I need to restrain the comments to specified columns, based on changes made in that column, either by the user or if the result changes due to references being changed. A bit tricky to explain, but hope you get the point.

I have now played a bit around with it and now I can see how it works.

The code monitors changes in the columns specified and if something in those columns change, ALL comments in the worksheet is altered to display the content of the cell that it is attached to.

In my case where I have a lot of help comment attached to cells all around in my worksheet I need to be able to control which columns comments are affected by this macro.

Also, if there is no comment attached to the cell in the column that needs a comment, it has to be created. I have over 1000 rows in my sheet, so I need the code to automaticly add a comment, if none is present.

As far as I can see, the automatic size function works, but it doesnt make line breakes, so a very long text is in one line. How can I define how many characters there can be in a result of a formula, before its put down to the next line in the comment?

In advance, thanks a lot for your help!

message edited by JacobJ

Report •

Ask Question