Excel VBA and Comment boxes

D

DCPan

Hi,

I wrote something that would automatically place the cell content into a
comment box, and replace the cell content with the words "See Comments".

Now, the user wants the comment box to auto-resize....how come I can't get
the comment box selection statement to work?

Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strComment As String
Dim strRange As String
strComment = Target.Value
strRange = Target.AddressLocal

'Set Target Row number to avoid this running on changing the header
'Set Target Column to determine column being modified
'Set Target Value so there's no infinite loop from "See Comment" feed

If Target.Row > 1 And Target.Column = 2 _
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 = False
Range(strRange).Comment.Text Text:=strComment

Range(strRange).Comment.Shape.Select True <- FAIL RIGHT HERE
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With

End If

End Sub
 
S

Sheeloo

Change
Range(strRange).Comment.Visible = False
to
Range(strRange).Comment.Visible = True

Following line will fail (as it is doing now) if comment is not visible...
Range(strRange).Comment.Shape.Select True
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top