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, 2023 in Others by Kithuzzz
• 38,000 points
573 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, 2023 by narikkadan
• 63,600 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, 2023 in Others by Kithuzzz
• 38,000 points
1,785 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,250 points
1,126 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
• 23,260 points
1,633 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
1,238 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
3,687 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
• 23,260 points
947 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,690 points
1,079 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, 2023 in Others by narikkadan
• 63,600 points
646 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
• 63,600 points
1,086 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