Macro Enabled Purchase Order Template






It’s been two years since I uploaded my Macro Enabled Purchase Order Template, and it’s been one of the most popular pages & downloads on my site to date…. I’ve recently updated this to fit some new requirements & I thought I’d share this with you all as it’s a bit shinier than before (oooh)

Purchase Requisition Template 63.4 KB

It has the same requirements as before:

  • Unique purchase order number on each request
  • Reduce the number of manual steps required to get approval from line manager
  • Provide centralised logging & archive of purchase orders raised

The updated template now also does the following:

  • Macro added to add & remove rows as required
  • Removed rows are deleted to prevent contribution to the total when non-visible
  • Protects cell formatting against pasted formatting
  • Conditional formatting to make the form look a bit neater
  • Only emails the range as it displays within Excel, not the entire worksheet

Important Steps:

  • You will need to add a reference to the Microsoft Office Object Library from Excel
  • Macros must be enabled (make the XLSX file a trusted document)
  • You must have Outlook & a valid MAPI profile for Outlook to be called
  • The email macro does not look up against exchange – The names you use must match the display name or exchange alias of the users as they appear in your GAL. As long as the name matches, Outlook will add the address and the mail will send. You could also use email addresses instead of names
  • You must update the paths to Purchase Order Log and the Archive PDF output, relative to your system. If you try and use the root of C:\ or any protected system folders the code will not run
  • The worksheet is protected except for editable cells, but there is no password. If unprotected, the worksheet will re-protect itself during various actions performed by the user

The Process Explained:

  • The user opens Purchase Requisition Form.xlsx. The PO number automatically increments by one & the file saves
  • User enters the details as required to the form. Once completed, the user his the Generate button
  • Email is generated & a PDF copy attached. Email is addressed To: {Approval field}; Cc: {Requested By field}
  • If there is a value inserted to the Service Request field, then our servicedesk@company.com address is CC’d to the email, and the SR# is added to the subject line for email integration
  • The PDF is written to disk, using the variables from the spreadsheet & today’s date/time for the filename to ensure no accidental file overwrites
  • The summary on the ‘Data’ worksheet is copied & pasted to the Purchase Order Log.xlsx sheet. You can hide the ‘Data’ worksheet once you’ve finished modifying anything
  • The visible portion of the purchase order is copied as a range & pasted to the email ready to send
  • User closes the workbook without saving changes (leaving the blank template ready for other staff)
  • In order to raise two consecutive PO’s, simply hit the ‘Reset Fields’ button to wipe input data, and increment to the next PO number

The Code:

I have added comments throughout the code which will assist you in editing this to fit your requirements. There code is broken down on the prior version of this post here, it’s not changed enough to warrant me reposting the same  information. To edit the VBA, press ALT+F11 in Excel. The different parts of the code can be found in the following locations:

Microsoft Excel Objects > ThisWorkbook
Autorun macros

Modules > Module1
Required fields validation
Create email
Save archive PDF
Update log

Modules > Module2
Reset form button

Modules > Module 4:
Add Row / Delete Row

The empty form:

PReq1a

Once completed, the generated email:

PReq2

 

 

 

 





This entry was posted in Macro, MS Excel, VBA. Bookmark the permalink.

2 Responses to "Macro Enabled Purchase Order Template"

Leave a Reply