none
Excel 2010 checkbox control move when I save my file.

    Question

  • I have over 2000 check boxes linked to their respective row positioned under column B with a linked cell in column C (True/False) and a macro aimed at Column N to record the date the status changes. The checkboxes are formated not to move or size with cell. The linked cell is anchored  $X$X for their respective row.  The boxes were each sized slightly smaller than the cell and positioned in the lower left corner of the cell.  This allowed the macro to capture the row of the respective checkbox and by using the offset made it possible to capture the change date in Column N. I sort the data on Column C, J, and D which moves the finished items to the bottom of the list grouping them of (J) and in Orderdate (d) order.

    This has worked for three years nicely in Excel 2007.  Now in 2010 the checkboxes won't stay put, moving upward and to the left when i reopen the file after saving.  Moving 2000 controls to a specific location within cells is very time consuming task. Today the bottom box was some 80 rows above where it should be and the rest were somewhere above it. It looked like a freight train wreck. I didn't print or sort the records before saving. Does anyone have some gorilla glue for excel spreadsheets.

    Thanks

    Tuesday, May 22, 2012 6:03 PM

All replies

  • Hi,

    If the position of each checkbox is the same offset of the position they used to be, then I think we can use a macro to make them go back to the original position.

    The code you can refer is below:

    Public Sub checkbox_position()

    Dim obj As Shape

    For Each obj In Sheet2.Shapes

        obj.IncrementLeft 5

        obj.IncrementTop -12

    Next

    End Sub

    The number you can change according to the position.


    Jaynet Zhang

    TechNet Community Support

    Wednesday, May 23, 2012 8:59 AM
  • Thanks for the reply. They are not the same offset.  They are scrambled, compressed and without looking at each one separately I'm not sure which one goes where.  This might help but I'm not sure.  The linked cell stored in the control has an offset of 0,1 or is one cell to the right of where the check box should be. If they could be moved back based on that information that would be great. Otherwise it's make all of them over again.  If I am forced to do that I am considering the active X checkbox because I think they will anchor to a cell and move with the row when sorted.  Not sure yet how to attach a macro to an active X check box for the second step (date stamp) of the process.
    Wednesday, May 23, 2012 1:26 PM
  • Hi,

    Try to install Security Update for Microsoft Excel 2010 (KB2466146) to check the issue.

    This is for 32-bit:

    http://www.microsoft.com/en-us/download/details.aspx?id=1003

    This is for 64-bit:

    http://www.microsoft.com/en-us/download/details.aspx?id=21276


    Jaynet Zhang

    TechNet Community Support

    Thursday, May 24, 2012 3:16 AM
  • Sorry but I have already loaded this update and it didn't solve the problem.

    every time I save the file the checkbox appears to have text behind it.   I can't use it then but I just open the saved copy and it refreshes for another round of tests.  Still no solution.

    Thanks again for all the suggesstions.

    Friday, June 22, 2012 5:09 PM
  • There a trick to fix this by resizing the page.

    add below codes to your sub "Private Sub Workbook_Open()" or other opening/activation event for the sheets(change the size 85 to what ever size you want for your form)

        ActiveWindow.Zoom = 100
        ActiveWindow.Zoom = 85
        ActiveWindow.ScrollColumn = 1

    This has fixed my problem and it's the easiest solution

    if you use macro to print, this need to be added in after each print as well.

    Tuesday, October 23, 2012 1:31 AM