Auto Number Excel Forms During Save As

[DP]

I viewed a response about auto number roll over in excel from Roger and Paul
B, like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets("Sheet1").Range("A1").Value = _
Sheets("Sheet1").Range("A1").Value + 1
End Sub

This is absolutely great. But, of course, I need to know if there's a way
to include a pop up that e-mails or at least retains certain information
about who did a "save as" on the form and what number it gave them.

I'm working on a purchase order form that is available to multiple
individuals. It needs to assign a new purchase order number (in increments
of 1 - like 001, 002, 003). But, from an administrative standpoint, we need
to know who got the PO number. I'm thinking something like a pop up window
that asks for user name and notes the PO number.

Am I dreaming or is this possible?

Thanks so much for any help I receive!

 

[FSt1]

hi
as an ex-buyer, all our po forms had a place for the buyer to enter buyer's
name.
if your form don't have this, then i think your form is missing something.
if you are doing a "save as" ie saving each po to a seperate file, you will
be creating yourself a data management nightmare. believe me. been there,
done that.
re-e-eal stupid. all info needs to go in a database.
and unless you do a save BEFORE the save as, then your po number wont
increment right.
and how are you naming your files. by PO number? auto or manual? you could
end up getting a message saying that that file already exists, do you want to
overwrite.
you cant have your po numbers start at 00001. the only way to have leading
zeros is to format as text. and you can't add 1 to text. needs to start at
10000 or something you can add 1 to.
I don't like the email thing. it would cause someone to read the email then
log the info into another exell file? too much work. decreases productivity.
needs to be automatic.
my idea.
drop the po number and the user name on a second sheet in the po file.
something like this...
Dim r As Range
Dim ro As Range
Dim un As Range
Set r = Sheets("sheet1").Range("A1")
Set ro = Sheets("Sheet3").Range("A65000").End(xlUp).Offset(1, 0)
Set un = ro.Offset(0, 1)
ro.Value = r.Value
un.Value = Environ("username")
Sheets("Sheet1").Range("A1").Value = _
Sheets("Sheet1").Range("A1").Value + 1

'and maybe add or at least work in somehow.....

Activeworkbook.save
application.dialogs(xldialogsaveas).show

my thoughts
Regards
FSt1

 

[DP]

Thank you for your time in responding.
I do agree with what you're saying. I believe this should be in an database
to start with, but the company is wanting it in Excel.

Understood about the number starting with leading zeros, bad example on my
part.

The PO does have a place for buyer to fill in name.

The situation is putting the form on at Network were multiple users
(restricted users) can access the form when they need to place a new PO, but
each person who saves accesses the form gets a new number.

From an administration standpoint, I thought it would be good to at least
know who got what PO number.

Again, I appreciate your input and time. Am going to try your suggestions.