vba to position comment boxes not working

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have the following vba macro to re-position comment boxes on a worksheet but it's having no effect, would anyone have any ideas as to why that could be?
Many thanks,
VBA Code:
Sub ResetComments()
    
    Dim cmt As Comment
    
    For Each cmt In Sheet1.Comments
        cmt.Shape.Top = cmt.Parent.Top + 10
        cmt.Shape.Left = _
            cmt.Parent.Offset(0, 1).Left + 5
    Next
    
End Sub
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
That works for me.
Are you using the old style comments or the new threaded comments?
Is the code looking at the correct sheet?
 

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows
Hi, thanks for responding.
It was 'ActiveSheet' so I changed it to 'Sheet1' to see if that made a difference.
Re. threaded comments - am using Excel 2016, the comments are generated from a macro using 'AddComment', if that helps in any way?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
What is the name of the sheet you want this to work on, or do you want it to work on the active sheet?
 

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

the active sheet is fine
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
In that case use
VBA Code:
For Each cmt In ActiveSheet.Comments
 

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

That's what I had previously to Sheet1, doesn't make any difference.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Are the comments always visible, or just on mouse over?
 

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows
on mouse over.
after I run this code there is no change to the comment box positions
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
That only affects the position of the comment if it's permanently visible.
Not sure it's possible to change the position of the comment when displayed via mouse over.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,225
Messages
5,623,488
Members
415,973
Latest member
charlesbm

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