comment with date

[RKS]

I can see the code for comment with date and time. its use with MICRO. can it
possible when I insert comment it will display with current date. without
using micro. please tell me how is possible. code which i have thru net is
below;

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:ss"
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

thanks for advance
RKS

 

[Dave Peterson]

Maybe...

Excel picks up the username from
Tools|Options|General Tab|under the User Name box.
(xl2003 menu system)

You could go into that dialog each day and change the username to today's date.

If you wanted, you could also use a macro that would change the name to the
current date each time you opened the workbook.

Option Explicit
Sub Autpen()
Application.UserName = Format(Date, "mmmm dd, yyyy")
End Sub

But that's using a macro, too.

 

[RKS]

Thanks its work fine. if we need username+date and time. i dont know about
programming so please change who is required and tell me.

Once again thanks and u people really magician.
RKS

 

[Dave Peterson]

Option Explicit
Sub Autpen()
Dim VbLfPos As Long
Dim myStr As String
myStr = Application.UserName
VbLfPos = InStr(1, myStr, vbLf, vbTextCompare)

'strip out old date
If VbLfPos > 0 Then
myStr = Left(myStr, VbLfPos - 1)
End If

myStr = myStr & vbLf & Format(Date, "mmmm dd, yyyy")

Application.UserName = myStr
End Sub