VBA Comment Format

0 votes

I'm having trouble with a section of code because I'm a novice at coding in Excel.

I know how to establish a fixed size for a single cell, but I'm not sure how to do it for the entire sheet. I want to be able to set a fixed size for the comment boxes.

Here is what I tried to accomplish, but it wasn't successful. Any assistance would be greatly valued.

Sub com2()
  Dim lArea As Long, h As Long, n As Long
  With Range("ActiveSheet").Comment
    n = WorksheetFunction.RoundUp(Len(.Text) / 100, 0)
    .Shape.TextFrame.AutoSize = True
    h = .Shape.Height
    If .Shape.Width > 250 Then
      .Shape.Width = 250
      .Shape.Height = 250
    End If
  End With
End Sub
Feb 27 in Others by Kithuzzz
• 35,300 points
27 views

1 answer to this question.

0 votes

Let's say you're having trouble referencing every comment in a specific worksheet.

Sub AutoSizeComments()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Tabelle4
    'Loop through comments
    Dim c As Range
    For Each c In ws.Cells.SpecialCells(xlCellTypeComments)
        adaptCommentSize c
    Next c
    Application.ScreenUpdating = True
End Sub
Sub adaptCommentSize(c As Range, _
    Optional ByVal w As Double = 250, _
    Optional ByVal h As Double = 250)
    With c.Comment.Shape
        With .TextFrame
            .AutoSize = True
            .AutoMargins = False
            .MarginBottom = 0
            .MarginTop = 0
            .MarginLeft = 0
            .MarginRight = 0
        End With

        If .Width > w Then
            .Width = w
            .Height = h
        End If

    End With
End Sub
answered Mar 18 by narikkadan
• 60,820 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21 in Others by Kithuzzz
• 35,300 points
39 views
0 votes
1 answer

Get files in my own file format for its own dynamic icon

Regarding MAC support, "The Mac and Windows ...READ MORE

answered Jan 8, 2019 in Others by DataKing99
• 8,240 points
678 views
0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,970 points
768 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
577 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,563 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,970 points
149 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
392 views
0 votes
1 answer

Excel VBA userform paste text in expanded format (not just to one cell)

It should work if you simply supply ...READ MORE

answered Jan 21 in Others by narikkadan
• 60,820 points
82 views
0 votes
1 answer

Excel VBA if file closed, then open and paste, else just paste data

Slightly re-worked to add full workbook/sheet qualifiers ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 60,820 points
237 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP