Solved

Excel 2013 - Comments keep changing size

Posted on 2014-03-13
12
212 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
Comment Utility
You will find this link helpful on Comments for Excel 2013

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

gowflow
0
 

Author Comment

by:gantone1
Comment Utility
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
Comment Utility
could you post the wb that have this problem ? and when does it resize ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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
Comment Utility
ok you will need to post a sample of your workbook so I could integrate it in.
gowflow
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:gantone1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
This macro worked great.  I will ask a separate question about the Inspector
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now