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.
"FSt1" wrote:
> 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" wrote:
>
> > 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!
|
Similar Threads
