VBA "auto-sizing" comment box

[DCPan]

Hi all,

The script I attached below will place what is typed in the cell into a
comment box, then replace the cell with "see comments" and delete the comment
box when "see comments" is erased.

Now, the question is...I have auto-size turned on...but why does the comment
box only format itself "length wise"?

Can I specify a comment box width and only have it autosize vertically?

Thanks!
_________________

If Target.Row > 1 And Target.Column = 14 _
And Range(strRange).Value <> "See Comment" _
And Range(strRange).Value <> "" Then

Range(strRange).Value = "See Comment"
Range(strRange).Select
Range(strRange).AddComment
Range(strRange).Comment.Visible = True
Range(strRange).Comment.Text Text:=strComment
Range(strRange).Comment.Shape.Select True
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With
Range(strRange).Comment.Visible = False
Range(strRange).Select
'Range("A" & (Target.Row + 1)).Select

Else

If Target.Row > 1 And Target.Column = 14 _
And Range(strRange).Value = "" Then

Range(strRange).Select
Range(strRange).ClearComments

End If

Range(strRange).Select
'Range("A" & (Target.Row + 1)).Select

End If

 

[Sheeloo]

Suppose somehow you were able to autosize the width...
how would you expect to see a comment like "Test Comment"? Comment box
expanded vertically with one letter per line?
If yes then use
..Orientation = xlVertical

If you want horizontal orientation and get the comments adjusted vertically
then while typing the comment you need to do ALT-ENTER for second line or
insert that through code where you want the comment to expand vertically...

 

[DCPan]

So, for example, if I have something like "Knight Rider 2008 show isn't going
to make it past Season 1", I hope it shows up like"

Knight Rider 2008
isn't going to make
it past Season 1

So, is there any way I could specify something like width 16, autoheight?

Thanks!

 

[Rick Rothstein]

Where is this code located... in an event procedure like Worksheet_Change?
What is strRange and, if it is what I think it is, why are you using
Range(strRange) instead of Target in your code?

 

[DCPan]

The code is located in the worksheet for worksheet_change.

For some strange reason, when I use the target in my code, it fails, that's
why I handed Target.AddressLocal off to strRange.

Thanks!

 

[Sheeloo]

There is no method provided by VBA but you can do something like this;
With mc 'mc having reference to the comment
.Shape.TextFrame.AutoSize = True
shapeArea = .Shape.Width * .Shape.Height
.Shape.Width = 50
.Shape.Height = shapeArea / .Shape.Width
End With

 

[DCPan]

That worked beautifully! Thanks again!

There is no method provided by VBA but you can do something like this;
With mc 'mc having reference to the comment
.Shape.TextFrame.AutoSize = True
shapeArea = .Shape.Width * .Shape.Height
.Shape.Width = 50
.Shape.Height = shapeArea / .Shape.Width
End With

 

[Rick Rothstein]

How about something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Target.Count <> 1 Then Exit Sub
If .Value <> "See Comment" Then
If .Row > 1 And .Column = 14 Then
.ClearComments
If .Value <> "" Then
.AddComment .Value
.Comment.Shape.Width = 95
.Application.EnableEvents = False
.Value = "See Comment"
.Application.EnableEvents = True
End If
End If
End If
End With
End Sub

 

[DCPan]

Thank you for teaching me structure!

You rock!

How about something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Target.Count <> 1 Then Exit Sub
If .Value <> "See Comment" Then
If .Row > 1 And .Column = 14 Then
.ClearComments
If .Value <> "" Then
.AddComment .Value
.Comment.Shape.Width = 95
.Application.EnableEvents = False
.Value = "See Comment"
.Application.EnableEvents = True
End If
End If
End If
End With
End Sub

 

[DCPan]

I forgot to put that safety to prevent the code from crashing as well when
multiple targets are selected.

I guess you once you hit the exit sub, it doesn't matter, but I thought you
are one "End If" short?

Thanks again!

 

[DCPan]

What does the application.enableevents do? Does it keep it from going into
an infinite loop when you reset the cell value with "see comment"?

 

[Sheeloo]

YES, you got it.

It prevents Worksheet_Change event from firing again in this. In general it
prevents all applicable application events from firing.

Thanks for your feedback...
--

 

[Rick Rothstein]

There are two types of If..Then statements... a single line method
(statement to be executed is located on the same line as the If...Then
statement) and a block structured method (multiple lines of code to be
executed for the give logical test). Only the block method requires the End
If statement (it tells the If..Then statement where the last line controlled
by its logical test is located at); the single line method, by its very
nature, does not need to signal where the last line is because there is only
one statement and it is located on the same line immediately following the
Then statement.

 

[Rick Rothstein]

Not an infinite loop (at least not in this case, but it is definitely
possible to construct code that will loop indefinitely), rather it stops an
unnecessary loop from occurring. When this line is executed...

..Value = "See Comment"

the assignment causes a Change event to fire. In the case of your code,
nothing will happen because the entry ("See Comment") is not one that will
cause your code to take any action, but there is no reason to let the action
cause the extra event firing... setting EnableEvents to False makes VB not
execute ANY other events until it is set to True again. Your code is simple
and turning it off and then on again around the assignment is easy to do
here; however, there are lots of situation where that is not the case. For
example, in those cases where an error could occur before you turn
EnableEvents back on, it is imperative to use an error handler. For example,
in pseudo-code...

Private Sub Worksheet_Change(ByVal Target As Range)
If <<some test>> Then
On Error GoTo CleanUpAfterError
Application.EnableEvents = False
Target.Value = <<some value>>
'
' More code here which could possibly generate an error
'
End If
Exit Sub
CleanUpAfterError:
Application.EnableEvents = True
'
' Other clean up code, if any, goes here
'
End Sub

 

[DCPan]

I was wondering how error handling in Excel VBA worked...I'm just fumbling my
way through as I'm slightly more familiar with Access VBA.

Thanks again for taking the time to help me out!

David