The Option B Excel PRL eForm program is an application designed to assist you in electronically recording Pesticide Reporting Law (PRL) information and reporting it to the NYS Department of Environmental Conservation. Each time you run the program you are asked to enter or verify some basic contact information. You can then view and fill out electronic forms that are similar to the four paper forms used to report pesticide usage and sale.
Option B runs under Microsoft Windows® and requires Microsoft Excel®.
Download the most recent version of the Option B installation package, OptionB.msi
from http://www.nysprl.com/ClientSoftware.htm.
You may be able to run the installation program directly from the web site, but
we recommend saving it somewhere on your computer and running it from there.
We also recommend uninstalling previous versions of Option B before installing the new version. Uninstalling the old version is not usually required but can help avoid confusion caused by having more than one version of Option B installed at the same time. Uninstalling the Option B program will not affect any saved data files. Recent versions of Option B include an “Uninstall” shortcut in the All Programs/Pesticide Reporting group, but for older versions you will need to open the Add/Remove Programs dialog from the Control Panel and remove the old Option B version from there.
To install Option B, run the installation package OptionB.msi that
you downloaded from the NYSPRL web site.
Windows 2000/Windows XP Operating Systems: You must log on as an administrator or to an account with administrator rights to run the installation package.
Vista/Windows 7 Operating Systems: Depending on how your computer has been set up, you may need to log on as an administrator or provide user name and password for an account with administrator rights in order to run the installation package. It is generally advisable to log on as administrator but may not be required.
The installation package has several steps. The program files are normally installed
in the folder C:\Program Files\NYSPRL, but you can select a different
location if you wish. In addition, the installation package sets the “Report
Root” to C:\ by default, but you can select a different location
if you wish. We recommend that you keep the default locations unless there is a
compelling reason to change them. (For example, some users in tightly controlled
networks do not have access to the C:\ drive and need to use a different
folder for program files and data files.)
When installation finishes, you should see a “PRL Option B” shortcut on the desktop and a “Pesticide Reporting” group under “All Programs”; the Pesticide Reporting group contains shortcuts to run or uninstall the PRL Option B program.
An Excel workbook is installed in the program folder for each of the PRL forms. You will be opening one of these workbooks from the Option B Registration Form when you choose to open a Blank Form. After you enter data, you will save the modified form (no longer blank) to the current report folder.
The Excel workbooks rely on VBA Macros to help you complete data entry tasks. In order to get full capabilities from the Excel workbooks, you must enable macros in Excel. See “How to Change the Macro Security Setting in Excel” for more details.
Start the Option B program in any of several ways:
PRL_OptionB.exe. The program was installed by default
to C:\Program Files\NYSPRL, but you may have changed this setting during
installation to another folder.A message box saying something like “Data directory created called 'C:\PRL2011'.” may appear. This is normal. The data directory is the location to which your data files will be written.
When you start the program, the “Registration” form opens. In this form you are asked to provide contact and identity information. The Registration form also gives you access to PRL Report forms, program documentation, and a variety of other tools or operations that may be useful when preparing your reports.
Near the top of the Registration form, you will see the current Report Year. This
is the year for which you are reporting pesticide applications or sales. If the
year shown is not the year for which you are recording pesticide applications
or sales, use Change Year to change the Report Year.
Near the top right corner of the Registration form, you will see the “Current Folder for Report Files”. This location is the PRLyyyy subfolder (where “yyyy” is the Report Year) of the Report Root. By default, this location is C:\PRLyyyy. If you need to store report files in a different location, change the Report Root. (The subfolder will still by called PRLyyyy, though.)
There are nine text boxes on the Registration form where you should enter contact and identity information. Note: You must enter either Certification ID or Commercial Permit Number or both.
Business Name: The name of the business or agency for which you are reporting, if any. Required if you enter a Business Registration Number or Commercial Permit Number, otherwise optional.
Business Registration Number: If you are reporting for a registered pesticide business or agency, enter the Business Registration Number assigned to the business by the NYSDEC. Business Registration Numbers are five numeric digits. (If your business has more than one business registration number, enter the one for the main business office. You may — but are not required to — submit the other business registration numbers using a Form 26A.)
Certification ID: The Certification ID is issued by the NYSDEC to a Certified Commercial Applicator, Technician, or Aquatic Anti-Fouling Paint Applicator. (Private Applicators are also issued Certification IDs but are not required to submit PRL Reports.) The Certification ID begins with “C”, “T”, or “B”, followed by seven numeric digits. Required to open a Form 26 or Form 26A. If you enter a Certification ID here, you must also enter the applicator’s name and your report must include either a Form 26A or Form 26 or both. A Form 26 is required if the applicator whose ID is shown here or any of the applicators on the Form 26A performed pesticide applications during the report year. (If none of the applicators employed by your business or agency performed reportable pesticide applications during the report year, a Form 26A listing all of the applicators is sufficient.)
Commercial Permit Number: Enter the Commercial Sales Permit Number for your business in this box. Commercial Sales Permit numbers are issued by the NYSDEC; they consist of an “R” followed by five numeric digits. A Commercial Permittee must include a Form 25 or Form 27 or both as part of the PRL report. Note: Commercial Sales Permits are issued to businesses that sell pesticides either for resale or end-use in New York State. They are relatively rare — most pesticide businesses do not have a Commercial Sales Permit. If your business does not have a Commercial Sales Permit, do not enter anything in this box! If your business has more than one Commercial Sales Permit, you may enter one number here and provide separate Form 25 or Form 27 for each of the Commercial Sales Permit numbers. However, we recommend that you send separate PRL submissions for each Commercial Permit.
Applicator Name: Enter the name of the applicator whose Certification ID was entered above, if any. Required if a Certification ID has been entered in the Certification ID box.
Contact Person: You must provide a Name, Phone Number, and Email Address for a contact person. This is the person that the NYSDEC or Pesticide Reporting Service Bureau will contact if there are any problems with or questions about your report. Emails acknowledging receipt of your report and confirming that your report has been successfully processed will be sent to the email address you provide here.
After you enter the required contact and identity information, select one of the four PRL report forms by picking one of the “radio buttons” in the “Select a form to open” box. The available forms are:
Select the “Blank Form” button to open a new PRL form of the selected form type, or select the “Existing Form” button to open a previously saved PRL form. The selected form will open in Microsoft Excel.
Each time you open a new, blank PRL report form, the appropriate identity information will be copied into the header area of the new form automatically. In many cases, the identity information copied to the blank form can be edited there. Editing that information is rarely required, but you might choose to do so. For example, if you want to maintain separate Form 26 files for each applicator employed by your business, you can change the Certification ID and Applicator Name in the Form 26 workbooks.
For details about the user interface for the Option B PRL Form spreadsheets, see PRL Report Forms. Descriptions of the form fields and the data you need to enter into the data-entry grid are in the Guidelines document for each form, available from the “Guidelines” button on the form.
There are several buttons and menu items available on the Registration form that can help you prepare and submit your PRL report. Those additional controls are described here.
Saves the contact and identity information on the Registration form to the
identity.txt file in the Current Folder for Report Files. Contact and identity
information is automatically saved when you exit the program, open a PRL Form, or
start Upload Report, but this menu option lets you save the identity information
whenever you want.
The Report Root is the parent folder for your PRL reports. It contains a folder
for each Report Year; the sub-folders are named PRLyyyy, where “yyyy”
is the report year. For example, if you set the Report Root to C:\PRL
and create reports for 2011 and 2012, the C:\PRL folder will contain two subfolders:
PRL2011 and PRL2012. The initial Report Root was set when you installed Option B;
unless you change the default setting, the installation package set Report Root
to C:\. However, you can change the Report Root at any time.
Why change the Report Root? Most users will not need to change the Report Root from whatever choice they made during installation. However, some users might desire more than one Report Root. For example, if you need to enter PRL reports for more than one business (e.g., if you complete PRL reports for several clients), you might find it convenient to create a separate Report Root for each business. This approach will help you to keep the identity information and report files for each business separate.
When you select the “Change Report Root” command, a standard Windows
dialog opens to let you select the Report Root folder. You can create a new folder
at this time or pick an existing folder. If the PRLyyyy subfolder does not yet exist
for the current report year, it is created. If the identity.txt file
already exists in the Report Year folder, it is opened and the contents loaded into
the contact and information boxes of the Registration form; if the identity.txt
file does not exist, the contact and identity entries from the Registration form
are saved to a new file.
Report files are not automatically moved from the previous Report Folder to the newly selected one.
The Report Year is the year in which the pesticide applications or sales that you
are reported occurred. The current Report Year is prominently displayed near the
top of the Registration form and each PRL data form. The current folder for report
files is the PRLyyyy subfolder of the Report Root, where “yyyy”
is the Report Year.
To create or edit PRL form files for a different Report Year, choose the Change Year button or menu item. You will be prompted for the year to which you want to change. You may not enter a future year.
If the Report Year folder (PRLyyyy) for the new year does not exist, it is created.
If the identity.txt file already exists in the Report Year folder,
it is opened and the contents loaded into the contact and information boxes of the
Registration form; if the identity.txt file does not exist, the contact
and identity entries from the Registration form are saved to a new file.
Report files are not automatically moved from the previous Report Folder to the newly selected one.
The Product List is a list of pesticide products (EPA Registration Numbers and Product Names) that you use frequently when preparing PRL reports. The Product List makes it easier for you to enter data into PRL Form 25, 26, and 27 by letting you enter product information once and then selecting a product from the list. This command opens a dialog box with which you can create or edit your Product List.
A Product Selector is shown on Forms 25, 26, and 27 as a convenient way for repeatedly entering EPA numbers and product names into those forms. You can also open the Edit Product List dialog from those PRL data entry forms.
Start the Upload process. See How to Submit Your PRL Report to NYSDEC for details.
The Help menu on the Registration form gives you access to several documents.
Exits the Option B program.
If there is no missing or invalid contact or identity information, the entries are saved to the current Report Folder. If contact or identity information is missing or invalid, you will be given a choice to correct the deficiencies or exit without saving.
IMPORTANT: The Excel eForms rely on the use of VBA Macros.
ALWAYS enable Macros when using this software. If you do not enable Macros,
many of the features in the spreadsheets will not operate or will not function correctly.
Instructions for enabling Macros in several versions of Excel are available from
the Help – Macro Security Setting menu item on the Registration
form. Depending on your macro security setting and version of Excel, Excel may ask
if you want to enable Macros when you open these forms. If Macros have not been
enabled, the spreadsheets should show a prominent warning in the header area. (Note:
the “Macros Disabled” warning may be briefly visible when you open a
form and while you are saving a form; this is normal.)
You will use Form 26 (for pesticide applications) or Forms 25 and 27 (for Sales) to report pesticide-related activity. On each of those forms, you can indicate that there was no reportable activity for the report year.
Form 26 — If the applicator for whom you are reporting made no pesticide application during the report year, check the box labelled “No Commercial Application Were Conducted This Year”.
Form 26A — For each applicator on your list of applicators, you should indicate whether or not that applicator made pesticide applications. If any of the applicators made applications, you must include at least one Form 26 to report the applications made. If none of the applicators on whose behalf you are reporting made applications, you do not need to include a Form 26 “No Applications” report.
Form 25 — There are three “radio buttons” near the top of the data entry Form 25. Two are used to distinguish two types of sales reports. Form 25 is used to report either Sales to another Commercial Permittee for Resale or Sales to commercial applicators for End Use; a separate report form should be used for both reports. If your company made no sales of either type, select the “No Sales” radio button. If your company made sales of one type but not the other (for example, if your company made sales for resale but not for end use), fill in a report form for the type of sales you made — you do not need to complete a report form for the type of sales you did not make.
Form 27 — if your company made no sales of restricted use pesticides or general use agricultural pesticides to certified private applicators in New York State during the report year, check the “No Sales” box.
PRL Report data are entered in the spreadsheet below the header area. Please read and follow the NYSDEC instructions for the specific eForm(s) that applies to your pesticide activity. These instructions explain the requirements for proper completion of each type of PRL eForm. Click the “Guidelines” button on the report form to view these documents at any time. Please Note: ditto marks or use of the words "ditto", "same", or similar entries are not acceptable.
The header area is “frozen” — i.e., it will remain visible at the top of the spreadsheet as you scroll down through the data entry rows. Please ensure that you have scrolled up to the rows that contain data if you are editing an existing workbook.
For your convenience, the column headers show a “tooltip” describing the contents of that column if you hover over the header cell with your mouse. In addition, most of the data entry cells show a descriptive prompt when the cell is selected.
You can use standard Excel methods for moving around the spreadsheet from one cell to another. Note: Excel has a few surprising behaviors if you use arrow keys or Tab to move more than two rows past the part of the spreadsheet containing data. In particular, the input focus will automatically move back to the top of the worksheet instead of to the next row.
Many of the standard editing features of an Excel spreadsheet are available for
your use during data entry. For example, Copy, Cut,
Paste, and Clear Contents can be used to edit individual cells.
There are also buttons labelled “Copy Row” and “Delete Selected
Row(s)”. To use the Copy Row feature, position the mouse on the row you wish
to copy and click the Copy Row button. The row is copied to the first empty row
in the spreadsheet. To use the Delete Selected Row(s) button, select any cells in
the row or rows that you want to delete. Then click the button.
Values entered into cells are checked against a simple set of validation rules. If you make an improper entry on the report forms, you will be given an error message, the background color of the cell will change, and a warning message “One or more cells contain invalid entries” will appear in the header area of the form.
The EPA Reg Number and Product Name columns can be filled in by selecting a product in the EPA Reg Number List (or Product Selector). See Product Selector for more details.
The Units column on forms 25, 26 and 27 has a drop-down list for easy entry of units. When a cell in the Units column has the input focus, a button with down-pointing arrowhead appears in the cell. If you click on the arrowhead (or press Alt-DownArrow), a list of acceptable choices drops down.
Date values must be entered in one of these formats: MM/DD/YYYY, MM-DD-YYYY, MM-DD or MM/DD where MM=month, DD=day, YYYY=year (two-digit years are acceptable). If the year is omitted, the application will use the Report Year.
Forms 26 and 27 have a column for County Code. A list of county codes is included in the Guidelines document for each form. As an added convenience, a popup dialog is available to help you select the correct county code. The county code pop-up has three different modes. The county code pop-up mode can be set by either clicking the yellow button at the top of the grid or (if the popup is enabled) by clicking in the county column. The button also indicates the current status of the pop-up.
Forms 25, 26, and 27 all have an “EPA Reg. Number List” in the upper right. If you click on a product in that list, the EPA registration number and product name are written to the corresponding columns in the current row of the data entry grid. The list of products only appears on the spreadsheet if you have added one or more products to the list. You can edit the list of products by selecting the “Edit EPA List” button at the top of the list.
You can save the workbook as you would a standard Excel workbook. However, if the workbook has not been previously saved, the program will automatically suggest a name for the file based on the form type and identity information in the form header. It will also suggest saving the workbook in the current Report Folder. When you save the workbook:
Save will overwrite the existing
file.Please Note: If you choose to save the same data several times to different files — for example, if you save the file, edit it by adding more data, then save it again with a different name — please submit only the final version with your report. Avoid submitting duplicate pesticide application or sales records.
When you close an Option B workbook, you will be given an opportunity to save it.
You can print out an Option B workbook using standard Excel print commands. However, any printout is for your own records only. Please do not submit the printed copy as your PRL report.
Three buttons on each Option B workbook provide access to documentation files:
The Edit Product List dialog provides a user interface for modifying the list of products that appears in the Product Selector on Forms 25, 26, and 27. The dialog displays a list of EPA Registration Numbers and Product Names. There are seven buttons on the form:
Clear by
Reloading.We have tried to make these electronic PRL Form templates as easy to use as possible.
If you encounter problems installing or using them, please contact the Pesticide
Reporting Service Bureau:
By Phone: 800-281-7538 (toll-free in New York State) or 607-257-5707 (a toll call
outside of New York State)
By Email: prlsupport@nysprl.com
Or visit the website: http://www.nysprl.com/
The site's Frequently Asked Questions section may be useful:
http://www.nysprl.com/nysprl_faq.htm.
If you have questions regarding the Pesticide Reporting Law and reporting requirements,
contact the New York State Department of Environmental Conservation, Pesticide Reporting
and Certification Section:
By Phone: 518-402-8748
By Email: prl@gw.dec.state.ny.us
Or visit the website:http://www.dec.ny.gov/chemical/27506.html.
By default, the name given to a new file follows the naming convention described below.
The Form Prefix is a single letter associated with each PRL
Report Form type. The first letter of the file name must be the
correct Form Prefix for that form type, as shown below:
The next 5 to 8 characters of the generated file name are ID numbers which represent either your Registered Business number, Certification ID Number, or Commercial Sales Permit Number. Which ID number is used depends on the form; Forms 25 and 27 use the Commercial Permit Number, Form 26 uses the Certification ID, and Form 26A uses either the Business Registration Number (if any) or Certification ID.
The next two digits are an (optional) unique sequence number between 00 and 99.
The last part is the extension of ".xls", which indicates an Excel workbook.
The pattern used for the file names is Form Prefix + ID number + sequence number + extension. For example, a Form 26 for an applicator with ID number C1234567 is given the default name of PC123456700.xls.
Some other examples: PC555555501.xls, SR3333301.xls, MR3333302.xls
While using these templates, the file names will be generated automatically for you. If you choose to change the file names and do not follow the above guidelines, your electronic documents may not be acceptable by NYSDEC and may be returned.
If you need to change the file name, we recommend that only the sequence number of 00 be changed. Using our example, PC123456700.xls might be changed to PC123456701.xls. NEVER change the Form Prefix.
Microsoft, Windows, and Excel are registered trademarks of Microsoft Corporation in the United States and/or other countries.