|
|
|
|
|
|
|
|
|
Excel
|
Autonumber Cell Value Increase by One on Open
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2000
|
|
Submitted by:
|
lucas
|
|
Description:
|
Each time the file is opened, the designated cell value is increased by one, whether you save the file or not.
|
|
Discussion:
|
if the Excel file is classified as "Read Only" the counter will still work! The other thing about it that's neat is that it saves a sequential text file to a local user directory. This allows a network administrator to keep track of how many times individuals open a single file, adding up all the individual numbers of course gives the total file usage for the network.
Uses a textfile to hold the value. Location for the textfile can be changed in the code.
(Some code by Aaron Blood at www.XL-Logic.com formed the basis for this code)
|
|
Code:
|
instructions for use
|
Option Explicit
Private Sub Workbook_Open()
Dim x As String
On Error GoTo ErrorHandler
One:
Open "c:\" & ThisWorkbook.Name & _
" Counter.txt" For Input As #1
Input #1, x
Close #1
x = x + 1
Two:
Sheets(1).Range("A1").Value = x
Open "c:\" & ThisWorkbook.Name & _
" Counter.txt" For Output As #1
Write #1, x
Close #1
Exit Sub
ErrorHandler:
Select Case Err.Number
Case 53
NumberRequired:
x = InputBox("Enter a Number greater than " & _
"zero to Begin Counting With", _
"Create 'C:\" & ThisWorkbook.Name & _
" Counter.txt' File")
If Not IsNumeric(x) Then GoTo NumberRequired
If x <= 0 Then GoTo NumberRequired
Resume Two
Case Else
Resume Next
End Select
End Sub
|
|
How to use:
|
- Open an Excel workbook
- Select Tools/Macro/Visual Basic Editor
- In the VBE window, select Tools/Project Explorer
- Select the ThisWorkbook module
- Copy and paste the code into this module
- Edit the location of where you want the Counter text file to reside or use the default.
- Now select File/Close and Return To Microsoft Excel
- Dont forget to save your changes...
|
|
Test the code:
|
- Open the workbook, enter a number in the input box for the start of the count
- Close and re-open the workbook, the number you gave will be incremented by one.
- (It automatically increments by 1 each time it is opened now, whether you save or not).
- You can also change (or even omit) the cell where the value is reflected in the code. (It is set to be displayed in cell A1 right now).
- Look in your C drive for the text file 'count_seq_file.xls Counter' and open it to view the count.
- NOTE: The code creates the text file 'count_seq_file.xls Counter'
- You can edit the textfile at any time to change the count.
|
|
Sample File:
|
count_seq_file.zip 8.17KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 198 times.
|
|
|