Re: auto gererated number when sheet opened

Manq5230

New Member
Joined
May 19, 2003
Messages
18
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Re: auto gererated number when sheet opened

Either an Auto_Open Macro or an Event with the following line:

ActiveCell.FormulaR1C1 = Range("A2").Value + 1

Where A2 iswhere your counter is..
 
Upvote 0
Re: auto gererated number when sheet opened

Put this in the ThisWorkbook module in the template:

Code:
Private Sub Workbook_Open()
    Dim FName As String
    Dim FNo As String
    Dim x As Long
    FName = ThisWorbook.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
 
Upvote 0
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.
 
Upvote 0
Re: auto gererated number when sheet opened

Hey thanks Andrew after a little work I finally figured out how to implement your code to work. And everything works great thanks again.
 
Upvote 0
Re: auto gererated number when sheet opened

Hi,
I am trying to do exactly the same thing and, like my colleague, an not experienced in VB but trying to apply and understand it.

I am using Excel 2002. I have cell R4CV in my worksheet which I want the auto number to apply. In one of the responses, it suggested the following:

Either an Auto_Open Macro or an Event with the following line:
ActiveCell.FormulaR1C1 = Range("A2").Value + 1
Where A2 iswhere your counter is..


I didn't quite understand this. I have the Complete Excel Reference book but I cannot find any help on Auto_Open Macros. How do I create one?

A seperate suggestion then gave some VB code to copy into ThisWorkbook module, which I have done. At the moment when opening the file I get a de-bug error on the line:

FName = ThisWorbook.Path & Application.PathSeparator & "Number.Txt"

Experience and knowledge are wonderful things, I feel so inadequate not understanding this, but hopefully, you can explain to me and I will feel so good at being to apply this to other future situations. I hope that you can help.

Barrie Bowdler
 
Upvote 0
Re: auto gererated number when sheet opened

Andrew,

You probably missed the TYPO at ThisWorbook.Path.

It should say ThisWorkBook.Path

Larry Dunn
The Typo King
 
Upvote 0
Re: auto gererated number when sheet opened

larrydunn said:
Andrew,

You probably missed the TYPO at ThisWorbook.Path.

It should say ThisWorkBook.Path

Larry Dunn
The Typo King

Thanks Larry. :oops:

I usually spot that sort of thing, but I thought I had copied the code from a working version. Now I remember changing Application.Path to ThisWorkbook.Path after the copy.
 
Upvote 0
Re: auto gererated number when sheet opened

Thank you so much! This was almost perfect for my application. The only change I made was to start by checking to see if the there was anything in the field to begin with. If there was, exit sub, otherwise everytime you open the file, you get a new number.
I put the following line as the second line:

If Range("A1").Value >"" Exit Sub
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top