Automatically add cell contents of specific range to cell comments when opening a document

[dslhs]

Hello,

Is there a way to automatically add cell contents of a specific range to cell comments on opening a document (specifically after the data from external sources has been updated).

The plan is to have a VBA code that:

- Wait till the document has been updated from linked documents (usually about 10 seconds)
- Takes the contents of each cell in H4:H100, I4:I100, J4:J100, K4:K100 and copies them into the comments for their respective cell
- Resize the comment box to fit around the text

The cells referenced don't contain the text themselves, but instead they reference other cells which contain a lot of text.

If you can get this to work, you'll make my staff and me very happy!

Many thanks,

 

[saboh12617]

Hello,

Well it is technically possible, but i would not do it: it will be TERRIBLY SLOW. You have around 400 cells and for each of them you want to read the comment and edit it. The problem is that unlike other cells parameters (like the value) for which you can read and write in one go, the comments/notes need to be accessed cell by cell. We looked on another forum for the same kind of question and it was taking around 2min to run the macro for a smaller array… super slow.

What about simply coping the old values in other columns?

 

[dslhs]

Thanks for your response. Lots of those cells are blank - in fact, usually it will only be about 15 or so that will have any content. Is there a way of the system ignoring it if the cell contains nothing and thereby speeding up the process?

 

[saboh12617]

Hello,

Well in this case it's very different !

Below a Sub you can call to update the given cells on the activesheet. However, I do not know which event will trigger this Sub (after what calculation?), maybe someone else can help you on this.

Public Sub UpdateComments()
  Application.ScreenUpdating = False
 
  Dim cellsToUpdate As Range
  On Error Resume Next
  Set cellsToUpdate = ActiveSheet.Range("H4:K100").SpecialCells(xlCellTypeConstants)
  On Error GoTo 0
  If cellsToUpdate Is Nothing Then Exit Sub

  Dim currentCell   ' As Range
  For Each currentCell In cellsToUpdate
    With currentCell
      If .Comment Is Nothing Then
        .AddComment .Value2 & vbNullString
      Else
        .Comment.Text .Comment.Text & vbCrLf & .Value2
      End If
   
    .Comment.Shape.TextFrame.AutoSize = True
    End With
  Next currentCell
 
End Sub