Add an Autonumber to a column, for use as a sequential Purchase Order Number

**oracle741** · 10-17-2013, 10:37 AM

I am a novice to VBA and am not sure how to code this:

When a new entry is made in the Job# column, a sequential autonumber is generated and placed into the PO Number field. The number format would be 001-0000 (where the first 3 prefix digits are set, and do not change, but the last 4, displaying leading zeros increment from entry to entry).

EPO2.1.xlsm

**jaslake** · 10-23-2013, 10:09 PM

Hi oracle741

Place this Code in the This Workbook Module

Please Login or Register  to view this content.

Place this Code in Sheet1 Module (or whatever you named it in the above Code

Please Login or Register  to view this content.

See attached.

**oracle741** · 10-24-2013, 11:02 AM

Hi John,

the code works well in the workbook EPO2.1 on it's own; very nicely, thanks. I have been also been working on other VBA Subs (with the help of code examples from other users) and when I include your code with the current version, it does not preform its operation. I tried some logical troubleshooting, but can't see where the problem is. I did rename the worksheet Sub, but that should not make a difference, right?

I also had two questions; first, is there a way to prevent it from crashing if an attempt is made to enter a job number out of sequence (as in skipping a line). I still would not want them to enter that data, but the Sub crashes if it is tried. Second, is there a way to 'call' the number prefix used from another sheet?

Thanks,
Ken

EPO1.1 (build).xlsm

**jaslake** · 10-24-2013, 11:44 AM

Hi Ken

Yes, doing this does make a difference...I'd ask why did you change it...

I did rename the worksheet Sub, but that should not make a difference

Yes this can be done in the Code

an attempt is made to enter a job number out of sequence (as in skipping a line)

Yes this can be done...where will it be stored?

is there a way to 'call' the number prefix used from another sheet

**oracle741** · 10-24-2013, 12:17 PM

Yes, doing this does make a difference...I'd ask why did you change it...

I changed it from Private Sub Worksheet_Change to Private Sub Worksheet_Enter; I did this because there is another Subroutine named Worksheet_Change.

Yes this can be done...where will it be stored?

It would draw from column 1 on sheet 3; columns 2 and 3 would contain the last name and first name. column 1 would also be formatted in a custom number format to display leading zeros.

I need to develop the list, but haven't been given the names yet. the three digit prefix would be assigned to an individual who has authority to make purchases. That sheet (sheet 3) would be locked and hidden, if that has bearing on the code.

**jaslake** · 10-24-2013, 12:38 PM

Hi Ken

Far as I know there is no

Private Sub Worksheet_Enter

Modify your ChangeEvent Code such as this

Please Login or Register  to view this content.

Dummy up something that simulates this and I'll look at it

Re: Add an Autonumber to a column, for use as a sequential Purchase Order Number
Yes, doing this does make a difference...I'd ask why did you change it...
I changed it from Private Sub Worksheet_Change to Private Sub Worksheet_Enter; I did this because there is another Subroutine named Worksheet_Change.

Yes this can be done...where will it be stored?
It would draw from column 1 on sheet 3; columns 2 and 3 would contain the last name and first name. column 1 would also be formatted in a custom number format to display leading zeros.

I need to develop the list, but haven't been given the names yet. the three digit prefix would be assigned to an individual who has authority to make purchases

**oracle741** · 10-24-2013, 02:26 PM

Thank you John, the code works flawlessly.

Also, TIL: the Sub line in VBA is not just a name, but is calling the type of Subroutine being used, thank you.

I attached the Workbook with the dummy sheet3

**jaslake** · 10-24-2013, 04:43 PM

Hi Ken

I can show you how to use your "User Names" on Sheet 3 on a Local Machine. I suspect you're on a Network and I can't show you how to do that.

In the attached I've set "John" assigned to Prefix "007" and you can se the results

Please Login or Register  to view this content.

**jaslake** · 10-24-2013, 04:44 PM

You're welcome...glad I could help. Thanks for the Rep.

Add an Autonumber to a column, for use as a sequential Purchase Order Number

LinkBack

Thread Tools

Rate This Thread

Display

Add an Autonumber to a column, for use as a sequential Purchase Order Number

Re: Add an Autonumber to a column, for use as a sequential Purchase Order Number

Re: Add an Autonumber to a column, for use as a sequential Purchase Order Number

Re: Add an Autonumber to a column, for use as a sequential Purchase Order Number

Re: Add an Autonumber to a column, for use as a sequential Purchase Order Number

Re: Add an Autonumber to a column, for use as a sequential Purchase Order Number

Re: Add an Autonumber to a column, for use as a sequential Purchase Order Number

Re: Add an Autonumber to a column, for use as a sequential Purchase Order Number

Re: Add an Autonumber to a column, for use as a sequential Purchase Order Number

Thread Information

Users Browsing this Thread

Similar Threads

Macro Reqd to update Purchase order log, everytime a new purchase order is made

Sequential Purchase Order No and File As

purchase order list & order number generator

Populate column in sequential order by number of records

purchase order counter in excel purchase order template

Bookmarks

Bookmarks

Posting Permissions