(C) 2009 - 2014 by Mourad Louha · All rights reserved

Positioning of form controls using VBA code – Bug in Excel 2010?

When testing an own developped VBA application in Excel 2010, I noticed a curiosity, which I personally would classify as a bug in Excel 2010.

My application is using some form controls, especially checkboxes which are hidden or unhidden per code. And the cells containing the controls are also hidden or unhidden per code.
The error in Excel 2010 appears when following steps are done:

  • Hide the form controls per code (Visible = False).
  • Hide the rows containing the controls.
  • Save and close the workbook.
  • Open the workbook again.
  • The form controls are now all on top and lost their original positions.

This problem does not appear in Excel 2007. Now, for getting sure that this is actually a problem of Excel 2010, I wrote a small sample application, which positioned 9 checkboxes as shown in the screenshots below. Please note, that I haved used the german version of Excel for the screenshots as the english version of Excel 2010 is not available yet for me. The properties for each control are set to “Move but don’t size with cells” (in German “Nur von Zellposition abhängig”). The two screenshots below show the worksheet in Excel 2007 and Excel 2010 in the initial state without any influence from VBA code.

Excel Controls Bug

Excel Controls Bug

Then I created the two procedures „HideCheckboxes()“ and „ShowCheckboxes()“ in a module and linked them to the two buttons. The relative simple code just hides or unhides the controls and the rows.

If the procedure linked to the button „Hide“ is called and the workbook saved, closed and reopened, the you can see the results as shown in the following screenshots for Excel 2007 and Excel 2010.

Excel Controls Bug

Excel Controls Bug

As you can see, all controls are stacked in Excel 2010. If you display the property „TopLeftCell“ for each control in a MsgBox, you will notice that in Excel 2007 all values ($C$3, $C$4, $C$5, …) are correct. Excel 2010 overwrites this property to $C$2 directly after hiding the controls and if you save and close the workbook, the value $C$2 is saved and all previous values are lost. This logically leads to an incorrect positioning of the controls when openening the file again.

Excel Controls Bug

An interesting behavious is that „TopLeftCell“ can be restored unless the workbook is not saved and closed.

The file is stored in the Open XML Format, therefore we can rename the file to *.zip and decompress the file with an unzip application. Depending on whether the file was created with Excel 2007 or Excel 2010, the archive contains different files a folder called „drawings“. The next screenshot shows an excerpt of the content of the two files „drawing1.xml“ and „vmlDrawing1.vml“ for the Excel 2010.

Excel Controls Bug

Let have look to the „vmlDrawing1.vml“ file, if you open the file with an text editor you can easily find the sections for the checkboxes.

Excel Controls Bug

The word „Anchor“ suggests the cell that is used for positioning the checkbox. And if we compare the values for „Anchor“ from the file for Excel 2007 with the values from the file for Excel 2010, we can notice that after hiding the controls in Excel 2010, all values have been set to „2, 0, 1, 0, 4, 0, 13, 0“. In the file for Excel 2007, these values are „2, 0, 2, 0, 4, 0, 13, 0“ for the first checkbox, „2, 0, 3, 0, 4, 0, 13, 0“ for the second checkbox and so on.

If you manually restore these values in the file „vmlDrawing1.vml“ for Excel 2010, then rebuild a zip file, rename it to a XLSX file and open the file in Excel 2007, you will notice that all the positions of the form controls are restaured. However, if you open the file in Excel 2010, nothing is restaured. I think, the „vmlDrawing1.vml“ file remained for compatibilty reasons in Excel 2010.

The file „drawing1.xml“ also contains some values for the checkboxes, but also the file „sheet1.xml“ for the first sheet in the „worksheets“. The values differs from the values for unhidden controls. I had experimented a little by changing some values to the values for unhidden controls. Unfortunately it was not possible for me to restore the original positions for the controls.

Finally I tested some other form controls. Excel 2010 has the same behaviour if you use comboboxes, radiobuttons or buttons. If you use buttons, it may happen that the buttons are reduced to a line, so someone may think they were removed.

If this behavious was not intentionally developped by the Office 2010 developers, then it’s a bug. Especially since this behavior has from my point of view no significant advantages.

You can avoid this problem by storing the position of the controls an a data sheet, for example, and, when unhidung the controls, set their original positions per code.

This article has also been published on my other blog www.software.maninweb.de
and on the German Version of the Excel Ticker blog.

 

 
Comments

Pardon English… It appears that saving the xlsm as an xlsb in 2007 and then opening it (and retaining the xlsb extension in 2010) can obivate this problem.

Hi Al, thanks for the tipp. I will check it out.

Regards 🙂

I can verify that this is a problem in the English version. In fact, you do not necessarily need all the VBA code. We manually entered a column of check boxes linked to the cells beneath them. These rows were also included in an several outline groups. After opening/closing the outlines and saving the worksheet, many of the check boxes are stacked on each other as you describe. This seems more evident in the lower sections of the outline. However, we have also noted that some of the upper sections propagate a shift in the check boxes (off the underlying cell) rather than stacking them on top of each other.

Mourad Louha

Hi Richard…

Thanks for your comment, suggestions and sharing your experience. I tried out what you described and its also the same in the German version of Excel.

Best regards 🙂

I have a similar problem.
I developed a VSTO Worksheet with Visual Studio 2008 C# .
But my boxes only move, if the pagesetup is not set to automaticaly. The boxes move to the left and to the top for each srolling up and down in the worksheet.

CD

Mourad Louha

Hi CD,

thanks for the info and sharing your experience. Interesting problem, although I do not have a solution.

Best regards 🙂

same problem occures in englist version. in my case – address of combo box shows address of next combo box in next row.

This error also occures when you have rows grouped. When you collapse the rows and then expand them, the form controls may or may not be in the correct position. This is the case for sure when you have the properties of the control set to “Move and size with cells”. I am not sure how it behaves under other property settings.

Hi Randall, much thanks for the info, Best Regards 🙂

This is a major bug in Excel 2010, the problem is not specific to controls any picture or attachment added with properties set to Move and size with cells remains collapsed and stacked at the top of the range of the grouped rows if the workbook is saved with the rows collapsed. Because positioning data has been lost an excel workbook used for project work and which stores many attachments is rendered useless and many hours of work can be lost.
Version of Excel is 2010 (14.0.6.6106.5005)

Hi Gary…

much thanks for the Info, I didn’t try it with pictures for example, but I will try it later, especially as you have the same Excel version as mine; SP1 I think.

Best Regards 🙂

My problem is that group boxes are all stacking up to the stop of the sheet. Work around is to specifically reposition the top of each group box in relation to an appropriate cell in the spreadsheet. eg.

ActiveSheet.GroupBoxes(“Group Box 180”).Top = Range(“B274”).Top + 11

This will position the top of the box at teh top of row 274, I’ve added 11 points on to this position to move it down a bit further.

Hi Edward,

Thanks for the tip, Best Regards 🙂

Stephen A. Watkins

Hey, I found this site while looking for a solution to this very problem (in the English Language version of Excel). Since this page features prominently in almost every variation of the google search query regarding the problem of controls collapsing together when rows are hidden, I thought I’d share my experience and how I resolved it:

My worksheet is tabular with a series of rows and checkboxes arrayed in columns. Every time a new row of data is added, a VBA macro adds checkboxes to the row. But when I tried to use the Filter method to zoom in on any data, my checkboxes started disappearing. I discovered they’d stacked on top of each other, as discussed here.

I was able to cobble together a bit of VBA that fixes the problem whenever it happens.

Dim myCkbx As CheckBox
Dim myRng As Range
For Each myCkbx In ActiveSheet.CheckBoxes
    If myCkbx.LinkedCell <> myCkbx.TopLeftCell.Address Then
        Set myRng = Range(myCkbx.LinkedCell)
        myCkbx.Top = myRng.Top
        myCkbx.Left = myRng.Left
    End If
Next myCkbx 

I have this in a Sub that I call on a button press, but I’m thinking this could just as easily go in the sheet’s On Open event to automatically fix this every time you open the worksheet.

Stephen A. Watkins

Is it possible to put some Code tags on that? Excel’s Not Equal sign got stripped out (because it looks like an HTML tag, i.e. LessThanSign followed by GreaterThanSign). The Not Equal should go in the If Statement between myCkBx.LinkedCell and myCkbx.TopLeftCell.Address.

Thanks and good luck!

Mourad Louha

Hi Stephen,

Thanks for sharing your code and solution!
I have edited the options for your previous comment so that the characters should be visible now.

Best Regards, Mourad

I use Excel 2010 and I developed a VBA with comboBox and spin button controls. I hide certain rows depending on what the user chooses in certain cells. Now I cannot even locate where the controls went. Even in the Design Mode of the Developer, the controls seem to have vanished. I know they are there because the VB code does not give any errors, but I have no way of changing the values in the spin button or the drop down menu combo Box. This is totally frustrating. I have data in Rows 1 through 240 and Columns A To Z. I have looked as far down as Row 4000 and Column DZ.

Hello Siva,

The controls may have a height = 0. That’s why they look, they have disappeared, but I guess, they are still there. You can, for example, iterate your controls by using vba and reset their height to a value greater than zero.

Regards 🙂

Thanks! Looks like that is what happened. Hopefully now I can make sure that the controls are on top of the proper cells by writing the code to specify the position, width and height of the control.

Thanks for sharing 🙂

Leave a Reply