I have a file that is updated many times during a month. It is possible to
include a Rev X in the file name so that X is automatically incremented each
time the spreadsheet is closed. Currently the Rev number has to be manually
changed.
--
JPS

Suppose the user want to close the workbook without making any changes??
--
Gary''s Student - gsnu200806


"JPS" wrote:

I have a file that is updated many times during a month. It is possible to
include a Rev X in the file name so that X is automatically incremented each
time the spreadsheet is closed. Currently the Rev number has to be manually
changed.
--
JPS

If the users response to the message "Do you want to save changes made to
xxx" is no, then do not increment, If they respond yes, or save manually,
increment the rev number +1.

We start a new file each month, so the first time saved would be Rev 1 but
we can handle this manually.
--
JPS


"Gary''s Student" wrote:

Suppose the user want to close the workbook without making any changes??
--
Gary''s Student - gsnu200806


"JPS" wrote:

I have a file that is updated many times during a month. It is possible to
include a Rev X in the file name so that X is automatically incremented each
time the spreadsheet is closed. Currently the Rev number has to be manually
changed.
--
JPS

Put the following macro in the workbook code area:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("Sheet1").Range("Z100")
.Value = .Value + 1
End With
End Sub

Adjust the sheetname and cell to meet your requirements.

Because it is workbook code, it is very easy to install and use:

1. right-click the tiny Excel icon just to the left of File on the Menu Bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm



--
Gary''s Student - gsnu200807


"JPS" wrote:

If the users response to the message "Do you want to save changes made to
xxx" is no, then do not increment, If they respond yes, or save manually,
increment the rev number +1.

We start a new file each month, so the first time saved would be Rev 1 but
we can handle this manually.
--
JPS


"Gary''s Student" wrote:

Suppose the user want to close the workbook without making any changes??
--
Gary''s Student - gsnu200806


"JPS" wrote:

I have a file that is updated many times during a month. It is possible to
include a Rev X in the file name so that X is automatically incremented each
time the spreadsheet is closed. Currently the Rev number has to be manually
changed.
--
JPS