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
Sheet1 before Refresh
Sheet2 (where details of the comments are kept)
Sheet1 after Refresh
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