VBA to autosize comment box in MS Excel 2011 for Mac not working in MS Excel 2016 for Mac. Ask Question Asked 1 year, 4 months ago. Active 1 year, 4 months ago. Viewed 371 times 2. Due to the purchase of a new MacBook Pro running macOS 10.12.6 (High Sierra), I've updated to MS Excel 2016 and the following code which worked well in MS Excel 2011. I'm experiencing a strange problem when trying to autosize comments. I'm running a For/Next loop where I place data according to certain criteria in a row of cells, then add a comment (or update an existing comment if one is already there).I've looked everywhere on the web and can't seem to find any information relating to this kind of error associated with comment autosizing. I would be extremely grateful for any suggestions from the experts here.
The relevant section of my code is as follows:
If Not Sheet12.Cells(TempRow, TempCol).Offset(0, ContractLength).Comment Is Nothing Then
This is the first time I am submitting something to the forum, so please excuse me if for some reason I am doing anything to violate forum protocol. I did read the rules carefully but may have missed something, and if so I apologize in advance. Please let me know if that is the case and I will try to make any corrections right away to this post to make it compliant.
Thanks very much,
How do I get a macro to resize a comment box?
You can get the code by using the macro recorder.
'Written by OzGrid Business Applications
Dim CBox As Comment
Set CBox = Range('F10').Comment
.Text Text:='OzGrid Business Applications:'
.Shape.ScaleWidth 1.49, msoFalse, msoScaleFromBottomRight
.Shape.ScaleHeight 1.24, msoFalse, msoScaleFromBottomRight
Set CBox = Nothing
OzGrid Business Applications
Ben if you want to resize ALL comment boxs
in an activesheet OR All sheets then this routine
may help you. Rsizing is @ 25% change as required.
Or scale down.
Dim cCell As Range
Dim sComment As Comment
Dim allComments As Range
Dim Sh As Worksheet
Dim Ans As Integer
Dim All As Boolean
Ans = MsgBox('Activesheet (Yes) or ALL sheets (No)', vbYesNoCancel)
If Ans = 2 Then Exit Sub
All = IIf(Ans = 7, True, False)
If Not All Then Set Sh = ActiveSheet: GoTo skipSh
It marks the user Documents and Pictures folders as protected by default, and you can add more folders. Avast cleanup pro for mac torrent.
For Each Sh In ActiveWorkbook.Sheets
On Error Resume Next
Set allComments = Sh.Range('A1').SpecialCells(xlCellTypeComments)
If allComments Is Nothing And Not All Then MsgBox 'No comments in ' & ActiveSheet.Name: GoTo Ex
'Leave On Error Resume next ON to take care of Loop not set error
'when doing active sheet only.
For Each cCell In allComments
'lock aspect to get even increase in size
.Shape.LockAspectRatio = True
'increase by 25% change as required
.Shape.Height = .Shape.Height * 1.25
Set allComments = Nothing
Set Sh = Nothing
Off topic..more or less
Ivan -- I introduced you to a site for Dutch accountants & accountancy students. See:
And also a long overdue: Welcome back.
Re: Off topic..more or less
Great..many thanks for that. I see that you
have been busy on this site. Keep up the great
work. I'm sure you have helped a lot of people.
Thanks all of you