VBA solution

[lucky]

Hi All
Problem: I wish to record the changes to certain cells in a worksheet when worksheet.save. I wish to make a table on a separate sheet listing the cells (or named ranges) that I wish to monitor. So each time the workbook is saved, the table creates a new line of information (with the date) Therefore I end up with a table of information on a separate sheet that records the contents in certain cells on different dates.
Reason: I wish to monitor changes made by a small number of users who will be given the password to the protected worksheet (so only they can save changes). For this reason I cannot use the Share Workbook - Track Changes functionality in Excel.
Solution: By creating a separate table referencing the monitored cells (or named ranges) and using the 'offset' property/method (to create another line in the table)?? when worksheet.save But I am no good at writing such code. Any help/ideas appreciated!!!!
Thanks for you time.

 

[Damon Ostrander]

Hi Lucky,

Lets just say for example that the list of cells or named ranges (I will assume each named range consists of one cell) you want to save are in cells A1:An (where n is any number) in worksheet "Save Cells", and the worksheet containing the saved lines is named "Data Log". Your workbook Save event should look something like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim SWS As Worksheet
Dim LWS As Worksheet
Dim NewRow As Long
Dim iRow As Integer
Set SWS = Worksheets("Save Cells")
Set LWS = Worksheets("Data Log")
NewRow = LWS.[a65536].End(xlUp).Row + 1
'put date-time stamp in first column
LWS.Cells(NewRow, 1) = Now()
For iRow = 1 To SWS.[a65536].End(xlUp).Row
'use iRow on SWS to determine the column on LWS
LWS.Cells(NewRow, iRow + 1) = Range(SWS.Cells(iRow, 1).Value)
Next iRow
End Sub

It is important that if the ranges you are logging are not always going to be on the active worksheet at the time the save occurs that the ranges specified on the Save Cells worksheet specify the worksheet as well as the cell, e.g., Sheet1!B4, or Sheet1!GrandTotal...

 

[lucky]

Thank you Damon for your very precise response.
A bit of personalising and it has worked a treat!!
Cheers!