Automatic Update Upon Opening of Spreadsheet
 

I have a cell containing a number that I need to increase by an increment of
one each time I open the document. Is there a formula I can write that will
automatically do this for me? I am relatively new to the more advanced
capabilities of Excel, though I have a basic understanding of Visual Basic;
do I need to create a Macro or can I use a simple formula on the spreadsheet?
Thank you.

Automatic Update Upon Opening of Spreadsheet
 

Press Alt + F11 to open the Visual Basic Editor.
In the Project Explorer in the upper left, double-click the Thisworkbook
icon to open
the workbook module

In the module window drop down the list in the first combo-box below the
toolbars and select WORKBOOK, and then select OPEN in the combo-box to the
left.

Enter the following code, modifying the Sheet1 value to match the sheet that
will receive the update and the A1 reference to the cell that is to be
updated on open:


Dim ws As Worksheet
Dim l As Long

Set ws = ThisWorkbook.Sheets(1)

l = ws.Range("A1").Value
Range("A1").Value = l + 1
ActiveWorkbook.Save

Set ws = Nothing

Click FILE in the menu and select CLOSE AND RETURN TO EXCEL.

Save and close the workbook and reopen it to test drive.

If you don't want anyone to modify this value you might want to consider
protecting the worksheet.
--
Kevin Backmann


"Sara" wrote:

I have a cell containing a number that I need to increase by an increment of
one each time I open the document. Is there a formula I can write that will
automatically do this for me? I am relatively new to the more advanced
capabilities of Excel, though I have a basic understanding of Visual Basic;
do I need to create a Macro or can I use a simple formula on the spreadsheet?
Thank you.

Automatic Update Upon Opening of Spreadsheet
 

Thank you so much! That was incredibly helpful.

"Kevin B" wrote:

Press Alt + F11 to open the Visual Basic Editor.
In the Project Explorer in the upper left, double-click the Thisworkbook
icon to open
the workbook module

In the module window drop down the list in the first combo-box below the
toolbars and select WORKBOOK, and then select OPEN in the combo-box to the
left.

Enter the following code, modifying the Sheet1 value to match the sheet that
will receive the update and the A1 reference to the cell that is to be
updated on open:


Dim ws As Worksheet
Dim l As Long

Set ws = ThisWorkbook.Sheets(1)

l = ws.Range("A1").Value
Range("A1").Value = l + 1
ActiveWorkbook.Save

Set ws = Nothing

Click FILE in the menu and select CLOSE AND RETURN TO EXCEL.

Save and close the workbook and reopen it to test drive.

If you don't want anyone to modify this value you might want to consider
protecting the worksheet.
--
Kevin Backmann


"Sara" wrote:

I have a cell containing a number that I need to increase by an increment of
one each time I open the document. Is there a formula I can write that will
automatically do this for me? I am relatively new to the more advanced
capabilities of Excel, though I have a basic understanding of Visual Basic;
do I need to create a Macro or can I use a simple formula on the spreadsheet?
Thank you.