Report of Changes made to Large Worksheet

Good Morning,

I have a large worksheet (50x3400) that some users may need to make changes to. Changes could be in most columns except Column A, RecordNumber. My goal is to create an audit-type worksheet that shows what a user has changed. The user can also delete a whole record or add a new record.

I would like the report worksheet to show something like this:

User (Have function that picks up Excel username)
Date Changed (have function that picks up Excel last date saved)
Record Number (Column A of the row where change occurred)
Column Changed (Text of column header)
Old Value
New Value

If an entire record is added or deleted, the RecordNumber would be filled in and "Record Added" or "Record Deleted" would perhaps appear in the Column Changed field. This seems to me to be a better solution than taking up 50 rows to show how each column changed.

Any suggestions would be most appreciated.

Thanks,
tom

I looked at Track Changes but it does not seem to give me the ability to show all changes on a separate worksheet.

You could load all your data into an array on Workbook Activate..

Private Sub Workbook_Activate()

LastRow = ActiveSheet.UsedRange.Rows.Count
LastCol = ActiveSheet.UsedRange.Columns.Count

Dim ArrBefore(LastRow, LastCol)
For a = 1 To LastRow
    For b = 2 To LastCol
        ArrBefore(a, b-1) = Cells(a, b).Value
    Next
Next

End Sub

then on saving you could do you checks...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

CurrentUser = application.UserName      'This is the Excel username
'CurrentUser = Environ("USERNAME")       'This is the system username

For a = 1 To LastRow
    For b = 1 To LastCol
        If ArrBefore(a, b) <> Cells(a, b+1).Value Then
            MsgBox ("User " & CurrentUser & " changed record No" & Cells(a, 1).Text & " Column" & Str(b) & " from " & ArrBefore(a, b) & " to " & Cells(a, b).Value & " on " & Date)
        End If
    Next
Next

End Sub

I've just sent the information to a message box here but you could put it wherever you wanted.

Adding and deleting records is a little trickier as they could feasibly insert/delete any number of rows at any location, but you could probably work something out using the array. Check for array elements that no longer exist in the spreadsheet or ones that appear in the spreadsheet but not the array.

I may have got my +1s and -1s mixed up a bit there when trying to compensate for the "header" column - but you get the gist

Hi Grul,

I will give this a try but as a VBA neophyte I may also need help changing from that message box to stuffing the results into a spreadsheet. May I impose on you further?

tom

Sure, but it may take me a couple of hours, I'll create a sheet instead of trying to do it from memory as I did above

Thanks, Grul!

Pierre is quite correct. Track changes does in fact cover all the points you wanted. (I didn't realise it was that comprehensive).
This is what it gives you...
Changes.jpg

I agree that Track Changes could work but i really do not like the controls and limitations Excel places on the function. for example it also disables macros. And the history page only shows up when you know the right keystroke combo. Very clumsy IMHO. I would much prefer Grul's (or another VBA solution) if we can get one working. Thanks.

Well if you really want a VBA solution I've made a start on it in the attached file.
However, what I've written so far only covers the ammendment of existing cell data.
It doesn't handle new rows or removed rows yet.

I'll keep working on it, and maybe the stuff I've already done will get you started.

Grul, I think this is on the right track. For simplicity, let's ignore the part about entire records being added or deleted...at least for now.

I see how these two functions work (nice job). But I wonder if this will work over weeks and months of changes by many different users. My vision was for a worksheet that would continually grow with changes made by the various users. As it stands now it looks like these functions must be run at the start and end of a session to capture changes. Am I missing something (I hope!). tom

The function to load the current data into the array is run whenever the workbook is opened and whenever it is saved. Every time the user saves the workbook any changes will be logged on Sheet2 and the the array refreshed with the latest spreadsheet data. If they then make more changes and save, the process will be repeated, logging any changes since the last save.
If another user opens the sheet and makes changes the same should happen. The logging process finds the next avilable row on sheet2 and stores the data there.

In theory, this code will keep logging any changes by any user until you run out of rows (about 65.5k changes)

I say "in theory" because, over the years, I've found that nothing is ever as simple as it seems.
I've run a few test updates, opening, changing, saving and exiting as well as multiple saves in one session.
It seems to be OK but I may have missed something, or some new event will throw the calculations off.

You can never make anything idiot proof, because they'll just invent a better class of idiot

Thanks, Grul. Lemme play with it. tom

Me again, Grul. Wow! This works beautifully! The only problem I have left is how the changes are recorded. Is it possible to cite in Column C the column header text instead of the column number? this is soooo what I need. Thanks! tom

OK

I've added the code for checking for new and deleted rows and attached the workbook.

I added a row, deleted a row and modified the values of some existing cells all in one go and it logged them all.
It seems to be working but my testing was fairly quick as it's nearly midnight and I'm getting tired

Thanks for all your help, Grul. I really appreciate it. I will add a post here tonight to let you know how it works for me...I have high expectations!! Good night.

Glad it's helped you.
I'll sort column C out for you in the morning - night.

I just couldn't go to bed without fixing it - it only took a couple of minutes
I've modified it to put the column header in the log rather than the column number.

Obviously my demo sheet has no headers so you won't get the full benefit until you apply it to your data.

Beautiful, Grul! Sleep tight!

What beautiful work, Grul! It works perfectly. I am totally delighted and sooooo appreciative. Thanks ever so much. Best regards. tom

p24leclerk, BTW, what did you fix on Grul's macro? It seemed to work for me. I must have overlooked something. tom

Hi p24leclerk,

Thanks, I will give it a try. I did NOT want to keep a copy of a deleted record. That might not be a bad idea but the change report isn't set up for like 50 columns. If we want a solution others could benefit from too, it might be a good idea to copy a deleted record to a new spreadsheet called "Deleted." Doing so would compensate for the different column counts. I do not need it for this application but, truth is, if I had it I would use it.

Thanks again!

tom

Thanks, p24leclerc, I am most gratified by your effort. I rather like Grul's array solution better, but I also like your addition for deleted records. Thanks again, tom.

Now I have another problem. The code from Drul is absolutely perfect. Until, that is, I paste it into my job. When I made a change to the spreadsheet and saved (causing the macro to take off), it blew up.

I have learned this so far: I have Options Explicit set, Drul did not. The first time it complained it was because a variable, c, had not been defined. No problem, I added c to the Dim statement. Then it blew up again. This time it complained that a subscript was out of range on this line:

If LastRow <> UBound(ArrBefore, 1) Then

So, I'm not a complete fool. I deleted the Option Explicit. Subscript error again!

This is not the end of the world...I can go with no Option Explicit and it works perfectly. But I would love to know what I need to do to fix the thing.

The file I am referring to is Demo3 above.

Thanks,
tom

I'm just on my way to bed, but will take a look in the morning.

Sorry, Grul, but I cannot make the problem go away. It has nothing to do with option explicit...that had to do with a missed dim statement.

I am now thinking it may have to do with the size of the array, 52 columns by 3500 rows.

I got it working just fine with a tiny subset, 24x12. But when I pasted the full data the error was back again.

Any suggestions would be most welcomed!

tom

p.s. I am running Office 2010 and the file is saved as xlsm to enable macros.

Hey Grul, I think I found the answer. The problem went away when quit the file and returned (so the on_activate could take off).

Hi Grul,

I have been playing with this code now for two days! I think most of my above-mentioned problems have been solved but now i have a bigger one. Excel hangs for whenever I add or delete a row.

I also had a strange problem with the code on this file. Upon running the CheckData macro initially, it reported changes I had not made. For example, in a column with mostly integers, it picked up a value of 2.0 and reported it as a changed value.

The file had a couple errors in the data, #REF!, to be specific, and this caused either a mismatch or a out of range error. fixing the errors solved that problem but if errors exist i would just as soon compare them rather than have to fix them to proceed.

I have attached the spreadsheet for your perusal and suggestions.

Thanks,
tom