Looking for an expert in Microsoft VBA to review a current project, complete the sections that requires the transfer of data to Word Docs and PDF pages, also provide solutions for the printing and saving of the Workbook.
Also want the code reviewed and estimate for time/cost to update code. This was written by someone without VBA experience.
Scope of work required:
1. Create the VBA code that links and transfers the required information to the Word Doc Template (ProbableCause).
a. Arresting Officer, Agency, Suspect arrested, charges, time and dates.
b. Charges need to be input using a dynamic range so that it minimizes the spaced used.
c. Officer needs to be able to complete his own statement below the charges outlining his reasoning for a lawful arrest and detention.
d. Can the Word Doc be opened automatically when the Tab is clicked?
2. Create the VBA code that links and fills in the required information to the DIC 23/24/25 forms, including the DIC24S (Spanish) and DIC25S (Spanish).
a. Prepare all six forms with links that fill in appropriate officer and suspect information.
b. Can the Doc be filled and opened automatically when the Tab is clicked?
3. Finish/Create Save Tab page where user can save the entire workbook/userform to jump drive.
4. Finish/Create Print page where user can select the number of copies for each document to be printed. See Print Tab for example.
5. Need a way to CLEAR all data inputted into Userforms and WorkSheets back to a default status, ready for the next user.
1. Estimate time to clean up current code.
2. Can Listboxes in Userforms be rewritten to allow items selected or input into listboxes be automatically transferred to their respected WorkSheets and can the Worksheets be automatically updated when changes are made to Listboxes. Want to eliminate CommandButtons that have been created to Confirm/Transfer items to WorkSheets.
Overview of Project
I have created an Excel VBA userform to help collect and input arrest data that is then transferred to a workbook with a variety of worksheets. Once all the information is entered, several of the worksheets are printed and used as official documentation.
The userform is done with the Multipage function and users will start on the left and move their way to the right, clicking on each page and entering the any necessary information. The pages and a brief description are as follows:
Page 1: Logon
New users can register and have their personal information stored for easy logon each time the use the system.
Page 2: Cust & Cont (Custody & Control)
This page contains the information that is entered and then transferred to the Custody Information WorkSheet. One of the main components of this page is the Inmate Charges ListBox. Here, officers will click the “Add New Charge” button and a Userform with charges is opened. Here, officer can select different Charge Databases and then select individual charges, adding or selecting addition information to the charge and then Submit the charge to be transferred to the original Inmate Charge Listbox. Once all charges are enter, the Charges UserForm is closed and the officer “Confirms Charges” which transfers them to the Custody Information WorkSheet.
Note: due the restrictions of the addme limit of 10 columns, I had to eliminate the Statute column when transferring the charge to the worksheet
Page 3: Vic/Wit Info (Victim/Witness Information)
Page 4: Money
Page 5: Clothing, Page: 6 Valuables, Page: 7 Evidence
Page 8: Affiant Cont.
Page 9: PC (Probable Cause)
This is a Word Document (template) that is a legal document, stating the arresting Officer, Agency, Charges, Description of Arrest, Times and Dates. All the information shown here has been pulled from with the Userform (pages) or from specific WorkSheet cells. This document is opened via VBA, any additional information is entered to complete the form. It should be cleared prior to being opened.
Page 10: DIC 23/24/25
These are other legal forms (PDF fill-in forms) used in the event a suspect is charged with DWI. Information is pulled from the UserForm or specific WorkSheet cells where necessary.
Page 11: Save
This page is to be used to allow the user to save the information on a flash drive. It needs to be save prior to clearing all data so that if necessary, they could pull the information up, make changes and save or print the new info.
Page 12: Print
This page is dedicated to printing the pages required to create a report. The pages that are required shown.
The print setup should allow the user to select the number of each page that they want printed. There is a minimum required of each to turn in and some officers like to keep hard copies themselves. So a print window listing each, each with a dropdown box for selecting the number to be printed, along with a print to WHAT printer selector.