Automatically updating comments across worksheets

svk01

New Member
Joined
Mar 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
When you get the error are any of the words highlighted in blue?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
Try:
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
That code will fail is there is already a comment in the cell. ;)
 

svk01

New Member
Joined
Mar 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

1616178040600.png
 

svk01

New Member
Joined
Mar 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
When I reverted back to the original code though it highlighted just a portion of the previous line.

1616178354496.png
 

Attachments

  • 1616178265660.png
    1616178265660.png
    33.1 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you are seeing this
1616178290009.png

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

1616178402302.png
 

svk01

New Member
Joined
Mar 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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?

1616179329188.png

1616179382673.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 19, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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?

1616181121681.png
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,577
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top