[mso] Auto add comments to Excel cells

I have found some code that does almost what I need to do, i.e. add a
comment to a cell containing the date & time. However I want this to apply
to a number of separate cells at the same time and be added automatically
when a spreadsheet is saved and closed. I t would also be good if every time
it deletes the previous entry so there is only ever one line in the comment
box.
Can anyone suggest some changes to this

 

Sub CommentDateTimeAdd()

'adds comment with date and time, 

'  positions cursor at end of comment text

 

    Dim strDate As String

    Dim cmt As Comment

    

    strDate = "dd-mmm-yy hh:mm"

    Set cmt = ActiveCell.Comment

  

    If cmt Is Nothing Then

      Set cmt = ActiveCell.AddComment

      cmt.text text:=Format(Now, strDate) & Chr(10)

    Else

      cmt.text text:=cmt.text & Chr(10) _

        & Format(Now, strDate) & Chr(10)

    End If

    

    With cmt.Shape.TextFrame

      .Characters.Font.Bold = False

    End With

    

    SendKeys "%ie~"

 

End Sub

 

 

I have tried altering the line     strDate = "dd-mmm-yy hh:mm"  to   strDate
= "Last Updated dd-mmm-yy hh:mm" and for some reason the result is:-   La14t
Up11ate11 11-Apr-07 08:18  

 

If I put the Last Updated in inside single quotes the s of Last becomes 15.
There must be some logic somewhere, but I cannot see it!

 

TIA

 

Geoff Culbertson

Petersfield, Hampshire, UK



*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
 This group will not allow for posting of emails, but will allow you to join 
and share problem files, templates, etc.:  
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

Other related posts: