ExcelBanter - Note or Comment

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Note or Comment (https://www.excelbanter.com/excel-discussion-misc-queries/124533-note-comment.html)

I have a spreadsheet that is on a shared drive and several people update this
sheet daily, I had found this VB that was posted by someone back in August of
this year and it works almost perfect except for one small item, HOW can I
track who is updating the cells, when the person puts the sheet on the shared
drive or emailed to one of the others and a cell is updated the name that
appears in the comment is the one who put the sheet on the drive and not the
person updating it. Is there a way to have the one who is updating the cell
to have their name add to the others who had update the cell earlier.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 0
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment < "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
ActiveWorkbook.BuiltinDocumentProperties("Author") & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
ActiveCell.Comment.Visible = False
'comment perhaps should be resized
End Sub

Tank

Try username instead.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 0
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment < "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
ActiveWorkbook.Application.UserName & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
ActiveCell.Comment.Visible = False
'comment perhaps should be resized
End Sub

Gord Dibben MS Excel MVP

On Wed, 3 Jan 2007 11:44:00 -0800, tankerman
wrote:

I have a spreadsheet that is on a shared drive and several people update this
sheet daily, I had found this VB that was posted by someone back in August of
this year and it works almost perfect except for one small item, HOW can I
track who is updating the cells, when the person puts the sheet on the shared
drive or emailed to one of the others and a cell is updated the name that
appears in the comment is the one who put the sheet on the drive and not the
person updating it. Is there a way to have the one who is updating the cell
to have their name add to the others who had update the cell earlier.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 0
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment < "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
ActiveWorkbook.BuiltinDocumentProperties("Author") & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
ActiveCell.Comment.Visible = False
'comment perhaps should be resized
End Sub

Gord, you are the man, it works great but I have a couple questions. First
how do I keep the formatting color, some of the cells have a back ground
color but when the cell has anything entered into it the cell changes to a
white background.

Second I only need some of the cells to have the comments in them not all of
the sheet, what do I need to change to get only the cells I need to to have
the comments in them.

THANK YOU for the help this board has been a great help in almost all of my
endeavors to use EXCEL or ACCESS

"Gord Dibben" wrote:

Tank

Try username instead.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 0
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment < "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
ActiveWorkbook.Application.UserName & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
ActiveCell.Comment.Visible = False
'comment perhaps should be resized
End Sub

Gord Dibben MS Excel MVP

On Wed, 3 Jan 2007 11:44:00 -0800, tankerman
wrote:

I have a spreadsheet that is on a shared drive and several people update this
sheet daily, I had found this VB that was posted by someone back in August of
this year and it works almost perfect except for one small item, HOW can I
track who is updating the cells, when the person puts the sheet on the shared
drive or emailed to one of the others and a cell is updated the name that
appears in the comment is the one who put the sheet on the drive and not the
person updating it. Is there a way to have the one who is updating the cell
to have their name add to the others who had update the cell earlier.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 0
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment < "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
ActiveWorkbook.BuiltinDocumentProperties("Author") & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
ActiveCell.Comment.Visible = False
'comment perhaps should be resized
End Sub

I have a couple of questions maybe you can address for me
First -- I am having trouble figuring our how to have the comment in just
certain cells, I have tried several differnt ways but I am stuck and unable
to exclude cells.

Second -- I tried to protect my sheet but when I do the coding wont work on
inserting the comments, I need an accurate list of the ones editing the
sheets and only on specific cells.

If you could help me with this I would certainly appreciate the help.
Tankerman

"Gord Dibben" wrote:

Tank

Try username instead.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 0
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment < "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
ActiveWorkbook.Application.UserName & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
ActiveCell.Comment.Visible = False
'comment perhaps should be resized
End Sub

Gord Dibben MS Excel MVP

On Wed, 3 Jan 2007 11:44:00 -0800, tankerman
wrote:

I have a spreadsheet that is on a shared drive and several people update this
sheet daily, I had found this VB that was posted by someone back in August of
this year and it works almost perfect except for one small item, HOW can I
track who is updating the cells, when the person puts the sheet on the shared
drive or emailed to one of the others and a cell is updated the name that
appears in the comment is the one who put the sheet on the drive and not the
person updating it. Is there a way to have the one who is updating the cell
to have their name add to the others who had update the cell earlier.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 0
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment < "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
ActiveWorkbook.BuiltinDocumentProperties("Author") & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
ActiveCell.Comment.Visible = False
'comment perhaps should be resized
End Sub

Tank

Assumes you have the cells in Const My_Range unlocked and sheet protected with
password of "justme"(no quotes)

Private Sub Worksheet_Change(ByVal Target As Range)
Const My_Range As String = "A1:A10"
' or "A1, A3, A6, A8, A10"
If Not Intersect(Target, Me.Range(My_Range)) Is Nothing Then
ActiveSheet.Unprotect Password:="justme"
Target.Interior.ColorIndex = 0
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment < "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
ActiveWorkbook.Application.UserName & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
ActiveCell.Comment.Visible = False
ActiveSheet.Protect Password:="justme"
End If
'comment perhaps should be resized
End Sub

Gord

On Sun, 7 Jan 2007 21:22:00 -0800, tankerman
wrote:

I have a couple of questions maybe you can address for me
First -- I am having trouble figuring our how to have the comment in just
certain cells, I have tried several differnt ways but I am stuck and unable
to exclude cells.

Second -- I tried to protect my sheet but when I do the coding wont work on
inserting the comments, I need an accurate list of the ones editing the
sheets and only on specific cells.

If you could help me with this I would certainly appreciate the help.
Tankerman

"Gord Dibben" wrote:

Tank

Try username instead.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 0
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment < "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
ActiveWorkbook.Application.UserName & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
ActiveCell.Comment.Visible = False
'comment perhaps should be resized
End Sub

Gord Dibben MS Excel MVP

On Wed, 3 Jan 2007 11:44:00 -0800, tankerman
wrote:

I have a spreadsheet that is on a shared drive and several people update this
sheet daily, I had found this VB that was posted by someone back in August of
this year and it works almost perfect except for one small item, HOW can I
track who is updating the cells, when the person puts the sheet on the shared
drive or emailed to one of the others and a cell is updated the name that
appears in the comment is the one who put the sheet on the drive and not the
person updating it. Is there a way to have the one who is updating the cell
to have their name add to the others who had update the cell earlier.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 0
On Error Resume Next
Dim curComment As String
curComment = ""
curComment = Target.Comment.Text
If curComment < "" Then curComment = curComment & Chr(10)
Target.AddComment
Target.Comment.Text Text:=curComment & _
ActiveWorkbook.BuiltinDocumentProperties("Author") & _
Chr(10) & " Rev. " & Format(Date, "yyyy-mm-dd ") & _
Format(Time, "hh:mm")
ActiveCell.Comment.Visible = False
'comment perhaps should be resized
End Sub