Form Checkbox Controls Disappearing

[markejo]

I have a complex Excel workbook (3.8 megs) with multiple worksheets The core worksheet contains about 200 YES/NO Form Checkbox Controls (not ActiveX). The Checkbox Controls are linked to VB code that mimics the actions of the YES/NO ActiveX control. The core worksheet includes numerous other Objects and Autoshapes linked to VB code. Users of the workbook can create multiple copies of the core worksheet (adds 1.5 megs to the file size) for use with different entities. The workbook was developed in Excel 2003. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Some users of the workbook who are using Excel 2007 and higher have begun experiencing a problem. The workbook operates correctly for a time, even when additional core worksheets are added to the workbook. However, without any “significant” change to the size of the workbook (for example, a user changes an amount in a cell) or any other specific reason, all of the Form Checkbox Controls (from all of the worksheets included in the workbook) disappear when the workbook is saved. The VB code for the Checkboxes is not affected, nor are any of the other Objects or Shapes in the workbook affected.
<o></o>
Any thoughts on what might be causing this?

 

[GlennUK]

Can you test to see whether the objects are still there but not visible?

Also, can you say whether these objects were grouped together ( with anything ) in any way ... I've seen grouped objects behave strangely in Excel 2007.

 

[markejo]

GlennUK - <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks for your response. <o></o>
<o></o>
In follow-up to you comments I developed a VB routine that creates a matrix of relevant information about all of the "Shape" objects on a worksheet. I compared the matrix information for a worksheet that is working correctly and one that is having the Excel 2007 "Save" problem. Here is what I discovered.<o></o>
<o></o>
1. All of the Checkbox shapes in the Excel 2007 workbook are gone (not just hidden from view).<o></o>
<o></o>
2. As noted in my original post, the core worksheet includes numerous other Objects and Autoshapes (Text boxes, Rectangles, etc.) linked to VB code. The Rectangles are used as "jump-links" to other parts of the worksheet. Most of the Rectangles do not include any Text as part of the Shape object, but are positioned over narrative information contained in the worksheet. For example, if the narrative says "if you have this problem, go to SECTION 21," a rectangle with no text would be placed over "SECTION 21" in the worksheet.<o></o>
<o></o>
In a good worksheet, the Rectangles with no text have a Text content that is "blank." In the Excel 2007 worksheet, the Text content of these shapes is #N/A. The Rectangles in the Excel 2007 worksheet do not display any text when edited and the underlying VB code still works fine.<o></o>
<o></o>
3. There is one set of Grouped Shapes in the worksheet, a horizontal line Grouped with a down arrow. No VB code is associated with this Grouped Shape. None of the other shapes in the worksheet are Grouped.<o></o>
<o></o>
4. This problem does not occur the fist time the workbook is saved in Excel 2007 (would make the problem more understandable). Users have opened and saved the workbook in Excel 2007 numerous times without any problems. Then all of a sudden, the next time they open the workbook, all the Checkboxes in all of the worksheets in the workbook are gone.<o></o>
<o></o>
Hopefully this will provide some insight into the problem I am having.<o></o>

 

[GlennUK]

If this happens all of sudden then I sympathise with you, 'cos it's going to be hard to trace the cause. Do the users do anything unusual to cause the corruption?