Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Excel 2007 FileFormat > Form Controls
Deze pagina in het Nederlands

The New Excel 2007 File Format

Changing properties of Form controls

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

Demo file

I have devised a small file with this on Sheet1:


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:


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

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


Window of opened zip container showing the worksheets\_rels folder

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


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:


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). If opened with Excel 2003, even stranger things happen, I got an extra checkbox control in my Excel 2003 when I changed the Font name of the VML file of the download to 'Courier New'.

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:

Surprise, surprise, your checkbox now looks like this:

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:


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 (6/8/2012 12:02:19 PM)

Hi Katrine,

Thanks!

 


Comment by: Scott (1/14/2013 10:20:12 PM)

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 (1/15/2013 11:49:06 AM)

Hi Scott,

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

 


Comment by: James (8/28/2013 5:33:42 PM)

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 (8/29/2013 4:16:48 PM)

Hi James,

Good find!

 


Comment by: Ebrasam (4/21/2016 10:08:04 AM)

Thanks for file:FormControls.zip !!

 


Comment by: Ebrasam (4/24/2016 10:36:26 PM)

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 (4/25/2016 12:45:40 PM)

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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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