CODEWITHSUNDEEP
excel
z8080
z8080

Reputation: 659

VBA code to automatically resize cell comment

When one adds a cell comment in Excel (2016), the comment box is notoriously suboptimally-sized and needs manual adjustment. I defined this macro (with VBA code that I got from here) to automatically resize the cell comment that appears when the mouse is hovered above a cell:

Sub FitComments()
Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments
    xComment.Shape.TextFrame.AutoSize = True
Next
End Sub

However, I would like this macro to instead:

  1. resize the comment box not just on one line but across multiple lines, optimising the rectangle's aspect ratio (the way Google Spreadhseets automatically resizes any cell comment box, the second it is created)
  2. act not upon all cell comments in the sheet but only upon the current cell's comment

I have however no real knowledge of VBA. Can anyone point out what changes I'd have to make to the code? Thanks!

Upvotes: 4

Views: 15098

Answers (2)

Directionsky
Directionsky

Reputation: 106

Sub Resize_All_Comments()

Dim xComment As Comment
Dim KHeight As Long
Dim KWidth As Long

On Error Resume Next

KHeight = Application.InputBox("Add text", "Height", "500", Type:=2)
KWidth = Application.InputBox("Add text", "Width", "500", Type:=2)

For Each xComment In Application.Select.Comment
    xComment.Shape.Width = KWidth
    xComment.Shape.Height = KHeight

Next
MsgBox "Done"
End Sub

using this you can resize according to you requirements, note it will resize all the comments in the current sheet.

Hope it helps someone

Upvotes: 4

Jason Cameron
Jason Cameron

Reputation: 91

Sub FitComments()
Dim Rng As Range
Dim Cell As Range
Set Rng = Selection
For Each Cell In Rng
    If Not Cell.Comment Is Nothing Then
    Cell.Comment.Shape.TextFrame.AutoSize = True
    End If
Next
End Sub

Excel 2016 is not available at my current location to test the above or I would have done so before posting. I am running Office 365 which automatically does the resizing as you describe. Multiple versions were tested until the macro did not error.

The concept behind this code is to apply autosize to the comments within the selection on a per cell basis.

I applied the macro to a Control + (letter of choice) keyboard activation for quick access.

Stepping into the code on a line by line basis, it ran through the expected number of steps for the test selection.

Upvotes: 6

Related Questions