BACKGROUND: Although the application provided has narrow use, I post it primarily because it provides VBA code that integrates many of the things discussed here on OzGrid. The Excel workbook creates a formatted Word document (actually several), writes formulas to cells, adds formatting and conditional formatting, sorts data, creates new worksheets and hides/unhides exisiting worksheets, as well as doing some error checking before printing some sheets (the consolidated donor and bidder reports). I thought a broad-spectrum example might be useful even if the application does not match a specific problem need.
The attached Word document describes using the Excel application, which due to size (about 250MB since I left enough data and formatting in it to provide a reasonable sample of different ways to enter data) needs to be downloaded from http://mysite.verizon.net/vze121d5w/ExcelStuff/ -- look for the links in the left-side menu. (A link to an example file of the Bid Recorder that is generated by the first file also is located there.)
The main file “Auction Manager” is used to manage our church’s annual auction (an on-site evening event with no on-line or remote bidding). The code gets partially updated (as well as added to) each year, so varies in uniformity, but it seems to work fairly well and I pass it on in case anyone has use for such an application, or wants to pick the code apart to see how various things are done.
OzGrid is largely to “blame” for my ability to put this application together in the first place; so fellow Oz members, have fun playing with what you’ve helped me learn … and thank you all.
The basic file (Auction Manager) does a number of things, including generating a second Excel file for subsequent use. Initially it is used to
• record donated item descriptions and donor information,
• sort the items into categories decided annually by the auction chairs,
• produce Word document output for various purposes (portions of a hard-copy auction booklet, input for an HTML file used for the auction website, proofing copies for the auction committee, hard-copy sign-up sheets for use at the auction for the silent-auction items, and hard-copy sheets to post the silent-auction winners).
When all donated item entries are complete (received, categorized and sorted) the file contains code that produces a second version of the spreadsheet (Bid Recorder) that is used auction night to record the bids and then produce consolidated reports for the donors (stating who won their items and what tax deduction they get) and winners (what items they won and how much they owe).
The application assumes that the donors and bidders are a known crowd and data validation is used from the list on tab “Lookup” (which I usually keep “Hidden) to ensure spelling consistency. Highly observant folks will note a seemingly redundant listing of some couples (ones with differing his/her last names). This allows them to be looked up in the dropdown from either last name but still be sorted together when the donor and bidder reports are generated. (In our case we know almost everyone ahead of time and usually only need to add a couple of guests “on-the-fly” on auction night.)
The workbook relies heavily on VBA code. When it’s working correctly, you don’t need to know VBA. (My main user – the auction treasurer – usually finds something for me to fix every year.) I leave digesting the code to whomever wants to look at it.