July 23, 2023: The beta phase is done, thanks to all our testers!
After 15 years, I got tired of waiting for Microsoft to provide content controls in Word for Mac. So I wrote a VBA add-in to do it, and now need beta testers. The beta version is free. There’s no installer, but I provide installation instructions.
In a previous post, I’ve written about how you can insert content controls using VBA: Content Controls for macOS – Cool Code. But using VBA requires more technical ability than the average Word user has. So writing an add-in makes these controls available to a much wider group of users.
The new content controls appear on Word’s Developer tab:
The Properties button opens a dialog that allows you to set the items relevant to that type of control:
Current issues with this first version:
In Word for Windows, you can set a separate character style for the control. The VBA commands for sorting styles don’t do the same thing as the Word interface, so I haven’t been able to make this work the same as in Windows. The content controls currently take on the style of the paragraph in which they are inserted, except for the Date Picker which displays Times New Roman (?!) when a date is selected.
The color for a control can be set to the same colors as in Windows, but there isn’t a custom color input yet.
If and when Microsoft gets around to adding content controls, the checkbox will probably select checked and unchecked characters from the macOS Emoji & Symbols dialog. Unfortunately, that dialog includes many system fonts that can’t be embedded in a file (this is a big problem with choosing bullets in PowerPoint, but that’s material for a different article). So I’ve created a subset of checked and unchecked symbols that can be used in checkboxes. These characters will work as expected and can be embedded in a template or document.
The properties dialog can’t preview the checkbox characters, because there is no way for VBA to retrieve the symbols currently used by a checkbox.
The date picker should be able to use a world-wide variety of locales and calendar types. I haven’t been able to program the system calls to macOS to get this information yet. The date picker currently uses U.S. defaults for date formatting. Using Microsoft date formatting codes (dd-mm-yyyy, etc.), you should be able to set up dates for other languages based on a Latin (Western) script. This is one of the items that needs testing in other languages.
You can get a free copy of the add-in by subscribing to this blog. If you’re already a subscriber, please write to me at the email in the right-hand column, and I’ll sed you a free copy.
As Apple and Microsoft release new operating systems, it’s no longer possible to install the latest version of Office on your old computer. Here’s is a list of the most recent Office you can run for the operating system you have, plus where to find it.
Most Recent Office You Can Run on macOS
Rant alert! Microsoft supports the last three operating systems in both Windows and Mac. Unfortunately, Apple has a policy of releasing a new operating system every year, whether we need it or not. The result of these two business decisions is that there are rafts of perfectly serviceable Macs out there that can no longer install the current version of Office. It looks like Apple intends us to toss them and buy new ones. Another case of lip service to environmental sustainability. Rant over
If you buy or subscribe to Office for Mac today, you’ll only find the current versions of Microsoft 365 (the subscription version) and Office 2021 (the retail edition) to be easily available. But Microsoft maintains a page of older installers at Update history for Office for Mac. All of them can be activated under a current Microsoft 365 subscription or Office 2021 license.
Please note that all of these are final releases: there will be no security updates to follow. Be careful when downloading Office files from the interweb.
At the time of writing, the current crop of obsolete Macs are those that can only run Catalina. These are machines that are around 10 years old, but are completely useable for applications like Office. The last Office version that Catalina can run is 16.66. At the History page, look for the Installer dated October 11, 2022. Download and run the installer. Then, in an Office program, choose Help>Check for Updates to get the final 16.66.1 version.
Catalina was the first OS to require 64-bit software. This requirement made Office 2011 obsolete, since it’s 32-bit. But Office 2011 is still a useful edition in macOS. It can still do things that were permanently removed from later versions of Office. So you might want to stick with Mojave and install the newer Office that goes with it. (There’s no problem in having Office 2011 and a later version installed on the same computer.) In this scenario, download and install the 16.54 version dated October 12, 2021.
One of my favorite Macs is my 17″ MacBook Pro. It’s the machine that travels with me, but it’s 12 years old and can run only High Sierra. The most recent Office you can run on this computer is 16.43, dated November 10, 2020.
There’s a pattern here: Apple releases new operating systems in the fall, usually in September or October. The Office version that Microsoft releases the same month is the last one for the fourth-oldest macOS. I don’t have any machines running Sierra, but if I did, I would try the October 15, 2019 edition of Office 16.30.
As I mentioned earlier, you can run an older version of Office on an older computer. Since Office 2008, the file format has remained pretty constant, so the software can still be useful. You can install Office 2011 under Mojave or earlier, while Office 2008 can be installed under El Capitan and earlier. These versions can co-exist on a Mac with a newer version (or with each other), though Microsoft’s History page only goes back to 16.27 from July, 2019.
Most Recent Office You Can Run on Windows
Under Windows, the situation is easier, as Microsoft waits about 3 years between operating systems. Plus Windows isn’t as finicky about the hardware it runs on. I have an old Mac Pro from 2006 that can only run OS X Lion and Office 2011, but under Boot Camp it does either Windows Vista and Office 2010, or Windows 7 and Office 2016. Windows 8 is likewise limited to Office 2016, while Windows 10 (now 9 years old!) can run the current version of Office 2021 and Microsoft 365. You have to have a really old Windows computer to be unable to run a useable copy of Office.
The ebook version of OOXML Hacking has been released. The last three years have gone into adding the equivalent of 40 pages of new information. This is in addition to the trove of unique techniques that already appeared in the print version. Here are screen shots of the table of contents, to give you an idea of the topics covered in this publication:
Barnes & Noble distributes only in the U.S., so use Kobo.com if you’re from another country.
As always, all techniques are covered in both Windows and Mac, where possible. The book contains a link to a downloadable text file of all listings in the book, so you don’t have to re-key text from the screen. The book is currently available on Kobo.com and Barnes & Noble. Barnes & Noble distributes only in the U.S., so use Kobo.com if you’re from another country.
Please note, this e-book has digital rights management applied. All code listings are available in a downloadable text file, so you don’t have to re-key anything. If you have any problems, please contact us at this address
I also answer questions at Experts Exchange, where I’ve been awarded a Distinguished Expert award for 2021.
This site isn’t free, but it is jam-packed with expertise. There’s a 7-day free trial, if you want to check it out. Tag your post with Microsoft PowerPoint, Microsoft Word, VBA and/or Fonts Typography to ensure your Office questions are answered.
Chart templates solve the old problem of having to send out copy-and-paste samples of graphs. But they only display the first 6 of the chart template colors you designed.
Adding More Colors
The most common application of chart templates is to overcome the design limitation of having only a 6-color palette to work with. I wrote about this problem years ago: Office Charts: 6 Colors Maximum! – Best Practices. After a chart uses Accents 1 to 6, it starts recycling those same 6 colors with automatically generated darker and lighter variations. Banks and wealth management clients often need many more than 6 data series in their charts, but still want to have colors that are on-brand and designed.
To create a wider range of data series colors, create a chart with the maximum number of data series that the client requires. Our record is 25 colors! Then right-click on the chart edge and choose Save as Template. This captures the formatting of the sample that you’ve created.
Your template will work just fine on charts that have already been created. Select the chart, choose Change Chart Type (yes, even if it’s the right type already), click on the Templates icon, select the chart template and click on Apply. Easy peasy, job done!
The New Chart Problem
But this process breaks down if you try to create a new chart from that template. Choose Insert>Chart, click on Templates, and select the chart type. Then start inputting data. As soon as you get past the 6th color, Office starts using those damn Microsoft default darker and lighter variations instead of the colors you set! This is Authoritarian Helpfulness at its worst!
Yes, you can fix this. Reapply the template by choosing Change Chart Type>Templates and reselecting the template. The colors are corrected to the design specs. But you shouldn’t have to do this! And now that you’ve found this article, you don’t have to.
Understanding Chart Template Colors
Let me back up a bit, and explain how chart templates are structured. The top level of the XML consists of [Content_Types].xml, a _rels folder for documenting the relationships of XML parts and a chart folder holding the good bits.
Inside the chart folder, we find chart.xml, which holds most of the formatting that we created when we made the custom chart. Chart.xml contains definitions that contain the intended series color. The colors in chart.xml get set when you save the file as a chart template, and they get used when you apply the template to an existing table. But they are not used when you create a new chart.
Each series is numbered starting with 0, so this is the series for the 7th color. For the first six, the color is set to an accent color. Starting with the 7th, the color fills are in RGB/hexadecimal. Here’s a sample:
In addition, there are subfolders for charts, media, theme, and _rels. The media folder only holds a BMP file that is used for thumbnail display in Office 2007 and 2010, but not in later versions. The _rels folder is for relationships of XML parts. The theme folder contains themeOverride1.xml. As the name suggests, this is a theme that overrides the theme of an Office file that hosts it. This allows you to use a chart template in any Office document while retaining the original design appearance. Finally, we have the charts folder, which has the XML part we’re looking for.
This folder contains 2 files: style1.xml, containing the formatting for all the chart parts and colors1.xml, with a list of default fill colors and transforms fo apply to those colors. Colors1.xml is the file that supplies the colors when a new chart is created from the template. Here’s what colors1.xml looks like:
On a newly inserted chart, an Office program will use the 6 colors in turn. Then it will cycle through them again, applying the a:lumMod val=”60000″ transform. LumMod modifies the luminance, turning each accent color to a dark version. The chart will follow this pattern instead of using the colors you designed for the extra data series. If you reapply the chart type to the chart, then it will update with your designed colors from chart.xml
Fixing Chart Template Colors
My first step was to read the Microsoft specs for cs:colorStyle. Under section 184.108.40.206 CT_ColorStyle, it reads: “The total set of colors is all contained colors repeated each time with each variation applied. A color style can contain 6 colors and 7 variations. This yields a total of 42 colors with the first 6 having the first variation applied, the second 6 having the second variation applied and so on.” Not promising, we really need more than 6 colors here.
After many fruitless experiments, I decided to see if I could add extra colors anyway:
To my astonishment, it worked! I could create a new chart and insert 10 dataseries with each displaying the designed color. No need to reapply the template! Who would ever have dreamt that Microsoft documentation has a mistake in it! ;-D
The takeaway is that to create a many-colored chart template that works as expected under all circumstances, you need to set the extra color values manually in colors1.xml.
For Office for Mac users, AppleScript remains a useful tool in scripting Office documents. VBA for Mac has poor links to the operating suystem. In Windows, a programmer can easily make system calls to the OS. In macOS, the analogous system calls are not documented by either Apple or Microsoft. A few intrepid programmers have posted about macOS equivalents, but the information is sporadic and very hard to find. macOS’s built-in AppleScript can solve many of these issues, so I’m posting the best available Office AppleScript Reference.
This is old Microsoft documentation from 2008 that they’ve removed from their site. I don’t guarantee this, or offer support, I’m just posting it to make it available to interested programmers.
Office AppleScript Reference: MacScript vs AppleScriptTask
Ron de Bruin is a European programmer and MVP (Microsoft Valued Professional) who has written lots of good information about programming Office on a Mac. While he writes mostly about Excel, VBA is so similar across Office that most of the information also applies to Word and PowerPoint. Here’s his page on using the AppleScriptTask command in Office 2016 and later to call AppleScripts from a VBA macro: AppleScriptTask in Mac Office 2016 or higher.
To create a macro that can run on different versions of Office for Mac, or on both Windows and Mac, you’ll need to use conditional compilation. This uses #If – #Else – #End if statements to run a set of commands only in certain conditions. Here is Microsoft’s page on conditional statements for different Mac versions: Differentiate between Office for Mac versions at compile time. This MS reference page shows all available conditional compiler constants: Compiler constants.
Another useful reference for AppleScript automation of Office is the online article Moving from Microsoft Office VBA to AppleScript by Paul Berkowitz. This is a full-length book with comparisons between VBA and AppleScript, plus many real-world example scripts. It was originally published in 2007, in anticipation of Office 2008. Unfortunately, the downloadable PDF they promise is no longer available.
The Best Practice is to NOT use Text Effects in Office. Ever.
That could have been my shortest article ever, but I guess I should explain the reasons. I’m referring to the graduated fills and lines, the glows, reflections, shadows and 3-D effects you can add to text. In the past these effects have caused some problems with ordinary shapes, but with text, they’re a disaster.
Clearly, it’s not enough for most users that these effects are visually hideous. That just a natural result of the low value we assign to arts education. In many years of working with competent graphic artists, I’ve never been asked to create any template that uses these effects. Designers understand the need for restraint, users don’t. And so we get the appalling appearance of most Word and PowerPoint documents.
But the functional problem with these effects is how they affect PDFs created from Word, PowerPoint and Excel. Microsoft has no clue how to export true PostScript of the fancy effects. So they adopt a simplistic approach: flatten them to graphics. Unfortunately, this means the text vanishes, leaving behind only a pretty picture. Well, not even that. All kinds of PDF functions are impaired: Text to speech is impossible, accessibility goes right out the window, reimporting the PDF to Office is brain-dead.
I tested PDFs created in 3 ways: saving to PDF in Office, printing to Acrobat and printing to the Microsoft Print to PDF print driver that comes with Windows 10. When saving to PDF, all text with applied effects was flattened. When printing to either Acrobat or Microsoft Print to PDF, Gradient Fills, Gradient Lines and 3D Effects were flattened, while Shadows, Reflections and Glows remained as live text.
The moral is clear: when the client asks for Text Effects, just say NO!
There are more AutoShapes in Office than appear in the user interface. Over time, Microsoft has quietly added to the collection stored in Office. Many of the more recent shapes are used in SmartArt files, while others have no current use that I can detect. But for anyone who hacks XML or codes VBA will find this week’s download or every autoshape a useful reference.
These are all the shapes that can be used in a piece of SmartArt. This is a deep topic: SmartArt XML is a programming language with a Frankenstein syntax. But the starting point for any language is to get the names right. SmartArt and VBA both reference the AutoShapes collection in Office, but they use different names for the same objects. Many of the same shapes are seen in the Shapes dropdown of Office programs, but those names are also different. The main source of the VBA names are from this page: MsoAutoShapeType enumeration (Office), while the XML names come from this out-of-date listing: SmartArt AutoShapes.
So, for my own sanity, I created a Word document showing an example of each shape, along with it’s name in XML, in VBA and in the user interface. This has smoothed out my workflow, and it may help you as well.
For any non-interface shapes, you can insert them into a document with code analogous to this. For PowerPoint:
ActivePresentation.Slides(1).Shapes.AddShape msoShapeTrapezoid, 24, 24, 144, 144
Change the bolded word to the shape name in the VBA MsoShapeType Name column.
Some shapes have specialized VBA commands, like callouts:
ActivePresentation.Slides(1).Shapes.AddCallout msoCalloutTwo, 24, 24, 144, 144
Callouts led me to a discovery about legacy versions. msoCalloutOne gives exactly the same result as msoCalloutTwo in current versions of Office.That seemed odd, so I ran the same macro in PowerPoint 2003 (I still have it installed for its macro recorder). In that version, msoCalloutOne creates a callout with a vertical leading line that can be moved up and down, but not at an angle. Presumably, MS found that useless and deprecated it.