Resize & Reposition Comments

[collectorskingdom]

Hello. I'm new to the forum and I am not well versed in VBA. As you are aware, comments

frequently get moved and resized. ​

With the help of a friend, I was able to get the following macro to

properly resize and reposition all comments in each worksheet within my workbook. I​

t worked great for about 6 months, but now it does not work any more.

When I "comment out" the "On Error Resume Next,"

I receive a "Run-time error '1004': Application-defined or object-defined error." ​

The macro errors at "cmt.Shape.TextFrame.AutoSize = True."​

I tried some of the solutions I saw in earlier threads that appeared to serve the same purpose, but I could not get them to work properly.

Here's what used to work for me:
Public AppWB As Workbook
Sub Autpen()
AppWBName = ThisWorkbook.Name
Set AppWB = Workbooks(AppWBName)
Dim ws As Worksheet
On Error Resume Next
For Each ws In AppWB.Worksheets
For Each cmt In ws.Comments
'Modify your comment code
cmt.Shape.TextFrame.AutoSize = True
cmt.Visible = False
cmt.Shape.Top = cmt.Parent.Top - 10
cmt.Shape.Left = cmt.Parent.Offset(,1).Left + 10
Next
Next ws
End Sub

Thank you in advance.

 

[JLGWhiz]

It might be looking for a Dim statement

Dim cmt As Object

I don't see a problem with the syntax if you are using a PC. Don't know about MAC. Also, make sure you have at least one comment.

 

[collectorskingdom]

JLGWhiz, thank you for responding. I am using a Windows 10. I tried using "Dim cmt As Object" and ensuring there is a comment on every worksheet, but it did not solve the problem. I pasted my original code in a new workbook with a simple comment and it worked just fine.

 

[JLGWhiz]

JLGWhiz, thank you for responding. I am using a Windows 10. I tried using "Dim cmt As Object" and ensuring there is a comment on every worksheet, but it did not solve the problem. I pasted my original code in a new workbook with a simple comment and it worked just fine.

That seems to eliminate the basic code as the problem. Now, the trick is to find out what in the previous file prevented the code from producing the desired results. Kind of hard to troubleshoot that one from afar.
regards, JLG

 

[collectorskingdom]

That seems to eliminate the basic code as the problem. Now, the trick is to find out what in the previous file prevented the code from producing the desired results. Kind of hard to troubleshoot that one from afar.
regards, JLG

Thanks, JLG. I was afraid you'd say that. I guess it's back to the drawing board. Thanks for your help. Best wishes!