Solved

Excel 2013 - Comments keep changing size

Posted on 2014-03-13
12
219 Views
Last Modified: 2014-05-04
Once I create a comment and it is properly sized, if I come back later it is often shrunk in size as to be unreadable.  I have not figured out what changes elsewhere in the workbook I might have made but it is very frustrating to keep re-sizing the comments.

Is there a way to lock their size?

Thanks!
0
Comment
Question by:gantone1
  • 6
  • 6
12 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39929268
You will find this link helpful on Comments for Excel 2013

http://www.youtube.com/watch?v=S6bUI4Xz7JM

gowflow
0
 

Author Comment

by:gantone1
ID: 39940282
I did not notice anything that clearly locked a comment box size though I did find 'Lock aspect ratio' which I am not clear if it causes comment box to retain shape.  Do you know if that will prevent the comment box from changing size?  If so is there a way to apply this setting to all comments without touching each one?

Thanks!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39940346
could you post the wb that have this problem ? and when does it resize ?
gowflow
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 29

Expert Comment

by:gowflow
ID: 39940384
I think this may solve your issue somehow. If you need help integrating it let me know.

http://discussions.virtualdr.com/showthread.php?237673-Excel-comments-moving-and-changing-sizes

gowflow
0
 

Author Comment

by:gantone1
ID: 39973570
Wow, that macro looks like the solution to my problem, but I am not familiar with macros and could waste a lot of time getting this to work.  If you could give me some tips it would be greatly appreciated.

Thanks!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39974550
ok you will need to post a sample of your workbook so I could integrate it in.
gowflow
0
 

Author Comment

by:gantone1
ID: 39975288
I copied one of my sheets to the attached file.  This was a work in process so please ignore any other problems you find with it.  But for the list boxes there are some examples that are messed up, for example G19, whereas F19 has already been fixed (for now at least!).

Please let me know if this is a good enough example.

Thanks!
Test.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39975721
ok fine here it is:

I have put the macro (modified and embellished) in a module in your workbook and saved it as an .xlsm (macro enabled workbook) and ran it on the file. check the file to see if all is ok.

here is the code.

Sub CommentFix()
On Error GoTo NeedToUnprotect

' This macro modifies all comments in all open workbooks to:
' (1) move and size with cells
' (2) be physically positioned near the cell to which they correspond
' (3) be optimally sized appropriate to the text within
' This macro was created by combining code posted on the following website:
' http://www.contextures.com/xlcomments03.html
' Modified and formated by gowflow on Apr 3, 2014

'---> Declarations
Dim ThisFile As Workbook
Dim MyWorkbook As Workbook
Dim MySheet As Worksheet
Dim MyComment As Comment
Dim CommentCount As Long
Dim lArea As Long
Dim Fixed As Boolean

'---> Set Variables
Set ThisFile = ActiveWorkbook
Fixed = False


For Each MyWorkbook In Workbooks
    MyWorkbook.Activate
    
    For Each MySheet In MyWorkbook.Sheets
        MySheet.Activate
        CommentCount = 0
        For Each MyComment In MySheet.Comments
            '---> Fix the Comment
            With MyComment.Shape
                .Placement = xlMoveAndSize
                .Top = MyComment.Parent.Top + 5
                .Left = MyComment.Parent.Offset(0, 1).Left + 5
                .TextFrame.Characters.Font.Name = "Tahoma"
                .TextFrame.Characters.Font.Size = 8
                .TextFrame.AutoSize = True
                CommentCount = CommentCount + 1
            End With
            
            '--> Deal with Large Comments
            If MyComment.Shape.Width > 300 Then
                lArea = MyComment.Shape.Width * MyComment.Shape.Height
                MyComment.Shape.Width = 200
                MyComment.Shape.Height = (lArea / 200) * 1.1
            End If
            
        Next MyComment
        
        '---> Advise User
        If CommentCount > 0 Then
            MsgBox ("A total of " & CommentCount & " comments in worksheet '" & MySheet.Name & "' of workbook '" & MyWorkbook.Name & "'" & Chr(13) & "were repositioned and resized.")
            Fixed = True
        End If
    Next MySheet
Next MyWorkbook

ThisFile.Activate

If Fixed = False Then
    MsgBox ("No comments were detected.")
End If
On Error GoTo 0
Exit Sub

NeedToUnprotect:
    MsgBox ("You must unprotect all worksheets before running the macro.")
    ThisFile.Activate
    Exit Sub

End Sub

Open in new window



Upon your reply I will advise how to make it work for you.
gowflow
Test-Comments-Fix-V01.xlsm
0
 

Author Comment

by:gantone1
ID: 39978813
WOW!  I had your version open, then opened mine (backup of course!) and I could see the macro in your version.  When I ran it for all sheets it fixed all my sizing problems!!!!!!

- Once I closed your version I could not see the macro in my version.  How do I load it up so that it is part of my spreadsheet?

- When I distribute this spreadsheet the pages will be locked so the text boxes should not change.  I would like to distribute without the macro or have it disables to that customers do not get warnings that file contains macro.  What is best way to do this.

Thank You VERY MUCH!!!!
0
 

Author Comment

by:gantone1
ID: 39979212
After running the macro on my spreadsheet and fixing the comments, I got the following message when saving the document.

Be Careful! Parts of your document man include personal information that can't be removed by the Document Inspector.

I have never seen this message and it is kind of alarming.  Do you know what would cause this?

Thanks!
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39979730
yes it is Inspector I had it too as took your file and just added the macro. and usually this link explains how to deal with this its no worry as there are comments or hidden columns when you run the Inspect and then says to accept to save the results then you won't get this anymore.

I did this on the version attached.

Now to answer your question you need something like an addin if you want ask a new question and I could help u with it as this one got already enough crowded like this.

gowflow
Test-Comments-Fix-V01.xlsm
0
 

Author Closing Comment

by:gantone1
ID: 40041181
This macro worked great.  I will ask a separate question about the Inspector
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Cascading drop down lists in Excel 4 116
Excel Power Pivot - Aggregation 7 81
Excel sort for duplicate records 7 76
Excel Formula return each instance from list 2 75
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question