Auto numbering a cell

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 7, 2003

• #2

This will increment the number in cell A1 each time the workbok is opened:

Code:

Private Sub Workbook_Open()
    Dim FName As String
    Dim FNo As String
    Dim x As Long
    FName = Application.Path & Application.PathSeparator & "Number.Txt"
    FNo = FreeFile
    x = 0
    On Error Resume Next
    Open FName For Input As #FNo
    Input #FNo, x
    x = x + 1
'   *** Change range reference to suit ***
    Range("A1").Value = x
    Close #FNo
    FNo = FreeFile
    Open FName For Output As #FNo
    Write #1, x
    Close #FNo
End Sub

Is that what you want?

 

Upvote 0

G

go4broke

New Member

Joined

Jan 6, 2003

Messages

24

• Jan 7, 2003

• #3

The suggestion is if the same file is opened. I am looking for a solution that works with a template where the file will be unique each time, filled in, and saved/submitted for use as if it were a pre-numbered paper form.

Thanks

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 7, 2003

• #4

Save the workbook as a template. Then choose File, New and select the template. Excel will open an instance of the template as a new xls file. The number will be updated.

 

Upvote 0

G

go4broke

New Member

Joined

Jan 6, 2003

Messages

24

• Jan 7, 2003

• #5

This works GREAT!! One additional nuance.

I have an OS SET operator with a user name value. How can I access this to get the first 3 characters and concatenate this with the sequencer.

Thanks for the help!!!

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 7, 2003

• #6

Sorry I don't undertand "I have an OS SET operator with a user name value".

 

Upvote 0

G

go4broke

New Member

Joined

Jan 6, 2003

Messages

24

• Jan 7, 2003

• #7

I was trying to describe an "operating system" set value from the autoexec.bat file.

The value is something like:
set user=SAMPLE

Thanks

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 7, 2003

• #8

You can use the Environ function:

UName = Left(Environ("user"),3)

then

Range("A1").Value = UName & x

If you always wanted the number to be say 5 characters then use:

Range("A1").Value = UName & Format(x,"00000")

 

Upvote 0

G

go4broke

New Member

Joined

Jan 6, 2003

Messages

24

• Jan 7, 2003

• #9

Task completed. Your help is much appreciated!!

I also added my own uCase() function so the naming would be the same, as well as & " - " to the value.

Thanks again!!!!

 

Upvote 0