Copy all comments from one worksheet to itself after refreshing the data?

[leonlai]

---

Hi,

I am trying to develop an Excel VBA application which will do the following.

What I am trying to do?

I have Sheet1 which is populated by an SQL Stored Procedure from data in a server, when a button is pressed.

As the sheet is very complex, I permit the user to add any helpful comments anywhere on the sheet.

The data on the server may change, and the user may wish to refresh his data (by pressing a button).

This will delete all data on Sheet1 (including the helpful comments), and replace them with updated data.

But I don't want the user to lose his comments. My idea is to copy all the comments to Sheet2 before erasing and re-populating Sheet1, and then to re-create the comments on Sheet 1 after refreshing, using the details stored in Sheet2.

To find the location of a comment, I don't use Row Number, because this may change, but I use MyIndex field rather.
So, if my original comment was for MyIndex = 112, and was in column B (Field1), then after Refresh, a new comment must be created exactly there!

I am attaching 3 screenshots to explain my idea.

Can anyone help me develop my VBA? I don't even have a clear idea how to do it.
Any help very much appreciated

Thanks
Leon
-------

Sheet1 before Refresh
https://ln.sync.com/dl/9c87a6ef0/ydpv8ubv-6k7gxb45-q8ftgq66-jj4ixrqx

-------
Sheet2 (where details of the comments are kept)
https://ln.sync.com/dl/ddc72d300/brqgwffu-gt7f3hmm-4x44jrjt-jdpd8g7x

------
Sheet1 after Refresh
https://ln.sync.com/dl/d99f87660/xgdfqbah-9y7sntvj-idwjpxz2-rvaw7wuk
------

Here is my code for copying the details of comments to Sheet2:

Dim coment As Comment
Dim cel As Range
Dim Sht1 As Worksheet
Set Sht1 = Worksheets("Sheet1")


Dim Sht2 As Worksheet
Set Sht2 = Worksheets("Sheet2")




For Each cel In Sheet1.UsedRange
   Set coment = cel.Comment
   
   If Not coment Is Nothing Then
   
   
   
   Sht2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Cells(cel.Row, 1).Value
   Sht2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = cel.Address
   Sht2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = coment.Text
   
            
   End If
   
   Next cel
   
End Sub

 

[Kamolga]

Hi,

I would work with an array rather than a sheet. This macro has to be launched from Sheet1

Sub StoreComments()
[COLOR=#008000]'Store Comments in an array (3Columns)[/COLOR]
 Dim sh As Worksheet: Set sh = ActiveSheet
 Dim Cmt As Comment
 Dim x As Long: x = sh.Comments.Count
 If x = 0 Then GoTo NoComment
 ReDim myarray(1 To x, 1 To 3)
    x = 1
    For Each Cmt In sh.Comments
           [COLOR=#008000] 'Store MyIndex[/COLOR]
              myarray(x, 1) = Cells(Cmt.Parent.Row, 1).Value
           [COLOR=#008000] 'Store Comment[/COLOR]
              myarray(x, 2) = Cmt.Text
           [COLOR=#008000] 'Store column Number[/COLOR]
              myarray(x, 3) = Cmt.Parent.Column
     x = x + 1
    Next Cmt
[COLOR=#008000]'If you want to put the array in Sheet2 (useless)[/COLOR]
  Worksheets("Sheet2").Range("A1:C" & UBound(myarray, 1)).Value = myarray
[COLOR=#008000]'Replace your data in sh with SQL...[/COLOR]

[COLOR=#008000]'Put comments back[/COLOR]
On Error Resume Next
 Dim MyIndex As Range: Set MyIndex = sh.Range("A1:A" & sh.Cells(sh.Rows.Count, "A").End(xlUp).Row)
 For x = 1 To UBound(myarray, 1)
  MyIndex.Find(myarray(x, 1)).Offset(0, myarray(x, 3) - 1).AddComment (myarray(x, 2))
 Next
 On Error GoTo 0

NoComment:
End Sub

 

[leonlai]

Hi, Kamolga

Thanks a lot for your quick response.

Your idea of using arrays is great, because it provides a faster route to the solution. I never used arrays before.

Your code works like a charm.

Best Regards,
Leon

 

[Kamolga]

Great thanks for the feedback.

Note that you need to handle a no comment case:

number of rows of ​

the array is the number of comments in the sheet, so if 0 comment, there would be an error. The way I handled it (going to the end of the macro, so end of sub) is wrong as it would not refresh the data (SQL) if there were no initial comment. You can move

nocomment:

and handle it or instead of

[LEFT][COLOR=#333333][FONT=monospace]If x = 0 Then GoTo NoComment
[/FONT][/COLOR][/LEFT]

maybe

On error resume next

would do the trick