none
FreezePanes doesn't work with Application.ScreenUpdating = False (Excel 2007) RRS feed

  • Question

  • Hi

    I have a workbook (Excel 2007) with several worksheets and want to freeze panes in all

    The code:

    .

    Application.ScreenUpdating = False . ActiveWorkbook.Worksheets(4).Select ActiveWorkbook.Worksheets(4).Activate Range("E5").Select ActiveWindow.FreezePanes = True

    instead of range.select  also tried Application.Goto ActiveSheet.Range("E5") and if the activecell is for instance Z35 doesn't work correctly

    just work if  I set the Application.ScreenUpdating to true...

    Can someone advise... would like not see the screen jumpping...

    João

    Wednesday, February 5, 2014 9:51 PM

Answers

  • I use the same workaround in Excel 2003; it works fine.

    The trick is that the cells above and to the left of the freeze have to be visible (hence GoTo "A1").

    If the corner cell is not in the window, the window  is quartered as usual.

    Regards,

    Zk


    Saturday, June 14, 2014 2:13 PM

All replies

  • This works for me:

        Dim ws As Worksheet
        Application.ScreenUpdating = False
        For Each ws In Worksheets
            Application.GoTo ws.Range("A1"), True
            ws.Range("E5").Select
            ActiveWindow.FreezePanes = True
        Next ws
        Application.ScreenUpdating = True


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, February 6, 2014 6:18 AM
  • Hans

    if previously to run that code in one of the sheets you have selected for instance the cell CM25 that works ?

    I have tried and it doesn't. Only if I have the Application.ScreedUpdating = true (turn on)

    My problem is when the code run the user has already interact with the workbook,...

    João

    Thursday, February 6, 2014 9:08 AM
  • It does work for me, but I'm using Excel 2010, not 2007...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, February 6, 2014 3:41 PM
  • I use the same workaround in Excel 2003; it works fine.

    The trick is that the cells above and to the left of the freeze have to be visible (hence GoTo "A1").

    If the corner cell is not in the window, the window  is quartered as usual.

    Regards,

    Zk


    Saturday, June 14, 2014 2:13 PM
  • Zk

    Yes the trick is make the goto before set the ScreenUpdating to False.

    It also works in Excel 2007

    Thanks.

    João.

    Thursday, July 3, 2014 4:00 PM
  • I am having a similar issue: If I set Application.Visible = False, ActiveWindow.FreezePanes does not work.

    I'm calling the vba function stored in an Excel Marco-Enabled Workbook (.xlsm), using a PowerShell Script, while the PowerShell script is triggered by SQL Server Job Agent. If I directly run the PowerShell script in PowerShell console, this issue won't happen. But if I run this script in the PowerShell console inside the SQL Server 2014, this bug will happen.

    Environment:

    System: Windows Windows Server 2012 R2 Standard (6.3.9600 Build 9600)

    SQL Server: Microsoft SQL Server 2014 Standard 64-bit

    Excel: Excel 2013 32-bit (15.0.4420.1017) MSO (15.0.4420.1017)  (Microsoft Office Professional Plus 2013)

    PowreShell Version inside SQL Server: 

    PS SQLSEVER:\SQL\test> $PSVersionTable.PSVersion
    
    Major  Minor  Build  Revision
    -----  -----  -----  --------
    4      0      -1     -1

    • Edited by kind03 Tuesday, July 23, 2019 7:53 AM
    Tuesday, July 23, 2019 7:50 AM