Autosize Comments Height, but specify set width.

[Jason Campbell]

Hi all,

I have the need to specify comments to Auto-size in respect of height, but have a specified/static width of 9.26cm.

The reason for this is that the comments are being used as a guide to requirements that must be met to enrol on a course. The comment box cannot exceed the width of 9.26cm, however must be able to auto-size in respect of height, so as to enable to written content to be displayed and viewed.

As a new fledgling to Excel, I'm now aware that there are numerous gremlins as regards to the Excel Comments and will need to make use of VBA to get the comments to do exactly what I want.

I've already made use of the below macro to realign/anchor comments that keep jumping all over the place, thanks to a post on MrExcel... all I need to do now is auto-size the comment box height and give it a static specific width of 9.26cm!!

Could anyone give me a few pointers?

Further, whenever the data is filtered, I have to run the macro below again...is there anyway that I can create a macros to automatically re-anchor the comments after clearing the filters?

I thought Microsoft Would of nailed this one by now...

Sub ResetComments()


Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
cmt.Shape.Top = cmt.Parent.Top + 5
cmt.Shape.Left = _
cmt.Parent.Offset(0, 1).Left + 5
Next

End Sub

 

[igold]

Hi Jason,

Is this what you are looking for:

Sub ResetCommets()


    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        cmt.Shape.Top = cmt.Parent.Top + 5
        cmt.Shape.Left = _
        cmt.Parent.Offset(0, 1).Left + 5
[COLOR=#ff0000]        cmt.Shape.TextFrame.AutoSize = True[/COLOR]
    Next


End Sub

HTH

igold

 

[Jason Campbell]

Hi IG,
That is close to what I want to achieve, however I don’t want the width of the comment box to exceed 9.26cm.
I want the height of the comment box to auto size to accommodate long entries of text that will push down to the next row.

Example:
|--------Width to remain 9.26cm---------|
1. Blah blah blah blah blah blah blah..
Blah blah blah blah blah.
2. blah blah blah blah blah blah blah.
3.Blah blah de blah de blah blah…

As opposed to:
|-----------------------------AutoSizeMacro-------------------------------|
1. Blah blah blah blah blah blah blah Blah blah blah blah blah.
2. blah blah blah blah blah blah blah.
3.Blah blah de blah de blah blah…

I hope my attempted illustration of the issue at hand has been able to say what I’m trying to describe?
Anyway, thanks for the response in the first place.
Jason

 

[igold]

Hi Jason,

I can't find anything on wrapping the text. About the only thing I can think of is to have all comments a preset Height and width, which you would have to play with to get the size you are looking for.

Sub ResetCommets()


    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        cmt.Shape.Top = cmt.Parent.Top + 5
        cmt.Shape.Left = _
        cmt.Parent.Offset(0, 1).Left + 5
[COLOR=#ff0000]        cmt.Shape.Width = 100[/COLOR]
[COLOR=#ff0000]        cmt.Shape.Height = 75[/COLOR]
    Next


End Sub

igold

 

[igold]

One last thought...

Perhaps you could have your height predicated on the number of characters in the comment. I just made these parameters up, but the concept does work and you would have to find criteria that works best in your situation...

Sub ResetCommets2()


    Dim ct As Integer, [COLOR=#ff0000]ht As Integer[/COLOR]
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        [COLOR=#ff0000]ct = cmt.Shape.TextFrame.Characters.Count[/COLOR]
        cmt.Shape.Top = cmt.Parent.Top + 5
        cmt.Shape.Left = _
        cmt.Parent.Offset(0, 1).Left + 5
        cmt.Shape.Width = 100
[COLOR=#ff0000]        If ct < 30 Then ht = 20[/COLOR]
[COLOR=#ff0000]        If ct >= 30 And ct <= 60 Then ht = 40 ' and so on...[/COLOR]
[COLOR=#ff0000]        cmt.Shape.Height = ht[/COLOR]
    Next


End Sub

HTH

igold

 

[Jason Campbell]

Hi IG,

Thanks for the additional posts. I will give your last option a play to see if I can get it to work. The crux of the matter is that I need the width to be fixed, which works great with the cmt.Shape.Width = 260 parameter set...but the height I need to be dynamic and adjust accordingly.

I will be sure to let you know how it goes if I can get it to work on your last idea.

Thanks again

Jason

 

[igold]

I look forward to knowing how you make out. If you get it to work, please post the code so that I can save it away in case I need to apply it in the future.

igold

 

[Jason Campbell]

Hi IG,

All credit to Dana DeLouis, whose post I located, which does exactly what is required with a few minor adjustments for my specific setup:

Sub ResetShapeComments()

'Originally posted by Dana DeLouis 2000-09-16 - found and tweaked for my purposes...thank Dana for the post!
Dim MyComments As Comment
Dim lArea As Long

For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 262 Then
lArea = .Shape.Width * .Shape.Height
'Width of 262 gets me as close to 9.26cm as feasible
.Shape.Width = 262
.Shape.Height = (lArea / 250) * 1.1
End If
End With
Next ' comment

Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
cmt.Shape.Top = cmt.Parent.Top + 5
cmt.Shape.Left = _
cmt.Parent.Offset(0, 1).Left + 5
Next

End Sub

With the above, I'm able to specify the width I want to remain static and let the height adjust dynamically without too much open space after the last entry of text. Of course, minor tweaking of the figures will be required to make the macro suit your needs.

Thanks again to Dana DeLouis for the original post!

 

[igold]

Hi Jason,

Thanks for the heads-up. I am glad you were able to find what you were looking for...

igold

 

[tbrock]

.Shape.Width = 262
.Shape.Height = (lArea / 250) * 1.1

Thanks for this. I have the exact same issue. While I have seen the code snippet elsewhere, there is a modification here though that I had not seen... lowering the denominator (from the actual comment width). I will play with this idea.

My issue is that I have some *long* comments which rely upon word wrapping for paragraphs. Autosizing makes these *very* wide resulting in a lot of white space in the comment. When those are plugged into this formula (specifying a lesser width), I often end up with an area size greater than the original (autosized) and therefore with a lot of blank lines at the bottom. (sigh)

SURE seems like maximum width would be an obvious limit for the autosize function.