How to resize a comment box, by embedding code into a function?

[JJ]

I have created a function that allows me to see a change everytime there is a
change on the worksheet. A comment box will appear and continue to append the
changes, unlike the "track changes function". I can not seem to figure out
why my comment box will not autosize......I have attached my code. Does
anyone know what I am doing wrong?

Function Worksheet_Change(ByVal Target1 As Excel.range)
With activecell.Offset(0, 0).range("A1")
On Error Resume Next
oldtext = .Comment.Text
If Err <> 0 Then .AddComment
newtext = oldtext & "Changed to " & .Text & _
" by " & Application.UserName & " at " & Now & vbLf
.Comment.Text newtext
.Comment.Visible = True
.Comment.Shape.Select True
.autosize = True
.Comment.Visible = True
End With
End Function

 

[Alan]

I have created a function that allows me to see a change everytime
there is a change on the worksheet. A comment box will appear and
continue to append the changes, unlike the "track changes function".
I can not seem to figure out why my comment box will not
autosize......I have attached my code. Does anyone know what I am
doing wrong?

Function Worksheet_Change(ByVal Target1 As Excel.range)
With activecell.Offset(0, 0).range("A1")
On Error Resume Next
oldtext = .Comment.Text
If Err <> 0 Then .AddComment
newtext = oldtext & "Changed to " & .Text & _
" by " & Application.UserName & " at " & Now & vbLf
.Comment.Text newtext
.Comment.Visible = True
.Comment.Shape.Select True
.autosize = True
.Comment.Visible = True
End With
End Function

Hi,

It appears from reading your code that the autosize method is being
applied to the cell (range) and not the comment itself.

You need to reference the shape property of the comment object to
return a shape object and then re-size that something like:

ActiveSheet.Range("D13").Comment.Shape.ScaleWidth 1.3, msoFalse,
msoScaleFromTopLeft

The 1.3 was chosen randomly.

BTW, what does the .range("A1") on the end of the first line do?


HTH,

Alan.

 

[JJ]

Alan,

The range("A1") is so that when I have the function cover the whole sheet
(ex. =worksheet_change(1:65536), it will only add a comment to the active
cell. I had previously tried your proposed solution, but when a new comment
is to be appended, the box multiplies by the number, in your case 1.3,
instead of resizing. I know how to have a specific width, but I would like
the width of the box to vary depending on the comment length. Any other
suggestions?

 

[Debra Dalgleish]

The following code will add a comment to the cell that was changed:

'=========================
Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim oldtext As String
Dim newtext As String

With Target
On Error Resume Next
oldtext = .Comment.Text
If Err <> 0 Then
.AddComment
End If
On Error GoTo 0
newtext = oldtext & " Changed to " & .Text & _
" by " & Application.UserName & " at " & Now & vbLf
With .Comment
.Text newtext
.Visible = True
.Shape.TextFrame.AutoSize = True
.Visible = True
End With
End With
End Sub
'============================

 

[JJ]

Thanks! I had the hardest time with that!

The following code will add a comment to the cell that was changed:

'=========================
Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim oldtext As String
Dim newtext As String

With Target
On Error Resume Next
oldtext = .Comment.Text
If Err <> 0 Then
.AddComment
End If
On Error GoTo 0
newtext = oldtext & " Changed to " & .Text & _
" by " & Application.UserName & " at " & Now & vbLf
With .Comment
.Text newtext
.Visible = True
.Shape.TextFrame.AutoSize = True
.Visible = True
End With
End With
End Sub
'============================

 

[Alan]

The following code will add a comment to the cell that was changed:

'=========================
Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim oldtext As String
Dim newtext As String

With Target
On Error Resume Next
oldtext = .Comment.Text
If Err <> 0 Then
.AddComment
End If
On Error GoTo 0
newtext = oldtext & " Changed to " & .Text & _
" by " & Application.UserName & " at " & Now & vbLf
With .Comment
.Text newtext
.Visible = True
.Shape.TextFrame.AutoSize = True
.Visible = True
End With
End With
End Sub
'============================

Hi Debra,

Out of interest, is there any way to hide the cell comment marker?
Obviously we can hide the comment itself, but what about the red
triangle marker?

Also, can we avoid the comment 'popping' up when the cell is selected
with a 'hidden' comment?

Both of those are user annoyance issues rather than anything more
fundamental.

Thanks,

Alan.

 

[Debra Dalgleish]

You can hide all the comment indicators:

Manually -- Choose Tools>Options, and on the View tab, for Comments,
choose None

Or, programmatically, e.g.:

Application.DisplayCommentIndicator = xlNoIndicator

If the markers are hidden the comments won't pop up

 

[Alan]

The following code will add a comment to the cell that was changed:

'=========================
Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim oldtext As String
Dim newtext As String

With Target
On Error Resume Next
oldtext = .Comment.Text
If Err <> 0 Then
.AddComment
End If
On Error GoTo 0
newtext = oldtext & " Changed to " & .Text & _
" by " & Application.UserName & " at " & Now & vbLf
With .Comment
.Text newtext
.Visible = True
.Shape.TextFrame.AutoSize = True
.Visible = True
End With
End With
End Sub
'============================

Another comment / question:

Wouldn't it be more useful to use the FORMULA property of the range
(cell), rather than the TEXT property on this line:

newtext = oldtext & " Changed to " & .Formula & _


Is there any downside to using the FORMULA property that I am not
foreseeing?

Alan.

 

[Alan]

You can hide all the comment indicators:

Manually -- Choose Tools>Options, and on the View tab, for Comments,
choose None

Or, programmatically, e.g.:

Application.DisplayCommentIndicator = xlNoIndicator

If the markers are hidden the comments won't pop up

Thanks Debra,

You're a star!

Alan.

 

[Debra Dalgleish]

Using .Formula instead of .Text shouldn't create any problems.
What happened when you tried it? <g>

 

[Alan]

Using .Formula instead of .Text shouldn't create any problems.
What happened when you tried it? <g>

Somewhat limited testing, but it seems to be fine (XL2000), and
definately better for what I want to do than TEXT.

Another follow up question:

I would like to use the text in a comment attached to a cell in my
code.

The TEXT method of the Comment object allows you to SET the text of
the comment, but how do I read it (programmatically)?

Am I being dim here?

I tried this:

Prompt = ActiveCell.Comment.Text

Response = MsgBox(Prompt, vbOKOnly, "This is the cell comment")


But it doesn't seem to work, presumably because TEXT is not a property
of the comment object?

So how do we get the text out into a variable?

Apologies if I am being slow / a pain!

Alan.

 

[Debra Dalgleish]

That should work. What result were you getting?

Somewhat limited testing, but it seems to be fine (XL2000), and
definately better for what I want to do than TEXT.

Another follow up question:

I would like to use the text in a comment attached to a cell in my
code.

The TEXT method of the Comment object allows you to SET the text of
the comment, but how do I read it (programmatically)?

Am I being dim here?

I tried this:

Prompt = ActiveCell.Comment.Text

Response = MsgBox(Prompt, vbOKOnly, "This is the cell comment")


But it doesn't seem to work, presumably because TEXT is not a property
of the comment object?

So how do we get the text out into a variable?

Apologies if I am being slow / a pain!

Alan.

 

[Alan]

That should work. What result were you getting?

Apologies - it appears that the cell did not contain a comment.

Hence I was getting an 'Object variable or with block variable not
set' error.

Sorry.


However, when I tried to debug by using this:

Set myComment = Activecell.Comment

and then stepped into the code, and looked in the locals window, there
is no 'text' property shown for the comment (even when it does
actually exist and has text in it!)

Am I missing the point of the locals window?

Alan.


PS: I appreciate you have better things to do than 'teach' me VBA
coding - tell me to go read a book or play with the buses if you
like - your help today has been very much appreciated.

 

[Debra Dalgleish]

Text is a Method, so it won't appear in the list of properties.