Help about saving changes

[Rekx]

First of thanks to everyone on this board for helping me out the last couple of days. You all are very friendly and soooo knowledgable about Excel...I wish there was something I had to offer.

Well, in the last couple of days I am very proud to have created this spreadsheet with the help of this message board;

reporting check sheet.xls
	A	B	C	D	E	F	G	H
1	Client	Rpts.Required	DateChecked	Current?	MissingDocument	Action	ActionLetterSent	SpecialCommentsFromClient
2	ACG	Shipments	2/20/2004		jhgjyg	hgfghf		None
3
4	ACMInc.	Shipment	2/20/2004		Current	Current		None
5		Bulk	2/20/2004		Current	Current		None
6		Storage	2/20/2004		Current	Current		None
7
8	AlexanderInternational	Storage	2/20/2004		Current	Current		None
9	AmericanPortServices	Storage	2/20/2004		Current	Current		None
10
11	AremiDistrubuidora	Shipments	2/20/2004		ytu	tyuty		None
12		Storage	2/20/2004		Current	Current		None
13
14	BruceAlbrightAgency	Storage	2/20/2004		Current	Current		None
15		Bulk	2/20/2004		Current	Current		None
Sheet1

When you hit, say, the "Shipment Update" command button, it automatically inserts the current date, asks if they are current, selects the correct button, and asks what documents are missing and what actions where taken etc and inputs them into the appropiate field.

My question is, is it possible for excel to record on a seperate sheet each change or update made for each client and all the info that was changed. For instance, for the client ACG at the top,I already have a link to another page, and I would like a history of each update kept on that page.

Is this possible? Thanks for your help.
-Adam

 

[GorD]

Not sure if this is what you are looking for but here goes anyway. Within the tools menu there is a track changes option. I have used this with a shared workbook, that allows different users to update at the samer time and it does allow all changes to be logged on a separate sheet along with who made the change

 

[Greg Truby]

You might also do a search here for keywords Change and Tracking. One of the hits was this thread which features some nice code by Andrew P.

 

[Rekx]

But wait a minute...if you make the workbook shared and/track changes then you can't use or edit the macros?

 

[TryingToLearn]

If you don't find an appropriate answer with the information given, consider adding a sheet called History and add code to your existing command button to copy it to the history sheet as well as whereever else it is currently going.

HTH

 

[Rekx]

Well, that is a good idea.

But how would I get it to post the info to decending cells on the history sheet?

For instance, on the main sheet, the commond button changes the same cells every time. But on the history sheet I would want it to go down the line (e1,2,3,4,5 etc) every time a change is made.

 

[Greg Truby]

The code Andrew posted in the thead I cross-reference above shows one way to do just that.

 

[Rekx]

hmmm. yeah, that is what I want to do, but that code written by Andrew doesn't seem to do anything; it doesn't make any changes on the "Track" sheet. Are you sure the code is right, or am I doing something wrong?


Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewVal As Variant
Dim OldVal As Variant
Dim Rng As Range
If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Target.Value = NewVal
With Worksheets("Track")
Set Rng = .Range("A65536").End(xlUp).Offset(1, 0)
With Rng
.Value = OldVal
.Offset(0, 1).Value = NewVal
.Offset(0, 2).Value = Now
.Offset(0, 2).NumberFormat = "dd/mm/yy hh:mm:ss"
End With
End With
Application.EnableEvents = True
End Sub

 

[Rekx]

Now I get an error message from the code:
"Run Time Error '1004':
Method 'Undo' of object '_Application' failed"

 

[Greg Truby]

Sounds like you may have overlooked Andrew's footnote to alter the code per your requirement. But in any event, the code he posted on that thread is pretty much assuming you're tracking changes to a single cell. I've modified his code a bit to track changes on multiple cells. You just define the object variable rngToTrack to correspond to the cells you want to track.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#007F00">' Borrowed from Andrew Poulsom</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> NewVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> OldVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range, rngToTrack <SPAN style="color:#00007F">As</SPAN> Range
    
    <SPAN style="color:#00007F">Set</SPAN> rngToTrack = [A:A,B20:D40,J1]       <SPAN style="color:#007F00">' <=== CHANGE PER YOUR NEEDS</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rngToTrack) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    NewVal = Target.Value
    Application.Undo
    OldVal = Target.Value
    Target.Value = NewVal
    <SPAN style="color:#00007F">With</SPAN> Worksheets("Track")
        <SPAN style="color:#00007F">Set</SPAN> Rng = .Range("A65536").End(xlUp).Offset(1, 0)
        <SPAN style="color:#00007F">With</SPAN> Rng
            .Value = Target.Address(False, False, xlA1)
            .Offset(0, 1).Value = OldVal
            .Offset(0, 2).Value = NewVal
            .Offset(0, 3).Value = Now
            .Offset(0, 3).NumberFormat = "dd/mm/yy hh:mm:ss"
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

NOTE: I hope I did not give the impression the code on that thread was the very best example out there. It's certainly a fine example, but again, if you do a search for Change and Tracking you may find several other examples that would yield ideas for you.

HTH