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)

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:

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 22 in total (Show All Comments):

Comment by: Jan Karel Pieterse (4/19/2009 7:17:50 AM)

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 (7/31/2009 12:03:41 AM)

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

Comment by: Jan Karel Pieterse (7/31/2009 3:15:19 AM)

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 3:51:47 AM)

amazing, thank you so much!!!

Comment by: Brian Skromme (2/10/2010 11:45:34 AM)

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 (2/11/2010 5:43:39 AM)

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 (8/12/2010 9:33:49 AM)

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 (8/16/2010 4:37:50 AM)

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

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.