Re: auto gererated number when sheet opened

[Manq5230]

Re: auto gererated number when sheet opened

I have a form that I wish to make into a template for our customers to fill in. I would like to have the "Purchase Order" field I've created to have a number automatically fill in with a new number everytime they open the template. Then they would do a save as and that number would stay with the saved file. Then when they open the template again it would then generate the next number in succession. Can this be done.

Thanks
Anthony

 

[LeeLee22]

Re: auto gererated number when sheet opened

Thanks to both of you, however I guess I don't have enough experience on either of these tips. If either of the two people could give me help on where to enter these macros or formulas I would apprciate it.

hi there
i have made up a Job Card in Excel and need it to do exactly what you were asking in this post, and I would have NO idea where (or how) to put this code in. Pls help... how on earth can I get automatic numbering on my job cards (my numbering is in cell F1)
PLS PLS help.

thank you!

 

[Andrew Poulsom]

Re: auto gererated number when sheet opened

In your template right click the XL icon to the left of File on the menu and choose View Code. Paste the code into the window on the right. You will need to save, close and reopen the workbook (with macros enabled) for the code to take effect .

 

[LeeLee22]

Re: auto gererated number when sheet opened

Oh my word, this works perfectly.... however my current job card number is 3706, so i need it to follow on from that number, and also the cell my number is in is cell F1, not A1.
not to sure how to ammend that code accordingly.

Thank you, thank you!!

 

[Andrew Poulsom]

Re: auto gererated number when sheet opened

The last number is stored in the file Number.Txt in the same folder as the workbook. You can open it in Notepad and change it.

Change this line:

Range("A1").Value = x

to:

Range("F1").Value = x

 

[LeeLee22]

Re: auto gererated number when sheet opened

wow, thanks andrew that does work! only one snag though,
when i open the job card, the new number is generated then i type all my info in and press save as say '3701', and close my template.
if i want to view that previous job card and i open say '3701', it automatically changes to the next number. how can you stop the number generating once the file is saved. I only need it to change when i open the main file, lets call it
0001 JOB CARD.

also, how can i change the code to save it to my current number sequences, ie: 3706?

 

[Andrew Poulsom]

Re: auto gererated number when sheet opened

In your template leave F1 blank and try changing the code to:

Private Sub Workbook_Open()
    Dim FName As String
    Dim FNo As String
    Dim x As Long
    If Not IsEmpty(Range("F1")) Then Exit Sub
    FName = ThisWorkbook.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("F1").Value = x
    Close #FNo
    FNo = FreeFile
    Open FName For Output As #FNo
    Write #1, x
    Close #FNo
End Sub

 

[LeeLee22]

Re: auto gererated number when sheet opened

thank you 1000 times over!!!

just need one last thing - how can i start my numbering sequence to '3706' which is where i currently am??

 

[Andrew Poulsom]

Re: auto gererated number when sheet opened

Change the number in the text file, like I said in post #14.

 

[LeeLee22]

Re: auto gererated number when sheet opened

Thank you very very much, i found it and changed it and now it works exactly as i had hoped!!!
much appreciated.

 

[franseen]

Re: auto gererated number when sheet opened

Hi guys, iv managed to get this to work in my target cell, but it only runs when i go into the source code and hit F5 otherwise nothing changes everytime i open the spreadsheet. also is there anyway you can get it to view
a larger number?? e.g 000001, etc
cheers