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
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