Generate comments containing cell content in a scrollable list with VBA

Question

I have created a scrollable list in excel (using the ActiveX Scroll Bar and OFFSET method). My goal is to generate a comment for every cell in this list. Each comment should contain the exact contents of the cell it is attached to. I will generate comments using a button to which I assigned the code. I click the button first (which generates the comments) and afterwards I scroll the scroll bar. What I would like is that after I scroll the scroll bar, I do not have to click the button again. In other words, after I scroll, I would like the comments to have adjusted automatically. This said, I have a problem. Although I am able to generate comments for static items, whenever I scroll up or down in the list, the comments do not update (i.e. if the comment generated for cell C5 is "Hello World", when I scroll down the comment for cell C5 will still be "Hello World" even though the content of cell C5 has changed). Here is the code I have so far:

Private Sub Create_Comments()
    'src refers to the cells for which we will generate comments
    Dim src As Range: Set src = Range("C5:C14")
    'tar refers to the cells where the comments will be generated
    Dim tar As Range: Set tar = Range("C5:C14")

    For i = 0 To src.Rows.Count - 1
        Dim sResult As String
        sResult = Cells(src.Row + i, src.Column)
        If sResult <> "" Then
            With Cells(tar.Row + i, tar.Column)
                .ClearComments
                .AddComment
                .Comment.Text Text:=sResult
                .Comment.Shape.ScaleWidth 4, msoFalse, msoScaleFromTopLeft
                .Comment.Shape.ScaleHeight 2.26, msoFalse,soScaleFromTopLeft
            End With
        Else: sResult = ""
            Exit Sub
        End If
    Next i
End Sub

Any insight into why this doesn't work as desired would be greatly appreciated! I am using Excel 2007.

Thanks!

Answer 1

In order to update the comments every time the scrollbar is changed, you need to assign the Create_Comments macro to your scrollbar (if a standard form control), or call it from a ScrollBar_Change event (if an ActiveX scrollbar).

Note that you may need to call Application.Calculate or Worksheet.Calculate before updating the comments in Create_Comments in order to ensure that the cells are updated before the comments are created.