Help about saving changes

Rekx

New Member
Joined
Feb 19, 2004
Messages
12
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
ABCDEFGH
1ClientRpts.RequiredDateCheckedCurrent?MissingDocumentActionActionLetterSentSpecialCommentsFromClient
2ACGShipments2/20/2004jhgjyghgfghfNone
3
4ACMInc.Shipment2/20/2004CurrentCurrentNone
5Bulk2/20/2004CurrentCurrentNone
6Storage2/20/2004CurrentCurrentNone
7
8AlexanderInternationalStorage2/20/2004CurrentCurrentNone
9AmericanPortServicesStorage2/20/2004CurrentCurrentNone
10
11AremiDistrubuidoraShipments2/20/2004ytutyutyNone
12Storage2/20/2004CurrentCurrentNone
13
14BruceAlbrightAgencyStorage2/20/2004CurrentCurrentNone
15Bulk2/20/2004CurrentCurrentNone
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

New Member
Joined
Feb 19, 2004
Messages
12
But wait a minute...if you make the workbook shared and/track changes then you can't use or edit the macros?
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723

ADVERTISEMENT

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

New Member
Joined
Feb 19, 2004
Messages
12
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

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

Rekx

New Member
Joined
Feb 19, 2004
Messages
12
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

New Member
Joined
Feb 19, 2004
Messages
12
Now I get an error message from the code:
"Run Time Error '1004':
Method 'Undo' of object '_Application' failed"
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top