Forcing input by user when cells are changed
Hi team,
Unfortunately no replies as yet, so I have cross posted at http://www.mrexcel.com/forum/showthr...90#post2762390. Will update both as news to hand.
I am ultimately trying to achieve a track changes with commentary by the comment box and I have found two extremely useful posts but have had no joy in trying to bring the two lots of code together.
The first I found here http://excel.bigresource.com/Track/excel-tr0ODCpD/. This is the most useful as it tracks all changes in a cell from x to y and date and username stamps the entry and any subsequent changes are listed underneath, and it autosizes beautifully. However for my purpose I need to add a little bit of information to each entry. So I went in search of code that pops up a dialog box and what is input in the dialog box is recorded as the comment.
I found this useful post here http://www.excelforum.com/excel-prog...e-comment.html. The only thing is getting it to work for all cells that aren't protected, and combining it with the above code so that a resulting comment would look something like:
Changed from "x" to "y" on 21/06/11 at 7:17pm by olliestyles (from first code)
Reason: Agreed new value with stylieo by email on 20/06/11 (this text input by the textbox that pops up from the 2nd code).
So, if there were two changes to the same cell the comment box would read:
Changed from "x" to "y" on 21/06/11 at 7:17pm by olliestyles
Reason: Agreed new value with stylieo by email on 20/06/11
Changed from "y" to "x" on 22/06/11 at 7:17pm by olliestyles
Reason: Stylieo changed his mind and reverted to x value by phone on 22/06/11
Thanks heaps for your time.
Ollie
Last edited by olliestyles; 06-26-2011 at 06:12 AM.
Re: Forcing input by user when cells are changed
After posting to http://www.mrexcel.com/forum/showthr...98#post2766298 and receiving help from pbornemeier and then messing with his solution, I have ended up with the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewText As String
Dim OldText As String
Dim OldValue As String
Dim NewValue As String
Dim Str As String
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
Str = InputBox("Reason for change")
If Str = "" Then Str = "No reason given, change invalid"
NewText = "- Updated on " & Format(Date, "yyyy-mm-dd ") _
& " at " & Format(Time, "hh:mm:ss") _
& " - " & " Changed from " & OldValue & " to " & NewValue _
& " by " & Application.UserName & " because " & Str & vbLf
If Target.Comment Is Nothing Then
Target.AddComment
End If
With Target.Comment
.Shape.TextFrame.AutoSize = True
OldText = .Text & vbLf
.Text Text:=OldText & NewText
End With
Target.Value = NewValue
Application.EnableEvents = True
End Sub
Hope this helps someone else too.
Regards,
Ollie
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks