file revision control

[Realtor Tim]

I hope I can express what I need clearly.
I am looking for a way to:
1) Check a range of cells to see if they have been modified since the last
time the worksheet was opened.
2) If they were changed in the previous session, then increment the revision
cell by one.
3) copy the changed cell range into the "checker" area, this becomes the new
standard for the next open.
I am trying to bump the revision whenever anybody changes a cell in the
range and then saves it. I don't want to bump the rev if they just open the
worksheet and print it or even just look at it. But if they changed any cell
in the range, then the next person to open the file gets the new revision. I
think it would be easiest to check at the open rather than any time the
worksheet is saved or at closing. I save changes a few times while I work on
something. And I would forget to bump the rev before I closed the worksheet.

 

[Jim Gordon MVP]

Hi Tim,

How are the various people going to share this workbook? Is it on a shared
directory that everyone involved has access to, or is it a workbook you pass
around from one person to another in succession?

The first thing to do is to see whether or not Excel already has some
functionality built-in that would satisfy your needs. Search Excel help for
this topic: About ways to share information.

While you're in Excel's help, be sure to read this topic:
How change history works
This topic has a link to instructions on how to display the history.

Using Tools > Sharing > Advanced you can tell Excel to keep a history of all
changes within a workbook. The date & time, change, and who made the change
is logged automatically for you. This method would save you the trouble of
building a custom macro. Check this feature out. If it's sufficient for your
purpose, then your work is done.

But if the built-in history feature is not robust enough, we can suggest how
to build a macro that handles things exactly as you described.

-Jim

I hope I can express what I need clearly.
I am looking for a way to:
1) Check a range of cells to see if they have been modified since the last
time the worksheet was opened.
2) If they were changed in the previous session, then increment the revision
cell by one.
3) copy the changed cell range into the "checker" area, this becomes the new
standard for the next open.
I am trying to bump the revision whenever anybody changes a cell in the
range and then saves it. I don't want to bump the rev if they just open the
worksheet and print it or even just look at it. But if they changed any cell
in the range, then the next person to open the file gets the new revision. I
think it would be easiest to check at the open rather than any time the
worksheet is saved or at closing. I save changes a few times while I work on
something. And I would forget to bump the rev before I closed the worksheet.

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Jim Gordon MVP]

Using Tools > Sharing > Advanced

Should say Tools > Share Workbook. Advanced tab.

-Jim

 

[JE McGimpsey]

I hope I can express what I need clearly.
I am looking for a way to:
1) Check a range of cells to see if they have been modified since the last
time the worksheet was opened.
2) If they were changed in the previous session, then increment the revision
cell by one.
3) copy the changed cell range into the "checker" area, this becomes the new
standard for the next open.
I am trying to bump the revision whenever anybody changes a cell in the
range and then saves it. I don't want to bump the rev if they just open the
worksheet and print it or even just look at it. But if they changed any cell
in the range, then the next person to open the file gets the new revision. I
think it would be easiest to check at the open rather than any time the
worksheet is saved or at closing. I save changes a few times while I work on
something. And I would forget to bump the rev before I closed the worksheet.

Jim's given you some good advice - this is only one way of using VBA to
implement a solution:

Put this in your ThisWorkbook code module (CTRL-click the workbook
titlebar and choose View Code):

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Static bAlreadyBumpedRevision As Boolean

If Not bAlreadyBumpedRevision Then
If Not Me.Saved Then
With Sheets("Sheet1").Range("A1")
.Value = .Value + 1
End With
bAlreadyBumpedRevision = True
End If
End If
End Sub

Change Sheet1!A1 to suit. This will increment the rev number whenever a
change is made in the workbook, but only once per session. It won't
update if you print, or if you open and close the workbook without
making changes.

Using the .Saved property in the _BeforeSave event is far easier than
copying values to a check range and running checks, but it will also be
triggered if formatting is changed, or a change is made then reversed
with Undo, too.

Note that this will NOT work for a shared workbook (being used by
multiple people at the same time).

If you want to check a particular range's values (only) against a saved
range, one way:

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim bAlreadyBumpedRevision
Dim rCheckRange As Range
Dim i As Long

With Sheets("Sheet1").Range("A1:J10")
Set rCheckRange = Sheets("CheckSheet").Range(.Address)
If Not bAlreadyBumpedRevision Then
For i = 1 To .Cells.Count
If .Cells(i).Value <> rCheckRange.Cells(i).Value Then
With Sheets("Sheet1").Range("L1")
.Value = .Value + 1
End With
bAlreadyBumpedRevision = True
Exit For
End If
Next i
End If
rCheckRange.Value = .Value
End With
End Sub

Where sheet "CheckSheet" could be a hidden sheet, and the revision cell
(here L1) is NOT in the checkrange (here A1:J10), or else it will update
each time.

 

[Jim Gordon MVP]

And to make it even more flexible you could put the version number in so
many different places: the Properties, a header, a footer, a cell, hidden
somewhere... Give some thought as to what the ideal situation would be for
you.

-Jim

Jim's given you some good advice - this is only one way of using VBA to
implement a solution:

Put this in your ThisWorkbook code module (CTRL-click the workbook
titlebar and choose View Code):

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Static bAlreadyBumpedRevision As Boolean

If Not bAlreadyBumpedRevision Then
If Not Me.Saved Then
With Sheets("Sheet1").Range("A1")
.Value = .Value + 1
End With
bAlreadyBumpedRevision = True
End If
End If
End Sub

Change Sheet1!A1 to suit. This will increment the rev number whenever a
change is made in the workbook, but only once per session. It won't
update if you print, or if you open and close the workbook without
making changes.

Using the .Saved property in the _BeforeSave event is far easier than
copying values to a check range and running checks, but it will also be
triggered if formatting is changed, or a change is made then reversed
with Undo, too.

Note that this will NOT work for a shared workbook (being used by
multiple people at the same time).

If you want to check a particular range's values (only) against a saved
range, one way:

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim bAlreadyBumpedRevision
Dim rCheckRange As Range
Dim i As Long

With Sheets("Sheet1").Range("A1:J10")
Set rCheckRange = Sheets("CheckSheet").Range(.Address)
If Not bAlreadyBumpedRevision Then
For i = 1 To .Cells.Count
If .Cells(i).Value <> rCheckRange.Cells(i).Value Then
With Sheets("Sheet1").Range("L1")
.Value = .Value + 1
End With
bAlreadyBumpedRevision = True
Exit For
End If
Next i
End If
rCheckRange.Value = .Value
End With
End Sub

Where sheet "CheckSheet" could be a hidden sheet, and the revision cell
(here L1) is NOT in the checkrange (here A1:J10), or else it will update
each time.

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Realtor Tim]

Thank you gentlemen. As always, I knew I would get good advice. I have found
that if a person can expresstheir problem well enough, you (The Community)
will have at least one answer. Very few of us are the first one to try and do
something and ran into difficulty.
Thank you again.