Excel's Ability to Apply Serial Numbers
Posted by Diana Johnson on November 30, 2001 5:13 PM
Can Excel auto-serialized forms (a Purchase Requisition form)as they are downloaded from a network drive?
The spreadsheet file would be on the network. The user would click on the shortcut and Excel and the Purchase Requisition form would load. At this point the serial number would increment by one so that the user sees the next serial number in sequence on the Purchase Requisition form. The user would complete the form and email it to the purchasing agent. So, each time someone loaded/accessed the form, the next serial number would appear.
There will be numerous users. Each user would have a unique serial number on their purchase requisition (in order to track the requisition). Thanks for your help.
Posted by Damon Ostrander on November 30, 2001 10:21 PM
Hi Diana,
Probably the best way to do this is to use direct file I/O in VBA and keep the serial number in a separate file on the network. This file could contain just the last serial number issued, or could contain additional information, including a complete history of serial numbers and when they were assigned and to whom.
It works like this: when someone opens the network PR form a workbook open event macro runs code that opens the serial number file, reads the last serial number, increments it in the file and writes it to the appropriate place on the PR form, and closes the serial number file so someone else can access it. The serial number file would be locked against simultaneous access from another user for probably under 1 second, so the liklihood of a user getting a "file locked" message because another user is accessing the serial number file is pretty remote unless your organization has hundreds of people writing PRs.
You would want to think also about the possibility of a user getting a serial number assigned, but then aborting the PR form. Should the serial number be reassigned, or should it go to serial number heaven? If reassigned, then the workbook Close event should be used to undo the changes to the serial number file.
Also, for security sake, you should password-protect the code from viewing so that users are not aware that the serial number file is being accessed and what and where it is. This is to prevent mischief that could result from a user deleting or modifying the serial number file.
All of what I have described would be pretty straightforward for an experienced VBA programmer to accomplish--probably just a few hours' work.
I hope this helps.
Damon
Posted by Lewis on December 02, 2001 2:44 AM
Hi,
I have a similar situation.
If the number of people using it is small and therefore it is unlikely two people want to use it at once you can put something like:
Sheets("New Problem").Select
Range("C4").Select
ActiveCell.Value = ActiveCell.Value + 1
to increase the number in cell C4 by 1
My Macro then clears all the other input celss and save the workbook.
Regards
Lewis
