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.

 

[Fluff]

When you get the error are any of the words highlighted in blue?

 

[mumps]

Try:

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").AddComment Target.Value
            Case "$F$3"
                .Range("F3").AddComment Target.Value
        End Select
    End With
End Sub

 

[Fluff]

That code will fail is there is already a comment in the cell.

 

[svk01]

Wow, thanks for the help guys. I tried that idea @mumps and here's what I'm receiving - not sure what the yellow highlight means. This might be because there are already comments in those cells, like @Fluff pointed out. Also, there have been no blue highlights thus far.

 

[svk01]

When I reverted back to the original code though it highlighted just a portion of the previous line.

 

[Fluff]

If you are seeing this

then the code window should automatically open to the code with something highlighted like

 

[svk01]

Yes that is the error I am receiving but when I click OK it just exits the error pop up and simply displays the original spreadsheet that was open behind it. For context, I am only able to run the code after I exit the VBA project window (see screenshot 1 below). If I try to run the macros within the window in screenshot 1 then the application does not detect the existence of the macro name and I cannot run. However, once I exit and press alt + F8 it is able to detect the macro name and I can run the code (see screenshot 2 below). Do you think this is a problem?

 

[Fluff]

You do not "run" that type of macro, it's called an Event & will automatically run when you manually change any value in that particular sheet.
if you change F2 on the All Responses sheet the comment on the other sheet should change.

 

[svk01]

Ohhh, okay gotcha, thanks for the explanation @Fluff. This is my first time with VBA so I truly appreciate all your help on this.

I just recently got this error and when I clicked "Debug" it took me to the same line highlighted in yellow in the screenshots above. Any idea?