Is there a formula that will add a 1 to a number upon saving a new worksheet.

hi,
possibly.
add a 1 to what number....where?
and what do you mean when you say "Saving a new worksheet"?

Regards
FSt1

"llivey" wrote:

Is there a formula that will add a 1 to a number upon saving a new worksheet.

You need to write a very small macro. Here is the VBA help instructions.
the code must be placed in the Thisworkbook object in VBA. In the Tools Menu
- Macro - visual Basic editor. the left window is the Project Window.
Double click Thisworkbook.

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel as Boolean)

sheets("sheet1").range("A3") = sheets("sheet1").range("A3") + 1
End Sub


VBA Help
---------------------------------------------------------------------------------------
WorkbookBeforeSave Event
See AlsoApplies ToExampleSpecificsOccurs before any open workbook is saved.

Private Sub object_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUi
As Boolean, ByVal Cancel As Boolean)
object An object of type Application declared with events in a class
module. For more information, see Using Events with the Application Object.

Wb The workbook.

SaveAsUi True if the Save As dialog box will be displayed.

Cancel False when the event occurs. If the event procedure sets this
argument to True, the workbook isn't saved when the procedure is finished.

Example
This example prompts the user for a yes or no response before saving any
workbook.

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel as Boolean)
a = MsgBox("Do you really want to save the workbook?", vbYesNo)
If a = vbNo Then Cancel = True
End Sub



"llivey" wrote:

Is there a formula that will add a 1 to a number upon saving a new worksheet.

You cannot save a worksheet, you can only save a workbook.

No, a formula cannot react to a save operation.

You would have to use event code to do what you wish.

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

Right-click on the Excel logo left of "File" or at left end of Title bar if not
maximized.

Select "View Code" and paste the above into that module.

Alt + q to return to Excel window.

If you are using this method for an invoice or similar, you may want to check
out JE McGimpsey's site on sequential numbering.

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html


Gord Dibben MS Excel MVP

On Sat, 5 May 2007 12:46:01 -0700, llivey
wrote:

Is there a formula that will add a 1 to a number upon saving a new worksheet.

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"llivey" wrote in message
...
Is there a formula that will add a 1 to a number upon saving a new
worksheet.