#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jock W
 
Posts: n/a
Default Comments box

Is it possible to have Excel to automatically add a 'comment' with the date
already in the comment to a cell when data is put into that cell?

tia
--
Jock Waddington
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Comments box

This crude Workbook code seems to do it........
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Upon entering a value in a cell, this macro will attach a Comment Box
'to the cell and insert the current date. It will append existing comments.
If ActiveCell.Value < "" Then
ActiveCell.Select
If Selection.Comment Is Nothing Then
Selection.AddComment
Selection.Comment.Visible = True
Selection.Comment.Text Text:=Date & Chr(10)
Selection.Comment.Visible = False
Else
Selection.Comment.Visible = True
Selection.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Date &
Chr(10)
Selection.Comment.Visible = False
End If
End If
End Sub



"Jock W" wrote:

Is it possible to have Excel to automatically add a 'comment' with the date
already in the comment to a cell when data is put into that cell?

tia
--
Jock Waddington

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Comments box

If the feature becomes aggravating, this version will allow you to toggle it
On and Off by typing "CommentOn" in cell A2.....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Upon entering a value in a cell, this macro will attach a Comment Box
'to the cell and insert the current date. It will append existing comments.
If Range("A2").Value = "CommentOn" Then
If ActiveCell.Value < "" Then
ActiveCell.Select
If Selection.Comment Is Nothing Then
Selection.AddComment
Selection.Comment.Visible = True
Selection.Comment.Text Text:=Date & Chr(10)
Selection.Comment.Visible = False
Else
Selection.Comment.Visible = True
Selection.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Date &
Chr(10)
Selection.Comment.Visible = False
End If
End If
Else
End If

End Sub

Vaya con Dios,
Chuck, CABGx3




"CLR" wrote:

This crude Workbook code seems to do it........
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Upon entering a value in a cell, this macro will attach a Comment Box
'to the cell and insert the current date. It will append existing comments.
If ActiveCell.Value < "" Then
ActiveCell.Select
If Selection.Comment Is Nothing Then
Selection.AddComment
Selection.Comment.Visible = True
Selection.Comment.Text Text:=Date & Chr(10)
Selection.Comment.Visible = False
Else
Selection.Comment.Visible = True
Selection.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Date &
Chr(10)
Selection.Comment.Visible = False
End If
End If
End Sub



"Jock W" wrote:

Is it possible to have Excel to automatically add a 'comment' with the date
already in the comment to a cell when data is put into that cell?

tia
--
Jock Waddington

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jock W
 
Posts: n/a
Default Comments box

Tried and tried but I can't get this to work.
No comment forthcoming.
Is this bit correct "ByVal Sh As Object, ByVal Target As Range"?

Cheers

--
Jock Waddington


"CLR" wrote:

If the feature becomes aggravating, this version will allow you to toggle it
On and Off by typing "CommentOn" in cell A2.....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Upon entering a value in a cell, this macro will attach a Comment Box
'to the cell and insert the current date. It will append existing comments.
If Range("A2").Value = "CommentOn" Then
If ActiveCell.Value < "" Then
ActiveCell.Select
If Selection.Comment Is Nothing Then
Selection.AddComment
Selection.Comment.Visible = True
Selection.Comment.Text Text:=Date & Chr(10)
Selection.Comment.Visible = False
Else
Selection.Comment.Visible = True
Selection.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Date &
Chr(10)
Selection.Comment.Visible = False
End If
End If
Else
End If

End Sub

Vaya con Dios,
Chuck, CABGx3




"CLR" wrote:

This crude Workbook code seems to do it........
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Upon entering a value in a cell, this macro will attach a Comment Box
'to the cell and insert the current date. It will append existing comments.
If ActiveCell.Value < "" Then
ActiveCell.Select
If Selection.Comment Is Nothing Then
Selection.AddComment
Selection.Comment.Visible = True
Selection.Comment.Text Text:=Date & Chr(10)
Selection.Comment.Visible = False
Else
Selection.Comment.Visible = True
Selection.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Date &
Chr(10)
Selection.Comment.Visible = False
End If
End If
End Sub



"Jock W" wrote:

Is it possible to have Excel to automatically add a 'comment' with the date
already in the comment to a cell when data is put into that cell?

tia
--
Jock Waddington

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Comments box

Did you watch out for any word-wrap coming through the post....sometimes that
messes macros up. I copied the code straight out of my VBE....it was working
there. Check each line for something funny looking. Worse case is, give me
your email and I'll send a file with it working in it.

hth
Vaya con Dios,
Chuck, CABGx3









"Jock W" wrote:

Tried and tried but I can't get this to work.
No comment forthcoming.
Is this bit correct "ByVal Sh As Object, ByVal Target As Range"?

Cheers

--
Jock Waddington


"CLR" wrote:

If the feature becomes aggravating, this version will allow you to toggle it
On and Off by typing "CommentOn" in cell A2.....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Upon entering a value in a cell, this macro will attach a Comment Box
'to the cell and insert the current date. It will append existing comments.
If Range("A2").Value = "CommentOn" Then
If ActiveCell.Value < "" Then
ActiveCell.Select
If Selection.Comment Is Nothing Then
Selection.AddComment
Selection.Comment.Visible = True
Selection.Comment.Text Text:=Date & Chr(10)
Selection.Comment.Visible = False
Else
Selection.Comment.Visible = True
Selection.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Date &
Chr(10)
Selection.Comment.Visible = False
End If
End If
Else
End If

End Sub

Vaya con Dios,
Chuck, CABGx3




"CLR" wrote:

This crude Workbook code seems to do it........
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Upon entering a value in a cell, this macro will attach a Comment Box
'to the cell and insert the current date. It will append existing comments.
If ActiveCell.Value < "" Then
ActiveCell.Select
If Selection.Comment Is Nothing Then
Selection.AddComment
Selection.Comment.Visible = True
Selection.Comment.Text Text:=Date & Chr(10)
Selection.Comment.Visible = False
Else
Selection.Comment.Visible = True
Selection.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Date &
Chr(10)
Selection.Comment.Visible = False
End If
End If
End Sub



"Jock W" wrote:

Is it possible to have Excel to automatically add a 'comment' with the date
already in the comment to a cell when data is put into that cell?

tia
--
Jock Waddington



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jock W
 
Posts: n/a
Default Comments box

It all looks ok to me, but it still no joy.
I have tried adding the code to the whole work book and also the worksheet,
but neither give a resukt.
email addy -

tx
--
Jock Waddington


"CLR" wrote:

Did you watch out for any word-wrap coming through the post....sometimes that
messes macros up. I copied the code straight out of my VBE....it was working
there. Check each line for something funny looking. Worse case is, give me
your email and I'll send a file with it working in it.

hth
Vaya con Dios,
Chuck, CABGx3









"Jock W" wrote:

Tried and tried but I can't get this to work.
No comment forthcoming.
Is this bit correct "ByVal Sh As Object, ByVal Target As Range"?

Cheers

--
Jock Waddington


"CLR" wrote:

If the feature becomes aggravating, this version will allow you to toggle it
On and Off by typing "CommentOn" in cell A2.....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Upon entering a value in a cell, this macro will attach a Comment Box
'to the cell and insert the current date. It will append existing comments.
If Range("A2").Value = "CommentOn" Then
If ActiveCell.Value < "" Then
ActiveCell.Select
If Selection.Comment Is Nothing Then
Selection.AddComment
Selection.Comment.Visible = True
Selection.Comment.Text Text:=Date & Chr(10)
Selection.Comment.Visible = False
Else
Selection.Comment.Visible = True
Selection.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Date &
Chr(10)
Selection.Comment.Visible = False
End If
End If
Else
End If

End Sub

Vaya con Dios,
Chuck, CABGx3




"CLR" wrote:

This crude Workbook code seems to do it........
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Upon entering a value in a cell, this macro will attach a Comment Box
'to the cell and insert the current date. It will append existing comments.
If ActiveCell.Value < "" Then
ActiveCell.Select
If Selection.Comment Is Nothing Then
Selection.AddComment
Selection.Comment.Visible = True
Selection.Comment.Text Text:=Date & Chr(10)
Selection.Comment.Visible = False
Else
Selection.Comment.Visible = True
Selection.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Date &
Chr(10)
Selection.Comment.Visible = False
End If
End If
End Sub



"Jock W" wrote:

Is it possible to have Excel to automatically add a 'comment' with the date
already in the comment to a cell when data is put into that cell?

tia
--
Jock Waddington

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comments in Excel JP Excel Worksheet Functions 2 April 4th 06 08:22 PM
Display Comments on Each Page Stanley Excel Discussion (Misc queries) 0 February 23rd 06 07:05 PM
Lookup Text in Comments malik641 Excel Worksheet Functions 2 July 5th 05 12:36 PM
Include comments on another spreadsheet jh3016 Excel Discussion (Misc queries) 1 July 3rd 05 12:24 PM
Can I reference comments by Cell Name? flo1730 Excel Discussion (Misc queries) 5 June 15th 05 03:34 PM


All times are GMT +1. The time now is 08:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"