Form Checkbox Controls Disappearing

markejo

New Member
Joined
Mar 22, 2011
Messages
2
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
Any thoughts on what might be causing this?
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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

New Member
Joined
Mar 22, 2011
Messages
2
GlennUK - <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Thanks for your response. <o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
1. All of the Checkbox shapes in the Excel 2007 workbook are gone (not just hidden from view).<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Hopefully this will provide some insight into the problem I am having.<o:p></o:p>
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,353
Messages
5,528,197
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top