Automatically updating comments across worksheets

[svk01]

Hello, I am using VBA code extracted from Andrew Poulsom's prior post. My version is pasted below:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Worksheets("HeatMap - Strengths+Weaknesses")
Select Case Target.Address
Case "$F$2"
.Range("F2").Comment.Text Text:=Target.Value
Case "$F$3"
.Range("F3").Comment.Text Text:=Target.Value
End Select
End With
End Sub

However, when I try to run this code I continuously receive an "argument not optional" error and despite many hours of debugging I cannot figure out how to fix it. For your reference, the below code has been attached to a sheet called "All Responses." This sheet contains data that has been copied into comments placed in a second sheet (within the same workbook) called "HeatMap - Strengths+Weaknesses." As you can probably tell, the locations of the originating cells in "All Responses" mirrors those in "HeatMap - Strengths+Weaknesses" because the aim here is to use the comment to elaborate on a score given to each response in "All Responses." Do you have any idea why the compiler is not receiving an argument and is thus outputting this error? By the way, I am unaware of the exact code posing problems because it doesn't highlight any code when the error is outputted.

 

[svk01]

Also tried changing F2 in All Responses and the corresponding comment in the other sheet did not update :/

 

[Fluff]

That suggests there is no comment on F2 in the HeatMap - Strengths+Weaknesses sheet.

 

[svk01]

Just tried to run it after pressing alt + F8 and received this error as well?

 

[svk01]

That suggests there is no comment on F2 in the HeatMap - Strengths+Weaknesses sheet.

There is, unfortunately.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[svk01]

Right, okay, so sorry about that! Its Microsoft 365, version 2008, windows.

 

[Fluff]

Ok do you have a comment in F2 or a Note?
ie do you see a red triangle or a purple splodge?

 

[svk01]

It's a 'purple splodge' haha (love the term btw)

 

[Fluff]

Ok that is a new style comment, whereas the code you are using is for the old style comment now called Note

 

[Fluff]

Try it like

.Range("F2").CommentThreaded.AddReply Target.Value