My specialty is developing automated Templates and Toolbars (Add-Ins) for Microsoft Word, PowerPoint, and Excel.
An important function of any template is to ensure a consistent look and feel to your documents by controlling logos, margins, fonts, spacing, and other formatting. But my templates go well beyond this to include time-saving automation features.
The automation is accomplished using macros, written in Microsoft’s proprietary automation language known as Visual Basic for Applications (VBA). VBA is built into Office, so there is no additional software to buy.
Macros can be written to do virtually anything that can be done manually using the normal program menus, and much more. A single macro can execute a complex series of steps, including asking for user input and carrying out action steps based on that input.
VBA can also control other Office programs besides the one you are working in, so for example a button in your Word template might automatically open an Excel workbook behind the scenes and read in a list to be presented to the user as a drop-down box. Or a macro in Excel might prompt you to select a range of cells, and then insert that information directly into PowerPoint as a table with your company’s formatting. Macros can create, open, and save any type of Office files.
I usually provide a custom tab in the Office menu “ribbon” containing buttons which launch the different automated functions. People easily learn to use these tools because they are integrated into the same programs they use every day and have a familiar look and feel.
I also use “Content Controls” extensively in my Word templates. These are fields into which the document creator inserts appropriate content, whether entered by hand, picked from a list, or pulled in from an external location. This can turn your template into a powerful document creation system, giving the user access to all of your company’s best content contained in centrally updatable external libraries.
My philosophy and my experience is that just about any kind of document automation can be accomplished with this technology. I love the creative process of thinking through with you how you can make document creation easier for your people. I encourage my clients to build a wish list of features, “it would be great if you could press a button and it would do such and such….”
Here are just a few of the kinds of features I have built for clients:
- A dialog box asks the user to pick what kinds of products are being sold, along with quantities and specifics about each. It then pulls in the appropriate descriptions, part numbers, and costs into various tables in the document, and tallies the total cost.
- An “insert content” button opens up a mini web browser page on the company’s SharePoint site, where the user can preview and/or insert standard company boilerplate document content, both text and graphics.
- A contract contains a placeholder where the name of the employee who will be the contract representative is to be entered. When the user clicks the field, a dropdown box appears so the user can pick the employee name from the company list, then the macro enters the person’s name and title into the document. That list of names lives in a spreadsheet on the company network, so it can be updated every time an employee comes or goes without needing to change the template.
- An engineering specification template contains all of the paragraphs that might ever be needed for the most complex solution that could be sold. As the salesman customizes each specification, he un-checks features that won’t be included in the current solution, and all of the paragraphs throughout the document that pertain to that feature are removed, and the paragraph numbering adjusted accordingly. The salesman clicks another button to indicate the State where the solution is being sold, and the appropriate legal phrases needed to meet that State’s requirements are inserted where needed.
- The client company name appears seven times throughout a proposal document. The seven placeholders are bound together, so that when the client name is entered into any one of them, all the others are instantly updated.
- The user clicks a button and selects from a list of executive bios available on the network, arranges them in the desired order, then clicks to insert them into the document.
- A salesperson opens a vendor bid for components that will become part of a telecom solution he is proposing. Using an Excel Add-In, he selects which components will be part of the solution, uses dialog boxes to indicate length of service agreement, with pricing drawn from the company pricing table, and enters markup percentages and applicable discounts. With one click, a new client quote is created in a new Excel worksheet, and the selected components are transferred line by line to the client quote, with marked up prices shown, totaled and summarized by section.
- A company letterhead template in Word lets users in all offices create consistent electronic letters. The template includes the company’s graphic header and footer, and inserts the correct address for the originating office. To insert the closing block, the user picks from a list of name/title blocks complete with digitized signatures.
- A proposal building toolbar in PowerPoint lets the user search a library of standard company capabilities slides, using a categorized menu with slide thumbnails, and insert one selected slide or a whole set of slides.
- One button makes it quick and easy to build a slide with photos of team members on a proposed project. The user highlights the team members by name from the latest staff list, then PowerPoint grabs the corresponding photos from the content library and arranges them into an attractive chart, automatically adjusting the layout depending on the number of photos chosen.
- A contract template provides three date picker controls for contract approval date, start date, and end date. As each is entered, the template automatically checks to make sure the dates are consistent (for example, the start date cannot be earlier than the approval date already entered), and if not, rejects the entry and displays an error message. In another control, after the user enters the contract dollar amount, the template spells automatically spells out the amount in words and puts it after the dollar figure in parentheses.
- Custom PowerPoint drop-down buttons make it easy to insert a variety of custom shapes and lines which match the company style, saving many keystrokes compared to adding and stylizing the shapes by hand from regular PowerPoint menus, or cutting and pasting them from old presentations.
- A report template comes pre-organized with main section headings. When the user clicks on a help button in the toolbar, a contextual help box pops up to prompt her what should be included in that particular section. Another button brings up a list of commonly used subheadings that relate to the current section, from which she can choose one to insert, or add a new heading, which then becomes part of the list the next time she uses the template.
I look forward to helping you automate your document creation!