VBA to AutoSize comments in cells

[Kra]

Hi all,

I am trying to set up a macro which will loop through all sheets and autosize all comments and change their properties to Move but don't size with cells. I created test comment and macro, but somehow it is not working (no errors, macro goes smoothly without changing anything in the sheet). Any ideas how to fix it and how to achieve this Move but don't size with cells? I was not able to set it up

Private Sub CommandButton2_Click()

Dim ws As Worksheet
Dim com As Comment
Dim rng As range
Dim celcom As range


    For Each ws In ThisWorkbook.Worksheets
        ws.Activate
            Set rng = ws.UsedRange
                For Each celcom In rng
                    If Not celcom.Comment Is Nothing Then
            
                            celcom.Comment.Shape.TextFrame.AutoSize = True
                            celcom.Comment.Shape.Top = celcom.Parent.Top + 15
                            celcom.Comment.Shape.Left = celcom.Parent.Offset(0, 1).Left + 15
                    
                    End If
                Next celcom
    Next ws
        
End Sub

 

[Kra]

I managed to fix code, but I still cannot set the settings of comment to Move but don't size with cell

Dim ws As Worksheet
Dim com As Comment
Dim rng As range
Dim celcom As range


    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
            Set rng = ws.UsedRange
                For Each celcom In rng
                    If Not celcom.Comment Is Nothing Then
            
                            celcom.Comment.Shape.TextFrame.AutoSize = True
                    
                    End If
                Next celcom
                
                For Each com In ActiveSheet.Comments
                    com.Shape.Top = com.Parent.Top + 15
                    com.Shape.Left = com.Parent.Offset(0, 1).Left + 15
                Next com
    Next ws

 

[Kra]

I managed to fix code, but I still cannot set the settings of comment to Move but don't size with cell

Dim ws As Worksheet
Dim com As Comment
Dim rng As range
Dim celcom As range


    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
            Set rng = ws.UsedRange
                For Each celcom In rng
                    If Not celcom.Comment Is Nothing Then
           
                            celcom.Comment.Shape.TextFrame.AutoSize = True
                   
                    End If
                Next celcom
               
                For Each com In ActiveSheet.Comments
                    com.Shape.Top = com.Parent.Top + 15
                    com.Shape.Left = com.Parent.Offset(0, 1).Left + 15
                Next com
    Next ws

Ok I fixed it as well, added line

com.Shape.Placement = xlMove

to loop

For each com