The Excel File Format

Pages in this article

  1. Worksheet Data
  2. Form Controls
  3. Edit with VBA
  4. Add RibbonX

Changing properties of Form controls

I was curious how Form controls are represented in the Excel OpenXML file format, so I started a journey searching for them.

Demo file

I have devised a small file with this on Sheet1:

Some form controls on a worksheet
Demo file showing a couple of Form controls

Where Form controls are stored

If you rename the xlsx file to xlsx.zip you can open the package and look inside. Open the folder xl and then the folder Worksheets. Open sheet1.xml. Near the bottom of that file, you can find this entry:

Snippet of xml in sheet1.xml file
Bottom section of sheet1.xml, highlighting the reference to the form controls file.

To find out what rId2 points to, open the _rels folder:

the _rels folder
Window of opened zip container showing the worksheets\_rels folder

Open the sheet1.xml.rels folder and find rId2:

A snippet of xml
Highlighted relevant portion of sheet1xml.rels

So now we know we need to navigate up the folder tree and find ..\xl\drawings\vmlDrawing1.vml.

VML files

There is one vmlDrawing?.vml for each worksheet that contains controls (or any other object that is captured in this vml file).

According to the Open XML specification document as it was made available at ecma (look for the "mark up language reference"), VML is described as:

"Vector Markup Language (VML) is an XML-based exchange, editing and delivery format for high-quality vector graphics. VML facilitates the exchange and subsequent editing of vector graphics between a wide variety of productivity and design applications. VML is based on XML 1.0, which is an open, simple, text-based language for describing structured data. "

Form controls in a VML file.

Drag the vml file from the explorer window on to your desktop and open the vml file.

If try to you open the vml file in Internet explorer (because it so prettily colours and indents xml) you get an unpleasant surprise, IE asks you whether it should download or save the file, putting you in an endless cycle unless you choose cancel. Luckily there is a tool called "XML notepad" (free download), which shows the xml in various ways. Here is the tool showing the xml portion of the checkbox:

Our xml displayed in XML notepad
XML Notepad showing the (formatted) xml part of a checkbox from the forms toolbar on an Excel sheet

Hey! what is that font node doing up there? For over a decade I've been telling people you cannot change a form's checkboxes font. Can you? Let's just try.

Changing the font of a checkbox (from the forms toolbar)

Note (2013-01-15): It seems that the behavior described below has been changed. If you use Excel 2010 to open a file changed using this trick, the font properties of the checkbox are no longer affected (which makes sense in a way, because you can also not change them from the user interface).

Switch to the treeview tab of XML notepad (if you're using that too) and edit the font face property and the size. I changed it to:

<font face="Arial Black" size="320" .....

Lets see what that did to our file. Follow these steps:

  • Save and close the vml file
  • Drag the vml file from your desktop (I do assume you opened and changed that one) to the Drawings folder inside the xlsx.zip container and confirm you want to replace the one that's there.
  • Close the explorer window and rename the xlsx.zip back to xlsx.
  • Open in Excel.

Surprise, surprise, your checkbox now looks like this:

The end result, our chekcbox

Forms checkbox in Excel with modified font

Lets stretch our luck a bit. Save the file as 97-2003 file format. Now try to open it in any of those formats (I tried 2003). Well, surprise, surprise, it sticks!

Changing a control's position

The position of  control is denoted by the Anchor node inside the control's set of nodes:

Where the xml needs to be edited
XML from the vmlDrawing1.vml file with size and position part highlighted

So what does this mean? The first two numbers tell us where the top-left corner of the control is horizontally: the column (zero based) and the offset from the start of the left of that column. The next two numbers represent the row number (again, zero based) and the offset downwards (pixels) from the top of that row. The next four do the same for the bottom-right corner.

In other words, in the xml above, the control's top-left corner is in column E, row 3. It is shifted to the right by 29 pixels. And the bottom-right corner is in cell I4, shifted 38 pixels to the right and 18 downwards, relative to that cell's top-left position.

Well, that's it. I hope I have given you a head start on working with form controls in the new Excel 2007 Open XML file format. I leave it up to you to figure out what the other nodes in the vml file are for.

Download

I have made the file used in this article available for download:

FormControls.zip


 


Comments

Showing last 8 comments of 35 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (8-6-2012 12:02:19) deeplink to this comment

Hi Katrine,

Thanks!


Comment by: Scott (14-1-2013 22:20:12) deeplink to this comment

Hi, I am trying to change the font size of a CheckBox control as described above but in Excel 2010. I make the changes but it has no effect. I even downloaded the example file provided above, edited as prescribed and only changed the text only from "Check Box 3" to "Yes" but it still had no effect. Is there something different in this process for 2010??? Thanks! Scott


Comment by: Jan Karel Pieterse (15-1-2013 11:49:06) deeplink to this comment

Hi Scott,

Thanks for letting me know. The behavior has indeed changed, so I have updated this page accordingly.


Comment by: James (28-8-2013 17:33:42) deeplink to this comment

I've managed to make it work in Excel 2010.

Along with changing the vmlDrawing1.vml file, as explained, I also found that the Drawing1.xml file also needed to be edited.

In the Drawing1.xml file, there's a bunch of attributes for the check box, and somewhere in the middle of it, you can find

<a:srgbClr val="000000"/>

Just replace the 000000 with the Hex color code (Eg, white = FFFFFF), and save and recompile along with the vml file.


Comment by: Jan Karel Pieterse (29-8-2013 16:16:48) deeplink to this comment

Hi James,

Good find!


Comment by: Ebrasam (21-4-2016 10:08:04) deeplink to this comment

Thanks for file:FormControls.zip !!


Comment by: Ebrasam (24-4-2016 22:36:26) deeplink to this comment

Hi Jan,
How about combo box font size? I tried Your trick for check boxes of my file and results were great. Thanks!
But I couldn't find location of font size of combo box in vml file. Is there any solution?
Best regards!


Comment by: Jan Karel Pieterse (25-4-2016 12:45:40) deeplink to this comment

Hi Ebrasam,

If the property isn't in the VML then there is nothing you can do, apart from using ActiveX controls.


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].