Set up an Ordering facility for a Buying Dept

[Stuart]

Would appreciate general guidance/comment for the following
possibility, please:

I've been asked if I could set up a facility to 'standardise' the
way in which users on a small peer to peer network place
their purchase orders......currently each user has developed
their own, individual system!

If this hasn't been done before, then my initial thoughts are as
follows:

1. Create the Master purchase order in an excel sheet.
2. Save the workbook as a Template.
3. One Cell (say "A5") would contain the Primary purchase no.
4. Each time a copy of the sheet is opened, Workbook/Worksheet
Change Event code would automatically add '1' to cell "A5".
5. Users would enter job-specific purchase order numbers in "A6"
such that a typical entry might be: 00101/ 55321
"A5" "A6"
6. Users would access the Order form via a menu item, and when
the Order form is displayed, there would need to be a 'save/
cancel' button. If cancel, then reset the number in the Template.
If save, then allow user to browse to a folder of their choice
and save, but also save to a default folder (so that a record of
all purchase orders is maintained).

Is this feasible? Is it already available somewhere?
Any help/thoughts much appreciated.

Regards.

 

[Guest]

Its all certainly possible and shouldn't be to hard to do providing you have some understanding of VBA and Excel.

I just have two questions
1) Do you need to search through purchase orders for any reason? - sum up the values or anything like that.
2) With the saving to a default folder would it be better to just save all the data into one master spreadsheet instead of having multiple spreadsheets all copies of purchase orders - maybe I'm not being all that clear - I'm just wondering if a record could be kept as just another row in a spreadsheet rather then a total new spreadsheet

 

[Stuart]

Thanks for the reply, and yes I have 'some' understanding of
VBA for Excel. Incidentally, it will be developed with XL2000
and all users have 2000.

One earlier typo......meant Workbook.Open/Sheet.Activate Event
not Change Event (though maybe even the Change Event could be
made to work).

This is for the company I work for, so users are my co-workers.

Your questions:

1) We have 2 types of users......type 1 will be creating the Orders
and are therefore computer-literate. Type 2 would probably
struggle to simply Log On (g). On the other hand, type 2 users
just happen to be the people who pay the salaries so.....

For now, I was thinking to test the user's new order in various
ways, and if it complies allow the user to browse to a folder
and save (whilst behind the scene also saving to a central folder).
In addition, to force a Print, so that the user will/should file the
hard copy to a central 'paper' file.......for the use of a type 2
user.

2) The Default folder might ideally be constructed as follows:
in a simple case, with limited orders....

Sheet1: records arranged sequentially according to Purchase
Order number, with limited additional data.
Sheet 2: this would be named with the job/project number, and
would hold FULL copies of the order details.
Records would be sequential according to the Purchase
number.
Sheet 3: would be a different job/project number....etc

ie pseudo code:
If Sheet("jobnumber") Exists Then
Copy record 'to next available row
Else Create It
& Copy record
End If

Hope this has clarified, rather than confused the issue.

Ragards and trhanks.

Its all certainly possible and shouldn't be to hard to do providing you

have some understanding of VBA and Excel.

I just have two questions
1) Do you need to search through purchase orders for any reason? - sum up

the values or anything like that.

2) With the saving to a default folder would it be better to just save all

the data into one master spreadsheet instead of having multiple spreadsheets
all copies of purchase orders - maybe I'm not being all that clear - I'm
just wondering if a record could be kept as just another row in a
spreadsheet rather then a total new spreadsheet