Herding people (i.e. getting them to do what you want) is sometimes like the task of herding cats. That is to say, not easy. For just under three years I was running the men’s ministry of my church, and in this blog post I’m going to discuss and share an automated tool I created which helped to organise ‘interest groups’. You may find it helpful in similar endeavours.
I’m going to assume a reasonable knowledge of Excel skill. The code works on Office 2016; minor tweaks might be required for other versions. (Note when you open the spreadsheet you will get a “Macro Warning” at the top of Excel, which you have to “Enable” if you want to run the macros).
What is an ‘interest group’? Life these days seems busy, and between work and other family commitments time can be short. For the sake of sanity, we all need a hobby or something that we can do for pleasure. To some that might be gardening, to others motor-biking or watching sport. The goal of the interest groups was to let men in the church community know who else enjoyed similar pursuits. If you’re going fishing, why not take along other avid fishermen? (no pun intended)
Generation 1: Manual. When I first began to manage the interest groups I did it manually. I put the interest and the associated names on powerpoint slides which I played throughout a morning (to advertise them). In addition to this, I also had to email out contact details for the individuals for each interest group. Maintaining these two lists (names and contact details) was double-handling and tiresome.
Generation 2: Automatic. The best thing about being an IT nerd is the potential to leverage technology to automate processes. I wanted the process to be:
- easier to update and manage the list of people and interests
- generate a Powerpoint to display the individuals names under interests
- generate emails with contact information
Populating the Spreadsheet
The spreadsheet contains multiple tabs:
- _Settings – options for email generation.
- _PeopleList – a table containing a master list of people and their contact details. All additions/removals/edits for people should be done on this sheet.
- _Template – this worksheet was used as a template which I could copy-and-paste to create the other tabs.
- Other tabs: in the demo version there is “4×4 Driving”, “8 Ball”, “Writing” and “Woodwork”. Each of these tabs was manually created by copying the template sheet and editing it. When you do this, the “Individual” column is automatically a drop-down list of names based on the _PeopleList. Selecting the individual auto-populates their email and phone numbers.
So the steps involved for populating the spreadsheet are:
- Add all of the people to _PeopleList
- Create (and customise) a worksheet for each interest group. (By customise I mean: change the title in cell A1, (optional) add a description in cell A2.
- Select individuals for each interest group
(Note that any tab/sheet that starts with “_” will be ignored by the macro).
Generating the Powerpoint
First you need a powerpoint template file. I hard-coded it, so the filename has to be “Men’s Interests Template.pptx”, and it has to sit in the same folder as the Excel file.
The powerpoint generation assumes that only one slide will be visible. That slide will be the template that is used. As can be seen on the left, only the 4th slide is visible. The other slides are hidden. (The idea being that they can be added/made visible post-generation).
When the template file is saved (and closed), we can run the macro by selecting the Excel spreadsheet and pressing Alt+F8 which will open the below dialog box. Select “PPT_AllInterestGroups” and press the Run button.
The macro will then loop through every worksheet in the Excel workbook (ignoring any worksheet whose name starts with “_”) creating a slide for each interest. A maximum of 7 names will be shown per slide. If an interest group is popular, multiple slides will be created.
A new file will be created (in the same folder) called “Men’s Interests_today.pptx”. It will include any hidden slides (including the hidden template file) and a slide for each interest group.
Now we can auto-generate the Powerpoint file from our masterlist, but how can we actually get contact details to those involved in the interest groups?
In the Excel workbook if you select one interest group (“4×4 Driving”, “8 Ball”, “Writing” or “Woodwork”) and then press Alt+F8 and “EMAIL_InterestGroup” and select Run it will auto-generate the email in Outlook for you.
The email will be generated using HTML and the values and text in the _Settings tab of the spreadsheet. If the EmailAction is set to “Send” it will send the email automatically; if “Display” it will create it and let you hit Send.
At the time I had to get this functional in a short period of time – and I now discover I never went back to round out the edges.
- Deleting a person is still tedious. You need to remove them from the _PersonList and then any interest groups they are in. (Easiest way to do this would be to do a Workbook-wide Find and remove them from interest groups first (by deleting the row) and then take them off the _PersonList. Obviously this could be automated.
- I never created a “Generate All Emails” option.
- A “Generate for Person X” which generated all interest groups for a person into a single email would be cool.
- A helper method could make creating a new Interest Group from the template would make it user-friendly.
- Obviously remove some of the hard-coding on file names etc.
- A Conditional Format to show you’ve added someone twice would be nice.
Hopefully this might be handy to some.