Hi,
I have been asked by work to create a spreadsheet where records are constantly changing as a result of a risk rating. Each month a new set of data is pasted into the master spreadsheet removing all data from the previous month. There are some constants that do not change month on month such as the client reference number. I have now been asked to link a comments box that allows users of the spreadsheet to add their comments against the relevant reference number. The problem here was that I needed to protect these comments (to create a rolling commentary) from being deleted when the new records were being copied in. After a lot of trying i used the below code to solve this issue:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "AJ1:AJ1000"
Dim SavedVal As Variant
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
SavedVal = .Value2
Application.Undo
If SavedVal <> .Value2 Then
.Value2 = SavedVal
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Now the challenge I face is linking these comments to the client reference number so that if a clients risk rating changes and they fall down the list the comment would automatically follow. I have toyed with the idea of a vlookup string but so far I have been unsuccessful in my attempts. Any ideas would be greatly appreciated.
Bookmarks