What I have now is a simple excel spreadsheet to input numbers and info. What I want to do with it is make it a template and have one cell in the top left corner to add a new number in order each time a new user opens it. For example in the cell would be number 123 and the next time when someone opens it, it would be 124. Is that possible? Thanks in advance.
The easiest way to do this is to write a macro.
Go to Tools-Macro-Visual Basic Editor
This will open the VBA Editor. Hopefully you will have a project view in the left hand side of the screen and a blank page on the right. In the Project - VBAProject view, double click on your workbook name, and then double click on ThisWorkbook in the list.
In the editor, write the code:
'------------------------------------------------------
Private Sub Workbook_Open()
Dim xNumber as integer
'Read in the current value
xNumber = ThisWorkbook.Worksheets("NameOfWorksheet").Cells(1,1)
'Add 1 and put the new value in the worksheet
ThisWorkbook.Worksheets("NameOfWorksheet").Cells(1,1).Value = xNumber + 1
'Make sure the change is saved
ThisWorkbook.Save
End Sub
'-------------------------------------------------------
You can pretty much copy and paste this code, the only change you need to make is to put the actual name of the worksheet in between the "NameOfWorksheet". Every line that starts with ' is a comment.
Cheers
I don't spend that much time in Excel macros, but I believe you could put some code in the Open event for the workbook. It might be that you would have to actually fire the incrementer code after the workbook has finished loading. Timing is key.
Private Sub Workbook_Open()
'v = cell such and such value
'v++
end sub
Another method would to use ODBC and VBA to increment a counter in a database (Access for instance). This way, the template would not have to be saved each time it was opened. Also, the template could be opened by multiple users simultaneously.
Discuss This Question: 5  Replies