PDA

View Full Version : Sleeper: Code to add comment when value of cell is changed



Immatoity
09-29-2005, 04:10 AM
Hi

Is it possible to do this?

I have a file that 7-8 people are using, and I want to be able to tell the end user if a cell value has been changed from the original. They are values...

eg when my existing macro populates the sheet it has a column of values (amongst other things). Then these 7-8 people can go in, and are allowed to change the values if necessary but the end user needs to see what the amount has been changed from. ie a comment with "val changed from ?50 to ?1500 by USER X 15/9/05", or better still/alongside create a new column where it "copies" the original value (pre-change) to allow a simple visual comparison?

I hope this makes sense?

MWE
09-29-2005, 04:54 AM
Hi

Is it possible to do this?

I have a file that 7-8 people are using, and I want to be able to tell the end user if a cell value has been changed from the original. They are values...

eg when my existing macro populates the sheet it has a column of values (amongst other things). Then these 7-8 people can go in, and are allowed to change the values if necessary but the end user needs to see what the amount has been changed from. ie a comment with "val changed from ?50 to ?1500 by USER X 15/9/05", or better still/alongside create a new column where it "copies" the original value (pre-change) to allow a simple visual comparison?

I hope this makes sense?
Makes a lot of sense. This is not hard to do. I propose:
1. using the worksheet change function to monitor activity
2. using a separate (possibily hidden) tab to keep track of history

1 & 2 are easy; I have applications that do this.

However, knowing what the value was before the change is a little more difficult. If you wish to monitor a finite number of cells in the worksheet, then before/after is not too hard. If you want to monitor everything, that is a little more difficult. Approx how many sheets and how many cells would you want to monitor for the target worksheet? An exact number is not required; just an estimate.

Also, would you want to keep track of who opened the workbook (and when) even if they make no change? Any other "security"?

Immatoity
09-29-2005, 05:45 AM
hiya...cheers for the reply

yeah I want to keep a tab of what the original cell value was...this will apply to one column only..max no of rows (therefore cells) is approx 1000.
Thinking about it would it be easier to essentially copy the column with the values in it to either a hidden column in the same tab or to a new tab and then somehow compare?

I would still prefer the original comment way, but anything is better than nothing.

I would love some additional security...one thing I have been asked to do is (in the tab these 7-8 people work in) only allow them to change data in columns F,G, and H...and protect A-e and I to X -but these other columns should be visible. However, not as easy as it sounds as the "tab" is cleared then populated by a macro...so at that stage the other columns need to be unprotected, its just before the file is accessed by the 7-8 others it needs switching on (and needs to be able to be switched off).

johnske
09-29-2005, 07:04 PM
Hi

Is it possible to do this?

I have a file that 7-8 people are using, and I want to be able to tell the end user if a cell value has been changed from the original. They are values...

eg when my existing macro populates the sheet it has a column of values (amongst other things). Then these 7-8 people can go in, and are allowed to change the values if necessary but the end user needs to see what the amount has been changed from. ie a comment with "val changed from ?50 to ?1500 by USER X 15/9/05", or better still/alongside create a new column where it "copies" the original value (pre-change) to allow a simple visual comparison?

I hope this makes sense?For this particular solution to the problem you will need to have the original values on another sheet, in this example I've made that Sheet2.

Put this code in one of the other sheet modules...


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet2.Range(Target.Address) = Target
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Target.ClearComments
With Target
.AddComment
.Comment.Visible = False
.Comment.Text Text:="Previous value = " & Sheet2.Range(Target.Address)
End With
End Sub

HTH,
John :)

malik641
09-29-2005, 08:54 PM
I would still prefer the original comment way, but anything is better than nothing.I'm sure you can do this. I just have one question, how many times will the value be changed? If it's just once, then adding a comment will be relatively easy. If it's more than once, I would need more time. But I still think this is do-able.

Give me a little bit I'll see what I can fix up :yes

malik641
09-29-2005, 09:12 PM
Awesome, check this out.

This only works for one cell at a time (a range of cells will be ignored and will only use the 1st row with 1st column of the range), just so you know.

Just put this in a worksheet module.

:thumb Works pretty good for me :thumb


Option Explicit

Public OldValue As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
OldValue = Target(1, 1).Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
Dim cmtText As String
Dim TheDate As Date
Dim TheTime As Date
If OldValue = 0 Then Exit Sub
TheDate = Format(Date, "Medium Date")
TheTime = Format(Time, "Medium Time")
cmtText = "Old value was " & OldValue & Chr(10) & "New value is " & Target(1, 1).Value & Chr(10) & "Date: " & _
TheDate & Chr(10) & "Time: " & TheTime
With Target(1, 1)
.ClearComments
.AddComment
.Comment.Visible = False
.Comment.Text Text:=cmtText
End With
End Sub

Is this what you were asking for???