The Excel File Format

Pages in this article

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

All comments about this page:

Comment by: OVGuillermo (27-8-2007 12:09:00) deeplink to this comment

Thank you for your site. I have found here much useful information.
Good site ! ;)

Comment by: Dinesh (25-6-2008 02:01:04) deeplink to this comment

This is pretty use ful stuff for someone who is looking to modify an existing excel
tool to excel 2007. But when i try the above steps i ended up with an error
saying "one of the files got corrupted" or "run time error" .. can you help me on
this regard. After editing the fields required and saving it from .xmls.zip
to .xmls getting a runtime error.

Comment by: Jan Karel Pieterse (25-6-2008 03:06:19) deeplink to this comment

Hi Dinesh,

When do you get these messages, when loading the file after modifications? What did
you modify?

Comment by: Kalpana (19-8-2008 12:43:11) deeplink to this comment

Thank you very much for this informative site. Though the ECMA standards have it
all, its better to read such explanations of it with an example. You made my day :-)

Comment by: Kalpana (9-9-2008 11:06:59) deeplink to this comment

Nice information. Was planning to write down something similar in my blog for other
souls who have to parse SpreadsheetML files.

Regards,
Kalpana

Comment by: Jan Karel Pieterse (9-9-2008 22:21:55) deeplink to this comment

Hi Kalpana,

Thanks. Let me know if/when something has been published.

Comment by: Dan (22-9-2008 21:27:20) deeplink to this comment

I've been trying to use this method to create a dropdown combobox form control with
background color orange. It is white by default and cannot be changed from the form
control properties. The problem is I don't see a field in the VML that corresponds
to the background color. Any ideas?

Comment by: Jan Karel Pieterse (23-9-2008 01:24:12) deeplink to this comment

Hi Dan,

Looks like you can't change that for a dropdown indeed. The text formatting
properties are not available.

Comment by: Dan (23-9-2008 15:50:56) deeplink to this comment

Thanks. I was hoping that this can be done by formatting the cell behind the
control to orange and chaning the order. It seemed that you should be able to do
it, but in reality the "order" doesn't do antyhing. The control is always hiding
whatever is behind it and there's nothing you can do to change its color indirectly
via the cell underneath.

Comment by: Laithoron (26-9-2008 20:39:48) deeplink to this comment

Good find on how to change the text for the checkbox. Is it possible to change the
size of the actual checkbox itself, and if so how?

Comment by: Jan Karel Pieterse (28-9-2008 12:00:32) deeplink to this comment

Hi Laithoron,

The information is embedded in the x:Anchor attribute.
I describe the meaning of the numbers just above the "Download" heading on this page.

Comment by: Hari singh chauhan (16-4-2009 22:26:03) deeplink to this comment

Thanks , but I have a problem for representing Controls Object with DML . when I create excel file in window and than open same file on Mac and also reverse than Controls does not comes proper place when any graphics behind that Control Object that time Control Object looks misplace . it happens when graphics intersect to Control Object. also that time Graphics size increases and cell width and height increases so Please gives some ideas. How can solve this problem

Comment by: Jan Karel Pieterse (17-4-2009 03:51:57) deeplink to this comment

Hi Hari,

Not sure I understand. You have an Excel 2007 file with controls which look odd on MAC Excel?
WHat happens if (from Excel 2007) you save the file as Excel 97-2003 format?

Comment by: hari singh chauhan (17-4-2009 21:59:47) deeplink to this comment

Thank you sir for reply.
When i create any file in Excel 2007 Window which contain graphics shape and control objects like Button,Checkbox etc. but control objects intersect and place inside at graphics shape . than open same file on Mac OS . it will look control objects misplace our position and increases size . if we will take two and three control objects and graphics shape which intersect to each other than we can see it properly . so i am not able to represent graphics shape and control objects same just like to window show it.

Comment by: Jan Karel Pieterse (19-4-2009 07:17:50) deeplink to this comment

Hi Hari,

I don't think there is much you can do about this except trying different setups and controls until you find a combination that works on both platforms.
Tedious, but I see no other way.

Comment by: jyoti bulchandani (31-7-2009 00:03:41) deeplink to this comment

Hi,
can u plz let me know how to change the backgroung color of the listbox form control.

Comment by: Jan Karel Pieterse (31-7-2009 03:15:19) deeplink to this comment

Hi Jyoti,

I don't think that is possible with forms controls. Maybe with the control toolbox control you can?

Comment by: norman (2-2-2010 03:51:47) deeplink to this comment

amazing, thank you so much!!!

Comment by: Brian Skromme (10-2-2010 11:45:34) deeplink to this comment

I find a weird bug with Form Controls on chart sheets in Excel 2007. When I first open a workbook created in Excel 2003, the chart sheet that was active when I last saved the file (in Excel 2003 format) does not show the form controls (they appear invisible). When I go to another sheet and change the state of a checkbox there (with Visual Basic behind the controls), then go back to the first sheet, they are now visible (though this depends on which sheet was active when saved). Problem does not occur in Excel 2003. (Of course, there are innumerable other problems that pop up only in 2007 also!!) Any ideas? Installing the hotfix for the screen updating issue involving the Unprotect method did not help.

Comment by: Jan Karel Pieterse (11-2-2010 05:43:39) deeplink to this comment

Hi Brian,

That is an annoying bug indeed. I would not know a work-around, apart from not using the xls file format. SO if there is no real reason to keep the file in 2003 format, just upgrade the file to 2007 format.

Comment by: Tighe Thomas (12-8-2010 09:33:49) deeplink to this comment

I am trying to create a macro in Excel 2007 that creates a Form Control check box in it. I have everything the way I want it, the only problem is when I apply the macro to certain worksheets the number of the checkbox created is always different, causing me to get the error message "identifier under cursor not recognized", refering to:

ActiveSheet.Shapes("Check Box 88").Select

Is there a way I can have the check box created always be the same number so my macro runs on every spreadsheet I need it to without having to go into VBA and manually changing the number of the check box to the number created by the macro?

Comment by: Jan Karel Pieterse (16-8-2010 04:37:50) deeplink to this comment

Hi Tighe,

You should add some code to the macro that creates the checkbox, which gives the checkbox a different name:

Sub Foo()
    With ActiveSheet.CheckBoxes.Add(251.25, 54.75, 72, 72)
        .Name = "TEST"
    End With
End Sub

Comment by: Tim (24-1-2011 09:17:19) deeplink to this comment

Hi JK,

Great article!

In response to the comment below, this doesn't seem to work if you want to change the actual checkbox size.
Could you be a bit more specific? Which numbers should be changed if for instace I would want to double the checkbox size in this x:Anchor range:

0, 11, 0, 7, 3, 27, 6, 8

Thanks in advance!

Tim
____________________________________________________________

Hi Laithoron,

The information is embedded in the x:Anchor attribute.
I describe the meaning of the numbers just above the "Download" heading on this page.

Comment by: Jan Karel Pieterse (24-1-2011 10:43:57) deeplink to this comment

Hi Tim,

I expect the checkbox size itself is fixed, but I guess you'll just have to try!

Comment by: aM (20-6-2011 10:49:02) deeplink to this comment

I have inserted a 4 check boxes (form control) in one cell in my excel spreadsheet. when i need to hide the row that the check boxes are in, they do not hide with the row - instead the stay visible in the next row overlapping with its content. how do i fix this?
your help is greatly appreciated!

Thanks in advance - aM

Comment by: Jan Karel Pieterse (21-6-2011 01:18:32) deeplink to this comment

Hi aM,

Check out the properties of the checkboxes, set them to move and size with cells.

Comment by: Katrine (8-6-2012 11:12:14) deeplink to this comment

This drawing1.vml file is also accessed in order to fix a bug which mirrors text in a combobox dropdown list when opening old excel files in 2010. Check out this webguide to fix it, it is great:

http://desl.co.at/php/dienstleistungen/excel2010_dropdown_mirrored.php

Deleting the "flip:y;" entry in the vml displays the text in dropdowns correctly again.

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.