Excel VBA and Comment boxes
 

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

Excel VBA and Comment boxes
 

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
--
Always provide your feedback...


"DCPan" wrote:

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

Excel VBA and Comment boxes
 

That did the trick! You rock! (I rated the post too!)

"Sheeloo" wrote:

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
--
Always provide your feedback...


"DCPan" wrote:

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

Excel VBA and Comment boxes
 

Thanks for the feeback... it helps everyone.

See http://www.contextures.com/xlcomments03.html for excellent examples for
dealing with comments...

--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"DCPan" wrote:

That did the trick! You rock! (I rated the post too!)

"Sheeloo" wrote:

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
--
Always provide your feedback...


"DCPan" wrote:

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