Excel AutoNumber Files

[johnske]

Hi Anne,

You mean something simple like this? Or something more?

Option Explicit
Private Sub Workbook_Open()
With Sheets("Sheet1")
    [A1] = [A1] + 1
End With
End Sub

[Anne Troy]

No...we'd need it to keep track, like invoice numbers.

I think there should be an entry each for single users and multiple users.

[johnske]

No...we'd need it to keep track, like invoice numbers.

I think there should be an entry each for single users and multiple users.

So a single workbook is going to be shared? How are you to determine who's using it - password protection?

PS: Will change my previous code to give protection for the number of times it's opened >>[vba]Option Explicit

Private Sub Workbook_Open()
With Sheets("Sheet1")
    .Unprotect
    Cells.Locked = False
    [A1] = [A1] + 1
    [A1].Locked = True
    .Protect
End With
End Sub

[Anne Troy]

Hi, John. Did you read this whole thread?
I think this is why it never got written up. Everybody is making it more difficult than necessary. I think what we need is this...

1. A place to store the last invoice (auto) number. Perhaps a TXT file that sits on the user's PC or on the server.

2. When we open the Excel file (or XLT?), we increment that number by one, and change it in the TXT, too.

If somebody goofs, well, then they can learn how to edit the TXT file.

Here's a perfect example, and I don't think it even uses a text file, but I think it's also only good for one person (but who cares? it's better than NO kb entry for this, and we can make a separate one for multi-users). Feel free to download it and steal the code. If it's password protected, don't crack it, just ask me for the password.

http://www.theofficeexperts.com/down...ExcelDownloads

[johnske]

Hi Anne,

Part of what I'm trying to find out is: Is this invoice going to be printed (or maybe emailed)? - If so, it'd probably be better to do the count update on the printout command rather than on the Workbook_Open. i.e. the count would only be incremented if the order went ahead to the point of having an invoice printed for it...

I'm assuming you're going to have something like Invoice Number 00123 somewhere and, Served By: Joe Bloggs somewhere else.

If the workbook's unprotected (or even if not) you could have a small userform with command buttons (with your reps. name on them) pop-up on Open, they click their name and it goes on the invoice - with the invoice count for that person and they just enter the details.

All this would not be hard, but at the same time it would take more than two or three lines of code...

Regards,
John

[Anne Troy]

There is no "joe bloggs", John. This has nothing to do with designating a user. I only mentioned multi-users so that more than one person can do invoices.

Why don't we just forget the multi-user thing for now.

I want to create invoices, and have the number increment by one. I don't care HOW that is done.

[johnske]

Hi Anne,

Using your example (in the link) - this increments by one every time an invoice has been printed.

Regards,
John

EDIT: Temp attachment has now been removed (John)
Reason: Read next post + only done for one sheet (didn't realize there was more than one "Invoice" sheet)

[Anne Troy]

Sorry. I prolly wasn't real clear about that. I sold that site to MrExcel, so we can't just go take the whole file. Any way we can clear all the stuff out and just have the top sheet, with no data tracking? Then, just put it in the KB, not here.

LOL. You might wanna remove www.TheOfficeExperts.com from the properties, too. And put YOUR name in there instead of mine.

[johnske]

I only did it as an example. Gotta go out now - later...
John

[Anne Troy]

Oh. Sorry!!

[johnske]

....Any way we can clear all the stuff out and just have the top sheet, with no data tracking? Then, just put it in the KB, not here.

LOL. You might wanna remove www.TheOfficeExperts.com from the properties, too. And put YOUR name in there instead of mine.

Done - with data tracking

[lucas]

While reading this I realized I had seen this question before. I got this from Aaron T. Blood at www.XL-Logic.com a while back. Seems to work. It uses a textfile and it increments each time the file is opened, whether you save it or not.

Sub Auto_open()
On Error GoTo ErrorHandler
One:
Open "c:\Counter.txt" For Input As #1
Input #1, x
Close #1
x = x + 1
Two:
Sheets(1).Range("A1").Value = x
Open "c:\Counter.txt" For Output As #1
Write #1, x
Close #1
Exit Sub
ErrorHandler:
    Select Case Err.Number
    Case 53  'If Counter file does not exist...
        x = InputBox("Enter Number to Begin Counting With", "Create 'Counter.txt' File")
        Resume Two
    Case Else
        Resume Next
    End Select
End Sub

[nourfarsi]

Private Sub Workbook_Open()
 Dim ws As Worksheet
 Set ws = Sheets("sheet1")
 Dim lr As Long
  lr = ws.Range("a65536").End(xlUp).Row
    With Sheets("Sheet1")
    If IsEmpty(.Range("A1")) Then
    .Range("A1").Value = 0
Else:
    ws.Cells(lr + 1, 1) = Val(ws.Cells(lr, 1)) + 1
        End If
    End With