Autogenerate purchase order number and input to Userform.

[scrawl.to.me]

I have a worksheet for creating and storing purchase orders. I have several different sheets and the main 'front page' sheet with command buttons. The command buttons are linked with userforms that are working perfectly, and inputting data into the appropriate sheet where and when I want.
However, on the worksheet that stores all the purchase orders, it is setup on some sort of Excel table. So if I'm manually putting in data, I just tab over the edge of the table and it automatically starts a new row and I have arranged for it to auto generate the next number.
What I want to do it:
When I click on the command button to create new purchase order, I would like the userform to go to the sheet where the purchase orders are recorded and find the next purchase order number. Therefore, going to the sheet ("Purchase Orders") and finding the last line of data +1. Then finding the auto-generated next invoice number from column A.
I'm not sure if it's possible, as the next invoice number is not actually generated yet, so the code may have to include allowing the table on sheet "Purchase Orders" to generate the number, then place it at the top of the userform.
I don't know if I'm making sense - hopefully. I've been working on this for hours! TIA.

[gmr4evr1]

Can you attach a sample of what you have and what you want...include any code and UserForms.

[scrawl.to.me]

Hi gmr4evr1, Thank you so much for posting. I'll do my best, as I'm not sure exactly how to show you all the info, but I'll give it a go.

So the 'front page' with command buttons' code looks like this:

Please Login or Register  to view this content.

OK. The code for the userform is:

Please Login or Register  to view this content.

Purchase Orders Table View.PNG

I hope that worked. We'll see.

[gmr4evr1]

To make the code easier to read, please click Edit Post, highlight the code then click the # sign.
A lot of members, including myself, are unable to see images on here.
Without seeing how the workbook and userform(s) are setup it would be very difficult to try to figure out a way to assist you.

[scrawl.to.me]

So what I want is on the table (as shown in image) to autogenerate the next invoice number. So in this second image, I have 'tabbed' all the way past Row I, which then starts a new line and automatically put the next purchase order number there (POER1004).

table.PNG

So I want to write into the userform or into the command button (AddNewPO) code so that the next PO number will automatically go into the top text box of the user form (pic attached).

userform.PNG

Hopefully, I'm making some sense..

[scrawl.to.me]

Oops, only just saw this. I will edit the post now.

[scrawl.to.me]

Is that code helpful? Do I need to attach something else? As you said photos are no good..

[gmr4evr1]

Much better with the code. One of the forum rules is to post code in that manner.
Yes, if you could attach a file with the code and userform(s) in it that would help a lot. If we have a sample to work with, it makes it easier for us to not only see what you are working with, but allows us step through the code to see what it is doing, which will also help us to meet your requirements better.

[scrawl.to.me]

Sorry to be a pain, how do I attach the sample?

[scrawl.to.me]

I think I have attached the excel sheet here.

[gmr4evr1]

I've been trying a few things but so far non have worked. I was thinking using LastRow would do the trick but it's not. Here is what I was trying on the add new PO button

Please Login or Register  to view this content.

But, all it does is put a 1 in the txtPONumber textbox. It's not pulling the value or text from the last row in column A of the Purchase Orders sheet.

[scrawl.to.me]

Hi gmr4evr1. Thank you so much for trying. It is a doozy, for sure. I was really hopeful it could be done. I'll keep trying and let you know if I come up with anything. Thank you.

[bakerman2]

Please Login or Register  to view this content.

[gmr4evr1]

bakerman2 to the rescue again!!!
I tried 3 different ways of doing this and the code you provided was similar to the 2nd one I tried, but I could get the Right(.Range("A" & .Rows.Count).End(xlUp).Value, 4) + 1 part of it correct. After seeing your code, I now know why. I was placing the 4 in the wrong place. I had it after the "A" instead of Value.

@scrawl.to.me
bakerman2's code works perfectly!

[scrawl.to.me]

Oh, you are the bomb, bakerman2! Thank you both bakerman2 and gmr4evr1!
That is wonderful. I have a number of other functions I want to put in this workbook, but am deciding what next - I'm sure you'll hear from me again! =)

[scrawl.to.me]

Actually, I have another related query, as I thought this would be easy after solving the last problem, but it's not proving as easy as expected..
How do I code it, so that when 'SaveEntry' is clicked it automatically changes Cell E3 on Worksheet 'Front Page' to the number that has just been created (by bakerman2's code!) in the "POER Number" text box in the NewPO userform?

Therefore, if I open the form and it gives me a new POER number of 86 (the "POER Number" text box, can I automatically have it change the selection of E3 on worksheet "Front Page" to POER86? Note that "Front Page" cell "E3" input is based on a list, so I want to choose the last number in the list.

Hope I'm making sense.

[bakerman2]

Please Login or Register  to view this content.

[scrawl.to.me]

Wow! Lifesaver. You have helped immensely. Helped relieve much stress and anxiety! Thank you once again!

[bakerman2]

Helped relieve much stress and anxiety

What a visit to a XL-forum can do.
You're more then welcome.

[scrawl.to.me]

Thank you. I'm sure you'll be hearing from me again. I have BIG plans for this worksheet!