PDA

View Full Version : comment time



lior03
02-23-2006, 12:18 AM
hello
this macro
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

puts date and time in a comment .while this macro
Sub commentalony()
Dim cm As Comment
Dim c As Range
For Each c In selection.Cells
c.clearcomments
If Not IsEmpty(c) And c.HasFormula Then
With c.AddComment
.Text Text:="Formula:" & c.Formula
.Shape.TextFrame.AutoSize = True
End With
End If
Next c
End Sub
adds a formula of activecell to a comment.can this macro be merged to one that will present formula in active cell with the time and date comment was created?
thanks

Bob Phillips
02-23-2006, 01:37 AM
Sub commentalony()
Dim cm As Comment
Dim c As Range
Dim strDate As String
strDate = "dd-mmm-yy hh:mm:ss"
For Each c In Selection.Cells
c.ClearComments
If Not IsEmpty(c) And c.HasFormula Then
With c.AddComment
.Text Text:=Format(Now, strDate) & Chr(10) & _
"Formula:" & c.Formula
.Shape.TextFrame.AutoSize = True
End With
End If
Next c
End Sub

lilstevie
11-17-2006, 09:01 AM
Is there any way to display the results of a formula in the comment box?

lucas
11-17-2006, 09:06 AM
Option Explicit
Sub CommentThem()
Dim cell As Range
On Error Resume Next
Selection.ClearComments
On Error GoTo 0
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If cell.Formula <> "" Then
cell.AddComment
cell.Comment.Visible = False
On Error Resume Next 'fails on invalid formula
cell.Comment.Text Text:=cell.Address(0, 0) & _
" value: " & cell.Value & Chr(10) & _
" format: " & cell.NumberFormat & Chr(10) & _
" Formula: " & cell.Formula
On Error GoTo 0
End If
Next cell
End Sub

lilstevie
11-17-2006, 10:23 AM
Once again.. Thanks Steve!

lucas
11-17-2006, 10:45 AM
Glad to help Buckeye...:whistle: