Mac Forum / Applications / Excel / September 2007

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

Quoting from "Realtor Tim" <Coon Rapids>, in article
AD3A6E22-A93A-498C-BC08-4E725EF96974@microsoft.com, on [DATE:

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.