Can someone help me with some related but slightly different problems?

1) Is there a function that will detect whether or not a cell has been
changed since a spreadsheet was opened? If so I can use this to prompt a
user to increment a version number in the file, which in turn will prompt
them to save the file to a different file name.

2) That's my simplest hope. If not, is there a method of automatically
incrementing a version number when a file opens? This would also mean I
would have to somehow force the user to save the file to a different file
name, since it would not be immediately obvious that it has happened (if you
see what I mean). Can this be done?

1) Not as an attribute that you can examine. Editing a cell would trigger
the change event and you could write code to work with that.

2) See the Events page at Chip Pearson's site.
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy



"Geoff C" <Geoff wrote in message
...
Can someone help me with some related but slightly different problems?

1) Is there a function that will detect whether or not a cell has been
changed since a spreadsheet was opened? If so I can use this to prompt a
user to increment a version number in the file, which in turn will prompt
them to save the file to a different file name.

2) That's my simplest hope. If not, is there a method of automatically
incrementing a version number when a file opens? This would also mean I
would have to somehow force the user to save the file to a different file
name, since it would not be immediately obvious that it has happened (if
you
see what I mean). Can this be done?

1) It seems you just want to detect a change. SO you could use
worksheet_change event to set a global flag, and test that in BeforeClose
event.

This code would go in Thisworkbook

Public fChange As Boolean


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If fChange Then
MsgBox "File changed, save under another name"
Cancel = True
End If
End Sub

and then

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Target
ThisWorkbook.fChange = True
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Geoff C" <Geoff wrote in message
...
Can someone help me with some related but slightly different problems?

1) Is there a function that will detect whether or not a cell has been
changed since a spreadsheet was opened? If so I can use this to prompt a
user to increment a version number in the file, which in turn will prompt
them to save the file to a different file name.

2) That's my simplest hope. If not, is there a method of automatically
incrementing a version number when a file opens? This would also mean I
would have to somehow force the user to save the file to a different file
name, since it would not be immediately obvious that it has happened (if
you
see what I mean). Can this be done?

You can detect the change, by Me.Saved = True or False, in
WorkBook_BeforeSave event procedure.
If Me.Saved is True, it means nothing has been changed in the workbook since
it was opened.
If false, it means it has been changed.
No need to use WorkBook_Change procedure.

I am giving below complete code.

You need to do following before running the code:

1. Name the Cell in the workbook which contains the Version number as
"VerNumb".
2. In VBA project insert a module and define following two public variables.
Rather
copy the below line in the Module. "Please note this must be done in a
Module!"

Public IamSaving As String, LastVerNumb As String

3. In WorkBook_Open() procedure add following code:
Private Sub Workbook_Open()
LastVerNumb = Range("VerNumb").Value
End Sub

4. Finally The WorkBook_BeforeSave procedure as below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim rsMyName As String, rsNewName As String
If Me.Saved = True Then Exit Sub
If IamSaving = "Yes" Then Exit Sub
If Range("VerNumb").Value = LastVerNumb Then
MsgBox "The contents of the workbook have been changed." & Chr(13) &
_
"Therefore you must change the Version Number." & Chr(13) & _
"The file will not be saved unitl you change the Version Number.", _
vbSystemModal, "Protecting Versions"
Cancel = True
Exit Sub
End If
rsMyName = Me.Name

GetNewName:
rsNewName =
Application.GetSaveAsFilename(InitialFileName:="Ne wFile.xls",
FileFilter:="WorkBook (*.xls), *.xls", Title:="Protecting Versions")
If rsNewName = "False" Then
Cancel = True
Exit Sub
End If
If InStr(1, rsNewName, "NewFile.xls", vbTextCompare) 0 Then
MsgBox "Please enter another valid file name.", vbSystemModal,
"Protecting Versions"
GoTo GetNewName:
End If
If InStr(1, rsNewName, rsMyName, vbTextCompare) 0 Then
MsgBox "Please enter a different file name!", vbSystemModal,
"Protection Version"
GoTo GetNewName:
End If
IamSaving = "Yes"
Me.SaveAs Filename:=rsNewName
IamSaving = "No"
LastVerNumb = Range("VerNumb").Value
Cancel = True
End Sub

"Geoff C" <Geoff wrote in message
...
Can someone help me with some related but slightly different problems?

1) Is there a function that will detect whether or not a cell has been
changed since a spreadsheet was opened? If so I can use this to prompt a
user to increment a version number in the file, which in turn will prompt
them to save the file to a different file name.

2) That's my simplest hope. If not, is there a method of automatically
incrementing a version number when a file opens? This would also mean I
would have to somehow force the user to save the file to a different file
name, since it would not be immediately obvious that it has happened (if
you
see what I mean). Can this be done?

Many thanks for all the suggestions, there is plenty to try out there! I
hope my VBA is up to it.

Thanks, Geoff.