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

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77
ydpv8ubv-6k7gxb45-q8ftgq66-jj4ixrqx
ydpv8ubv-6k7gxb45-q8ftgq66-jj4ixrqx

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:


Code:
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi,

I would work with an array rather than a sheet. This macro has to be launched from Sheet1
Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
Code:
nocomment:
and handle it or instead of
Code:
[LEFT][COLOR=#333333][FONT=monospace]If x = 0 Then GoTo NoComment
[/FONT][/COLOR][/LEFT]
maybe
Code:
On error resume next
would do the trick
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top