I have an Excel 2007 spreadsheet that has an index number in cell AJ1. At
this time, the number is "40606" Each time the spreadsheet is opened, I
need the number to increase by 1. So that the next time the sheet is opened,
the index number would be "40607" and so on. Any help would be greatly
appreciated.
--
Lineman

If a user opens the workbook and closes without saving, does the cell get
incremented?

If yes, you'll want to save as soon as the cell is updated.

This code (with hardly any validity checks) would go in a General module:

Option Explicit
Sub auto_open()
Dim myCell As Range
With ThisWorkbook
Set myCell = .Worksheets("Sheetnamehere!").Range("AJ1")
End With

With myCell
If IsNumeric(.Value) Then
.Value = .Value + 1
Else
'what should happen? reset the counter?
.Value = 1
End If
End With

ThisWorkbook.Save 'save right away????

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Lineman116 wrote:

I have an Excel 2007 spreadsheet that has an index number in cell AJ1. At
this time, the number is "40606" Each time the spreadsheet is opened, I
need the number to increase by 1. So that the next time the sheet is opened,
the index number would be "40607" and so on. Any help would be greatly
appreciated.
--
Lineman

--

Dave Peterson

Additional info that should have been included in my first posting. My bad.
Following is the macro that was used in Excel 2003. It does not work after
being copied into the 2007 Excel file. I changed the specified cell
information as the new (2007) sheet changed.

Sub Workbook_Open()
Sheets("Sheet1").Range("K1") = _
Sheets("Sheet1").Range("K1").Value + 1
End Sub

Would it be a version conflict that is causing this macro to not work in
Excel 2007?
--
Lineman


"Lineman116" wrote:

I have an Excel 2007 spreadsheet that has an index number in cell AJ1. At
this time, the number is "40606" Each time the spreadsheet is opened, I
need the number to increase by 1. So that the next time the sheet is opened,
the index number would be "40607" and so on. Any help would be greatly
appreciated.
--
Lineman