Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Feb 2015
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Objects moving after saving

    I have a .xlsx spreadsheet that the objects on the left of the sheet that move to somewhat center of the sheet after saving...but not all the time. The objects are check boxes with a text that are linked to a true/false cell. In format control/properties, I have "Don't move or size with cells" checked for all of them. All 30 check boxes move together when they move. I don't know what else to do to prevent them from moving. I also lock the cells. I'd be willing to email the spreadsheet if that would help. Thanks! Scott

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Until you can correct the issue, here is a workaround that will shift the boxes to the left when the workbook opens:

    Before code runs:
    drzzt1.png

    After code runs:
    drzzt2.png

    In the ThisWorkbook Module, paste the following:
    Code:
    Private Sub Workbook_Open()
        Test
    End Sub
    In a Standard Module, paste the following:
    Code:
    Public Sub Test()
    On Error Resume Next
    For I = 1 To 6 'CHANGE THE 6 TO THE HIGHERST NAMED CHECKBOX
        Worksheets("Home").Shapes("Check Box " & I).Left = 10 'CHANGE HOME TO THE NAME OF YOUR WORKSHEET
    Next I
    End Sub
    Tis code assumes that the checkboxes are form controls and have the naming convention of "Check Box #"
    Change the 6 in the line: For I = 1 To 6 to the highest number named checkbox. Example "Check Box 17" (Home> Find & Select> Selection Pane)
    Change "Home" to the name of your worksheet
    Change the 10 in the line: Worksheets("Home").Shapes("Check Box " & I).Left = 10 to the indent you prefer

    If you are using office >=Excel 2007 then you will have to save the file as an .xlsm

    HTH,
    Maud

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    ..or just use
    Code:
    ActiveSheet.CheckBoxes.Left = 0
    ..that way it doesn't matter if the check box numbers are out of sequence, or how many there are.

    zeddy

  4. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2015-02-28)

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Zeddy,

    The only issue with using that line of code is that it lines them all up only if they are already aligned somewhere on the sheet. If they are staggered on the page, then it moves them as a group placing the most leftward checkbox at zero and still staggered.

    Maud

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2015-02-28)

  7. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    Good spot.
    So, if you want to keep the same alignments, and move them as a group, use the single line of code.
    If your checkboxes are not lined up, and you want them to be lined up, use a loop.

    Seems like best of both worlds are available.

    zeddy

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    You're on top of it Zeddy! We just don't know if the are aligned or staggered on the OP's sheet

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    drizzt76 (2015-03-03)

  10. #7
    New Lounger
    Join Date
    Feb 2015
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I will give it a shot. I'm a novice Excel user and honestly never worked with code like this before but I will try at work tonight. They are aligned.

  11. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Drizzt,

    I'd suggest that once you have them aligned to hold your Shift key and right-click each of them then release the Shift key right-click any one of them and select Group. They will now stay together as a group. Hopefully the group won't move but if it does you only have one object to move.
    Code:
    activesheet.shapes(1).left = 10
    HTH
    Last edited by RetiredGeek; 2015-02-28 at 16:37.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Drizzt,

    I have emailed you back your revised sheet. It will realign the checkboxes each time the sheet is opened.

    Please note the large number of duplicated unused and non-visible checkboxes you have created (~30). Look in the selection pane. Are they really needed?

    Maud

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    drizzt76 (2015-03-03)

  14. #10
    New Lounger
    Join Date
    Feb 2015
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I did that and they have moved as a group.
    Quote Originally Posted by RetiredGeek View Post
    Drizzt,

    I'd suggest that once you have them aligned to hold your Shift key and right-click each of them then release the Shift key right-click any one of them and select Group. They will now stay together as a group. Hopefully the group won't move but if it does you only have one object to move.
    Code:
    activesheet.shapes(1).left = 10
    HTH

  15. #11
    New Lounger
    Join Date
    Feb 2015
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for the revised sheet. I deleted the non-visible checkboxes because they don't serve a purpose. The spreadsheet in practice was opened and closed many times because it requires an update before and after every flight. So before I enter the macro to align it automatically (it is so cool! Thanks Maudibe) I'm telling the crew not to close the spreadsheet but just minimize it and keep it running. Since I've made that change, it hasn't done it for awhile. If it acts up again I will put the macro in it should fix it...until they start to shift vertically. Maudibe got me on the right path....if that happens. Thank you for your recommendations.
    Quote Originally Posted by Maudibe View Post
    Drizzt,

    I have emailed you back your revised sheet. It will realign the checkboxes each time the sheet is opened.

    Please note the large number of duplicated unused and non-visible checkboxes you have created (~30). Look in the selection pane. Are they really needed?

    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •