3 years, 5 months ago
How do I make Excel 2007 increment the value in a given cell whenever the file is opened?
I'd like to give a series of worksheets I'm generating a serial number, and since each successive worksheet is derived by modifying the previous one, it seems like a formula to increment a cell upon file open would easily achieve this. Alternatively, I could write to a separate file upon file close, then read from that separate file upon opening the next file, and add 1.
Separate topics with commas, or by pressing return. Use the delete or backspace key to edit or remove existing topics.
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$1 Answer
Hit Alt-F11 to go to Visual Basic. Double-click on This Workbook, and in the code field, paste:
Private Sub Workbook_Open()
Range("A1").Value = Range("A1").Value + 1
End Sub
You can modify the cell to whatever you need of course. Note you have to have macros enabled or at least set it to prompt for macros in the Security settings. This will increment the cell's value by 1 whenever you open the workbook. In Excel 2007, you will have to save the workbook as .xlsm or as a .xls (97-2003 format).
Private Sub Workbook_Open()
Range("A1").Value = Range("A1").Value + 1
End Sub
You can modify the cell to whatever you need of course. Note you have to have macros enabled or at least set it to prompt for macros in the Security settings. This will increment the cell's value by 1 whenever you open the workbook. In Excel 2007, you will have to save the workbook as .xlsm or as a .xls (97-2003 format).
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$
I figured there would be some VB involved. Cheers, mate!
You know, this actually doesn't work, even with macros enabled. I'll have to send someone the form I'm using and see if they can get it working.
I tested it to verify, and it was working. You have to save it to actually have it increment of course, otherwise the value reverts.