Fixed objects will move
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Fixed objects will move

  1. #1
    Board Regular staticbob's Avatar
    Join Date
    Oct 2003
    Location
    Manchestershire
    Posts
    1,079
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Fixed objects will move

     
    Hi Guys,

    I'm having problems when trying to apply an auto filter to my data. When I select the critieria from the auto-filter drop down, on any field heading, I get this messgae "Fixed Objects Will Move" about 18 times.

    Any ideas ?

    Cheers
    Bob

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fixed objects will move

    Do you have comments within those cells by any chance ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular staticbob's Avatar
    Join Date
    Oct 2003
    Location
    Manchestershire
    Posts
    1,079
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fixed objects will move

    Yes, lots of the cells have comments Juan. Is there any way around this ? The comments are a fundamental part of the sheet.

    Thanks
    Bob

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fixed objects will move

    Well, I have never encountered that error myself. But some Google searchs pointed out that comments (or shapes for that matter) can cause that under some circumstances... unfortunately, the given solution was "Delete all shapes and see if that works..."
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Board Regular staticbob's Avatar
    Join Date
    Oct 2003
    Location
    Manchestershire
    Posts
    1,079
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fixed objects will move

    Thanks for the tips guys,

    It seems that this is linked to the freeze panes option. If I un-freeze panes and apply filters the error does not occur.

    Is there any way to code this ? So when a filter criteria is selected, remove freeze panes, apply the filter then re-freeze panes ?

    Thanks,
    Bob

  6. #6
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39° 17' 15" N, -94° 40' 26" W
    Posts
    9,962
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    For me it really was due to comments, nothing to do with Freezing Panes. I was able to fix it with a bit of VBA...

    Code:
    Sub CommentsMoveAndSize()
    
    '// Written by Greg Truby, July 2006
    '//
    '// If a large worksheet has comments and you
    '// attempt to apply AutoFilter, sometimes Excel will
    '// start telling you "Fixed objects will move."
    '//
    '// Per the kb article here:
    '// «« http://support.microsoft.com/default.aspx?id=211769 »»
    '// this routine fixes it.
    '//
    '// [Tested on a worksheet with 5,000 rows × 150 columns
    '//  and 1,796 comments.  Also tested on shared workbooks,
    '//  and protected worksheets. Neither raised an error.]
    
    
        Dim cmtEach As Comment
        
        If ActiveSheet.Type <> XlSheetType.xlWorksheet Then
            MsgBox "What are you? Daft?" & vbCr & vbCr & "Pick a worksheet.", _
                    vbExclamation, "Bad Sheet Selection"
            Exit Sub
        ElseIf ActiveSheet.Comments.Count = 0 Then
            MsgBox "There are no comments on the active sheet.", vbInformation, "I Have No Comment"
        Else
        
            '// Change each comment to "move and size with cells"
            For Each cmtEach In ActiveSheet.Comments
                cmtEach.Shape.Placement = xlMoveAndSize
            Next cmtEach
        
        End If
    End Sub
    My thanks to Juan Pablo and Debra Dalgleish for providing the trail of breadcrumbs I needed to solve my (actually our Finance Dept's) problem.
    Greg
    ………………………………………………
    Home: XL 2003, 2007, 2010, and 2013 on Windows 7
    Work: XL 2013, 2016 on Windows 10
    Please use CODE tags - especially for longer excerpts of code.

  7. #7
    New Member
    Join Date
    Sep 2006
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel: Fixed objects will move

    Apparently occurs when using autofilter

    Possible cause:
    There are columns without headers and not within the filter range that contain formatting or comments

    Possible solution:
    remove all filtering
    unhide all columns
    select all unused columns
    remove formatting from all unused columns
    delete all unused columns
    reapply filtering

  8. #8
    New Member
    Join Date
    Apr 2007
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fixed objects will move

    Hi,

    I too faced the same problem while in Auto Filter mode.
    Just try the below steps:
    1) Select and delete the Rows from the last data available row till 65536 rows in excel
    2) Select the last data available column and till to Last column of excel (Example: IV) and delete it.
    3) Now save it and try auto filter.

    The problem got solved for me. Hope it works for you. Enjoy

    Regards
    AG

  9. #9
    New Member
    Join Date
    Mar 2003
    Location
    bolton, england
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fixed objects will move

    What a life saver :O) Greg saved the day!
    This problem rendered my sheet useless; 12500 rows 50 columns 2500 comments; ran the macro 5 minutes and the autofilter was working again :0) joy.
    The filter also ran much faster however comments adjacent to hidden lines were not visible you could only see the indicatior line - changed "xlMoveAndSize" to "xlmove" ran the macro again problems solved.

    Cannot say thanks enough !!!

  10. #10
    New Member
    Join Date
    May 2008
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Fixed objects will move

      
    Thanks much to Greg. I've been researching and struggling with this issue for months and his was the first suggested solution that actually worked. What I especially appreciate was that instead of being speculative, it was specific. It correctly identified the nature of the problem and followed up with a detailed, specific and correct solution. Not only that, the code contained no errors!

    Bravo.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com