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

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77

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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,176
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:

leonlai

Board Regular
Joined
Mar 22, 2019
Messages
77
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

Well-known Member
Joined
Jan 28, 2015
Messages
1,176
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,353
Messages
5,528,197
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top