VBA File Save if File is Changed

[natezenmaster]

Hello programmers better than me,

Let's say I want to create a VBA program (or sub) to:
(1) save a file to a directory each time the contents (any cell in a list) is changed
(2) generate a time stamp or some relative way to create multiple files when the file is saved
(3) allow the original file to stay put and incorporate the new/amended data

E.g. Book1.xls is a shared workbook, user A accesses the file and enters some data and your clever VBA saves a version off (Book1TimeStamp.xls) in addition to saving the entered data to the original version (Book1.xls). Then, user B accesses file and screws things up and saves data to original version (Book1.xls) - I would like to be able to go back to Book1TimeStamp and retrieve the data from user A. Make sense?

Hard or easy to keep version history like this???

Many advanced thanks and gold stars to those future responders.

 

[natezenmaster]

Older, less clever me,

Here is the solution for those keeping tabs at home:

After my VBA routine runs its main program then it calls 'checkversion' sub in order to determine if new data warrants a version history saved off. If new data is present then it saves a new version off and saves the current version as the current file - that way each time a user enters data - I have the previous version saved and incorporate the new data in the current file... works as follows: (and yes, I will have to make sure the number of users doesn't get too large or too many files are created in my directory)

Sub checkversion()
' select dashboard and look at version marker
Sheets("Dashboard").Select
curver = Cells(1, 1).Value ' cell keeps track of version history
' take a look at current data in the entry list
Sheets("Task Entry").Select ' grab the current list of data
' set change notification to 0 (no change)
changeflag = 0
' read in data for comparison later
For counter = 1 To 32
currlist(counter) = Cells(counter + 1, 3)
Next counter
' open last known version
vername = "OG" & curver & ".xls"
lastknown = "\\directory\SharedWorkbook\History\" & vername
Workbooks.Open Filename:=lastknown ' open last known version
Windows(vername).Activate
' read in last known data in entry list for comparison
Sheets("Task Entry").Select
For counter = 1 To 32
lastlist(counter) = Workbooks(vername).Sheets("Task Entry").Cells(counter + 1, 3)
'MsgBox (currlist(counter) & " curr versus last " & lastlist(counter))
Next counter
' compare the data and alter change notification if a change is found
For counter = 1 To 32
If currlist(counter) = lastlist(counter) Then
changeflag = changeflag
Else
changeflag = 1
End If
Next counter
' if there is a change then save a new version off, close other workbook and save current file with new data
If changeflag = 0 Then ' no change
Windows(vername).Activate
ActiveWorkbook.Close False
Else ' list has changed
curver = curver + 1
Windows(vername).Activate
ActiveWorkbook.Close False
Windows("Task List to Calendar In Use.xls").Activate
Sheets("Dashboard").Select
Cells(1, 1).Value = curver ' cell keeps track of version history
vername = "OG" & curver & ".xls" ' append version history for opening
lastknown = "\\directory\SharedWorkbook\History\" & vername
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=lastknown
ActiveWorkbook.SaveAs Filename:="\\directory\SharedWorkbook\SharedWB.xls"
Application.DisplayAlerts = True
End If
'MsgBox ("Change Flag is " & changeflag)
End Sub

Works like a champ... hope others find it useful...