Text Method "Overwrite" problem

[skerdoba]

I'm having a problem with Overwrite=True when using the
Text Method to insert text into a comment in my Excel
worksheet. I can insert text into my comment at the
specified character position, no problem. However it
doesn't seem to matter if I set Overwrite to true or
false, the text is always inserted rather than overwriting
existing text.

Here is the description of the method, followed by my code:

Text Method
Sets comment text.
Syntax
expression.Text(Text, Start, Overwrite)
expression Required. An expression that returns a
Comment object.
Text Optional Variant. The text to be added.
Start Optional Variant. The character number where the
added text will be placed. If this argument is omitted,
any existing text in the comment is deleted.
Overwrite Optional Variant. True to overwrite the
existing text. The default value is False (text is
inserted).

Here's my code:
Worksheets("Applic").Cells(2, 3).Comment.Text strSysTime
& " seconds.", 65, Overwrite = True

Seems pretty straightforward. Am I doing something wrong?

 

[Bernie Deitrick]

skerdoba,

The insert parameter does just that: inserts at the specific location in the test string. But the help text is wrong - say it isn't
so! - since the default value of overwrite is TRUE and the overwrite parameter can only be used to set it to FALSE. Also, overwrite
means overwriting the entire comment, not just the comment starting from the insertion point.

So, if your comment on Cell A1 is:

Comment Text

and you use

Range("A1").Comment.Text Text:=" Bernie ", Start:=2, Overwrite:=False

you'll end up with:

C Bernie omment Text

If you actually want to change

Comment Text

to

C Bernie ext

i.e., have your comment string end up the same length overall, then you would need to use some string manipulations:

Dim myStr As String
myStr = " Bernie "
With Range("A1").Comment
.Text Text:=Left(.Text, 1) & myStr & Right(.Text, Len(.Text) - Len(myStr))
End With

HTH,
Bernie
Excel MVP