Creating an Electronic One Stop Dispensing Sheet using Excel

Disclaimer: The information contained on this page forms part of my GPC CPD portfolio and is therefore my personal opinion and in no way reflects the opinion or policy of my employer.


The aim of this learning exercise was to create an electronic one stop order sheet. In most hospitals ward order sheets are written out on the wards by hand before being sent down to a dispensary for dispensing. The handwritten forms are often illegible or missing key data which increases risk of errors and also slows the supply of medication to the patient. My electronic ward sheet is designed to be very quick and easy to fill in using an auto complete feature for the costcentre, drug and directions. It is possible to fill in the entire sheet without using a mouse. By using an automated import of lists of medicines and cost centres from a pharmacy computer system, it only allows users to request medicines that are on the system (and only allows them to be charged to real cost centres).

It is not possible to submit an entry without completing the required fields. I also built in the ability to scan in and scan out hospital numbers to reduce the risk of the wrong patient being selected.The form is designed to be either printed locally or directly to a specified dispensary printer.


  • Excel 2003 or above
  • CODE39 barcode font (code39hr.ttf) installed on each PC that you wish to open the spreadsheet. (Note: This font is copyright so you will need to source it from the copyright holder).
  • A4 printer (or Print to File)
  • This project utilises a small amount of code  from the “barcode fonts and encoders” distribution available on Sourceforge.


  1. Download the Electronic One Stop Dispensing Sheet here (Right Click and “Save as…” )
  2. Open the Excel Spreadsheet
  3. A security warning bar will appear – Click: Options>>Enable this Content>>OK
  4. To add an order,  click “Click here to add order”.
  5. Complete the fields before clicking “Add this Medication”. You can then add additional medicines for the same patient or click “Next Patient” to move on to the next patient.
  6. When you have finished adding orders, simply click on the “close” button on the medication order window.
  7. Click on the “Print” button.
  8. The print area will be shown in print preview. You may need to modify the paper size and orientation settings to suit your requirements.
And that’s it! This is a very simple but effective application for creating and printing of a one stop ward order sheet using a Macro-Enabled Excel Spreadsheet. It could easily be modified to email the completed form to an address.


Click here to add an entry


Medication Order Entry Window


Add medication and continue (clears medicine, directions and quantity fields with the focus returning to the medicine field), or click next patient to clear hospital number, patient name, cost centre, medicine, directions and quantity, focus returns to hospital number).


One of several validation warning dialogue boxes


Barcoded hospital number


Text Shrink to fit feature (Directions)


Building up an order


Ward and operator entry window (these have to be completed before printing)


Ward and operator info below order sheet


Print this sheet and the Printer Set Up Window


Print confirmation dialogue box


Confirm Sheet Clearance