Text Method "Overwrite" problem

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?

0
skerdoba (1)
7/10/2003 6:01:57 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
831 Views

Similar Articles

[PageSpeed] 43

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


"skerdoba" <skerdoba@speakeasy.net> wrote in message news:028101c3470d$5a4cdec0$a301280a@phx.gbl...
> 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?
>


-1
deitbe (120)
7/11/2003 3:33:34 PM
Reply:

Similar Artilces:

Save a Copy/Overwrite changes dialog box
All of a sudden, this dialog box pops up every time I want to save something in Excel 2002. The box says, "The file '<file name>' may have been changed by another user since you last saved it. In that case, what do you want to do? __ Save a Copy __ Overwrite changes even though I'm the only user that's changed anything in the past 3 years. The shared workbook command has not been changed at all, so that can't be the problem. The settings are the same as the ones I have at home for personal spreadsheets that don't have this dialog box. Help! Than...

Save a copy or Overwrite changes
I'm getting the following message when working with an excel file. It was not read-only when I opened it. I am on a network and using Excel 2002. It is not a shared workbook. The file 'Resources.xls' may have been changed by another user since you last saved it. In that case, what do you want to do? You need to select Save a copy or Overwrite changes and then you click OK or Cancel. What can I check to make sure that I am set up properly and stop getting this message? Thank you. I know you said it is not a shared workbook/file, but it sounds exactly like what Excel ...

Overwrite changes dialogue box
After updating Excel 2003, upon closing after making changes and dialagoue box appears with the option of saving a copy or overwrite changes? Anyone know how to turn this annoying feature off. ...

Index Services overwrites last logon time
Hello: In Exchange 2003/SP1 I have used to open Exchange System Manager – Mailbox store – Mailboxes and check the last logon time column , so I can get list of users who did not use their accounts in the last x days…. The problem is that after I have enabled the Indexing feature in Exchange to run everyday at 3:00 AM , it overwrites the last logon time of all mailboxes , so I can see that the last logon time for all my users is 3:00Am Does any one know how to workaround this problem? Also it seems that the Index service can not login to the mailboxes of users who have never logged on...

Problems editing text in a cell...overwrites some characters
I am trying to change some of the text currently in a Cell of Excel and in the formula bar it seems to overwrite half of the previous character. I cannot enter a single word without a character being deleted or overwritten. I've tried copying the date and paste special into a new sheet, same problem. It has so much info, it'll take me 4 days to re enter, can anyone tell me what is going on with this? ...

Application.thisworkbook.saveas .. Is there a way to bypass "Overwrite" popup?
I do a "application.thisworkbook.saveas" everytime to a different location, and sometime even on the same file. When I hit the same file, is there a way to make it so the "Overwrite" popup does not show up? Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements You might try putting Application.DisplayAlerts = False just before your ..SaveAs command, ...

Is HQ supposed to overwrite Bin location?
I have HQ and am running two stores. Each store has different bin locations for the products. Is there anyway to prevent HQ from overwriting this field? I would like for the bin locations to be local, not global. Currently i have found that running a 250 overwrites the BIN. I didn't think it would but.... Help? jessica from Greencoast wrote: > Help? .... inside from rms/hq I think is not possible.. only to create a snapshot via sql ... cod Some folks might call that a design flaw... If you're a merchant, it's a software design flaw. If you're a software engineer,...

overwrite a serialized object
I'm serializing an object into a file in XML. After the object is serialized, some other relevant data is written into the file. The rest of the data winds up being very large most of the time. Is there a way to re-serialize the object over itself? Here's a quick example: Say I have this: <?xml version="1.0" encoding="utf-8"?> <fileroot> <SerializedObject> <intParam>6</intParam> <doubleParam>4.68</doubleParam> </SerializedObject> <otherData>blarg</otherData> </fileroot> Is it possible ...

Inventory On Hand Quantity Overwrite
Dear All, I have a RMS with 20,000+ inventory. (no HQ) Is there anyway I can overwrite the existing "on hand" quantity of all items with a CSV file? I have used QSCImp.exe but I don't think it allows overwrite of exsiting items and only allows to create new items from the CSV files. Thank you for your help in adavnce. Ken Use the Physical Inventory create a blank batch and use the import file option and then commit the batch. This way you can update quantities and have a record at the same time. -- Disclaimer: Always test any queries that modify data on a test dat...

How can I overwrite the primary partition?
Hi, I'd like to use file shredder to overwrite the primary partition C:, but it failed for the partition is in use. What can I do? Can I install another system on another parition such as E or F, and then boot from E or F partition, so that I can overwrite the ex-primary partition using file shredder? Please help me. thanks. You can't unless you boot from CD or disk. Depending on your HD, you can download a tool just for that from here: http://support.wdc.com/product/download.asp?groupid=502&sid=30&lang=en The file is: Diag504fCD.iso, burn it on to a CD ...

Can I auto change text to RED if a user overwrites a formula?
I have a template that many users access. I have some cells that contain formulae that they sometimes want to overwrite. Can I set the text in that cell to show up as red if they overwrite the formula with hard input? Hi, I suggest: Define the name HasFormula =GET.CELL(48,INDIRECT("RC",FALSE)) (Go to Insert/Name/Define, enter as name HasFormula and in field "Refers to:" =GET.CELL(48,INDIRECT("RC",FALSE)) [don't forget the leading =]) Then select all cells of your relevant sheet (click at upper left corner). Go to Format/Conditional Formatting.., sele...

SaveCopyAs and Overwrite Alert
I have written a vba Function, for Excel 2007 under XP SP2, that offers a user the opportunity to save a safety copy of his/her file before proceeding. Copy below The function operates exactly as I would expect except that it does not raise an Overwrite Alert where appropriate - it just proceed to overwrite regardless. Can anbody see/suggest what I have missed or am doing wrong? Suggestions welcome TIA Chris ======Copy of macro ======== Option Explicit Sub TestSafetySave() Dim bProceed bProceed = SafetySave MsgBox bProceed End Sub Function SafetySave() As Boolean ' Offe...

Skipping cells when pasting so not to overwrite formula
I have a template where I have cells with formula. I have created outputs from Access which have these columns as blanks. When I paste the data into the template sheet, requesting Paste Special, Values, and Skip Blanks, I thought it would ignore the blank columns and leave my formula intact, but it writes the blank columns in Is there anyway of pasting the data in without overwriting the formula As an example my sheet will look like this text field / text field / text field / currency field / blank field / text field The template will have a formula to calcuated in the blank field Appreci...

Please Help! How to enable cell overwrite AFTER the query is created
How do I check and/or change to overwrite the target cell? I've already created the query and I can't find where to edit the overwrite option (Excel 2002) Select a cell in the external data range. Choose Data>Import External Data> Data Range Properties Change the setting for 'If the number of rows in the data range changes' Click OK Kipp wrote: > How do I check and/or change to overwrite the target cell? I've already created the query and I can't find where to edit the overwrite option (Excel 2002) -- Debra Dalgleish Excel FAQ, Tips & Book List http://...

publish configuration overwrites isv.config from where..?
I'm aware that isv.config is changed when customisations are imported with the Deployment Manager. But Publish Custumisation also seems to overwrite the file from somewhere (even if only contacts is selected to publish etc.)...? Our problem is a client is editing forms and they lose any changes to the isv.config every time they publish.. Where is it getting overwritten from? (We edit _resources\isv.config) TIA Simon I had this same issue. You need to go to the C:\Program Files\Microsoft CRM\Tools\config folder and rename or delete the isv.config in there. I am unsure why this is ...

How do you turn off "Save a copy/Overwrite changes" dialog box
Hello Guys, I have created a (password to open) workbook using Excel 2003 SP1 on a shared network drive which when I try to save by using Ctrl S or clicking the Save icon gives me an irritating dialog box which asks: "The file 'filename.xls' may have been changed by another user since you last saved it. In that case what do you want to do?" -Save a copy -Overwrite changes Please can someone tell me how to deactivate this annoying dialog box as I have been unsuccesful so far in my efforts :confused: Many thanks in advance Nick -- Nicko -----------------------------...

combo box selection overwrite issue
We have a combo box on a form. When you start typing i auto populates with the closest record and keeps it highlighted so if you keep typing it keeps pulling the next closest. It works fine with all records except one. if you type in Ho it comes up with the closeest records to that but instead of keeping it highlighted so you can type over it changes to insert typing and anything you type just goes after the Ho but all the other text it auto populated is still there. So if you want to select any other record that start with Ho you have to scroll through the list. I haev looked at...

How can I overwrite the last line data in text or csv file from VB
Hello, Is there a method that I can overwrite the last line of csv/text file WITHOUT reading the whole file from the first line from Excel VBA ? I am trying to read the last line of data and use if function to determine if it would be overwritten. Besides reading the whole file in and rewriting all of them out, is there another efficient way to handle it? You can read the whole file but only remember the last line so you're not using up lots of memory. This only remembers the last record read... Do While Not EOF(iFileNum) Input #iFileNum, strEachRecord strLastLin...

Archive overwrites, not appends, to earlier archive data
There are 2 ways to archive, as far as the documented tools describe: auto-archive manual File\Archive In each case, my archive OVERWRITES the content rather than appending to it, as I would expect especially of the auto-archive. Are there nefarious registry settings that are causing the archive to behave badly, bluntly blowing away my e-paper trails? (Saaaay, maybe this isn't such a bad thing...) I do need to find this gremlin and dispatch him quickly. thanks ...

Copying of cell overwrites Lock Format
Hello Everybody I have the following issue: I have a sheet, which is protected with "select unlocked cells" only. That makes sure, that the user cannot change the format of one of the unlocked cells. Let's say A1 is unlocked, the rest is locked. Formatted as Arial 12, no Background nothing. Now try the following. Go to another sheet, enter something in a cell and format it completely different, then copy and paste it onto your protected sheet to A1. Excel accepts the change of format. Is there a way around that? I could do it in VBA without big problems, but my Boss doesn'...

Excel 2003 prompts to save or overwrite
I have a few excel 2003 users that have xls files on a server, but they are the only ones using. Often, when they save, it tells them someone else may have changed the file, rename or overwrite. They are a bit concerned, It appears to be a mac services thing, I found the following MS article in support. http://support.microsoft.com/kb/324491 But it doesnt mention a patch for excel 2003. Should the users just get used to hiting overwrite? Regrads -- Carl For Excel 2003 no patch has been issued AFAIK Follow the instructions for editing the Registry or continue hitting overwrite. ...

want to overwrite Excel workbook instead of appending new data
Hello, I'm doing data import overnight. Now the new data is appended to the old data. But I want to overwrite Excel workbook instead of appending new datato the old data. Any idea? Thanks! In article <2E756F9B-ADD7-40DE-954E-A29BE379B321@microsoft.com>, Excel@discussions.microsoft.com says... > Hello, > > I'm doing data import overnight. Now the new data is appended to the old > data. But I want to overwrite Excel workbook instead of appending new datato > the old data. Any idea? Thanks! > Delete the existing data before importing? Depending on how you...

Automatically accepting overwrite
ok so i have this code: ActiveWorkbook.SaveAs Filename:="FILENAME.xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False but when it runs it asks me if i want to overwrite the existing file.which i do How can i alter this code to automatically overwrite? Thanks Robert --- Message posted from http://www.ExcelForum.com/ Robert This line of code may well work: Application.DisplayAlerts=False Regards Philip "robert_woodie >" <<robert_woodie.1124zm@excelforum-nospam.com> wro...

Ripping
I am trying to rip a couple of classical music CDs I got for Xmas. Although WMP retrieves the correct CD details, each track has the same name, e.g. "Rigoletto, the opera" Consequently during the ripping process each track as it is ripped overwrites the previously ripped track. Is there a solution to this? thanks in advance, DAvy Davy <me@removeallthistextchobham.org.uk> wrote in news:Xns9CEE671CAAD2Fmeremoveallthistextc@216.196.109.145: > I am trying to rip a couple of classical music CDs I got for Xmas. > Although WMP retrieves the correct CD deta...

retrieving of overwrite excel file?
pls! help me about my excel file it accedentally overwrite my updated file to the old file? pls! anyone there if you have an idea how do i retrieving my file give me some tips or advise how to fix my excel file. tnx.. Unless you have created a backup at some time, you are lost I am afraid. -- HTH Bob Phillips "PLS! need help." <PLS! need help.@discussions.microsoft.com> wrote in message news:9AA56CFF-8808-4F0F-97B5-5BEE92FDC2B5@microsoft.com... > pls! help me about my excel file it accedentally overwrite my updated file to > the old file? pls! anyone there if you ...