vba to re-position cell comments for selected range only

chasoe

Board Regular
Joined
Jan 29, 2006
Messages
64
Dear Sirs,

From time to time, my worksheets cell comments will mysteriously got strayed with comment box positions gone far far away, especially when filtering is in place in the sheet.
I have searched the Internet for the following codes which runs fine for re-positioning cell comments for whole sheet :

Code:
Sub ResetSheetCommentBoxPosition()
            
'Reset Comment box to Original Position FOR WHOLE SHEET

Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
   cmt.Shape.Top = cmt.Parent.Top + 5
   cmt.Shape.Left = _
      cmt.Parent.Offset(0, 1).Left + 5
Next
            
End Sub

But when I cannot modify the codes for selected range of cells :
Code:
Dim cmtx As Comment
Dim WorkRng1 As Range

For Each cmtx In WorkRng1.Comment
    cmtx.Shape.Top = cmtx.Parent.Top + 5
    cmtx.Shape.Left = cmtx.Parent.Offset(0, 1).Left + 5
Next
           
End Sub

Would appreciate if anyone could help.

Many thanks.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Are you looking to select a range of cells before running the code, or do you want to hardcode the range?
 

chasoe

Board Regular
Joined
Jan 29, 2006
Messages
64
Yes, correct, I just want to first highlight the desired range (some with comment boxes or while some may not), and then apply the codes on selected range only.

The first set of codes on whole sheet is OK. The second set of codes on selected range is my modification but doesn't work, and will be stuck on the third line.

The reason for only applying to selected range is that, when I'm focussing on a particular area in the sheet and want to read the details in the comment box but they have been placed outside the screen area for unknown reason. I don't want to spend time to scroll up / down / left / right to locate the box position. So the quickest way is to reset these comment boxes to my desired default position which is desirably close to the cell.

Should be simple to experts, but I've racked my brain without success.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,267
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
Dim cmtx As Comment
Dim WorkRng1 As Range

    Set WorkRng1 = Selection

    For Each cmtx In ActiveSheet.Comments

        If Not Intersect(WorkRng1, cmtx.Parent) Is Nothing Then
            cmtx.Shape.Top = cmtx.Parent.Top + 5

            cmtx.Shape.Left = cmtx.Parent.Offset(0, 1).Left + 5
        End If

    Next cmtx

[/code]
 

Watch MrExcel Video

Forum statistics

Threads
1,127,331
Messages
5,624,057
Members
416,007
Latest member
csf

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