How can I programmatically freeze the top row of an Excel worksheet in Excel 2007 VBA

Question

I am looking to programmatically freeze the top row of an Excel worksheet from VBA   The end goal is to produce the same effect as the View  gt  Freeze Panes  gt  Freeze Top Row command in Excel 2007 so that the top row of the worksheet is frozen and users can see the top row of the worksheet even as they scroll through the data

User · Answer

Tomalak already gave you a correct answer, but I would like to add that most of the times when you would like to know the VBA code needed to do a certain action in the user interface it is a good idea to record a macro.

In this case click Record Macro on the developer tab of the Ribbon, freeze the top row and then stop recording. Excel will have the following macro recorded for you which also does the job:

With ActiveWindow
    .SplitColumn = 0
    .SplitRow = 1
End With
ActiveWindow.FreezePanes = True

User · Answer

To expand this question into the realm of use outside of Excel s own VBA  the ActiveWindow property must be addressed as a child of the Excel Application object   Example for creating an Excel workbook from Access      Using the Excel Application object in another Office application s VBA project will require you to add Microsoft Excel 15 0 Object library  or equivalent for your own version     Option Explicit  Sub xls Build  Report       Dim xlApp As Excel Application  ws As Worksheet  wb As Workbook     Dim fn As String      Set xlApp   CreateObject  Excel Application       xlApp DisplayAlerts   False     xlApp Visible   True      Set wb   xlApp Workbooks Add     With wb          Sheets 1  Name    Report          With  Sheets  Report                 report generation here          End With           This is where the Freeze Pane is dealt with          Freezes top row         With xlApp ActiveWindow              SplitColumn   0              SplitRow   1              FreezePanes   True         End With          fn   CurrentProject Path  amp    Reports Report    amp  Format Date   yyyymmdd    amp    xlsx          If CBool Len Dir fn  vbNormal    Then Kill fn          SaveAs FileName  fn  FileFormat  xlOpenXMLWorkbook     End With  Close and Quit      wb Close False     xlApp Quit End Sub   The core process is really just a reiteration of previously submitted answers but I thought it was important to demonstrate how to deal with ActiveWindow when you are not within Excel s own VBA  While the code here is VBA  it should be directly transcribable to other languages and platforms

User · Answer

Rows  2 2   Select ActiveWindow FreezePanes   True   This is the easiest way to freeze the top row   The rule for FreezePanes is it will freeze the upper left corner from the cell you selected   For example  if you highlight C10  it will freeze between columns B and C  rows 9 and 10   So when you highlight Row 2  it actually freeze between Rows 1 and 2 which is the top row   Also  the  SplitColumn or  SplitRow will split your window once you unfreeze it which is not the way I like

User · Answer

Rows  2 2   Select ActiveWindow FreezePanes   True   Select a different range for a different effect  much the same way you would do manually  The  Freeze Top Row  really just is a shortcut new in Excel 2007  and up   it contains no added functionality compared to earlier versions of Excel

User · Answer

The problem with the recorded macro is the same as the problem with the built-in action  Excel chooses to freeze the top visible row  rather than the actual top row where the header information can be found   The purpose of a macro in this case is to freeze the actual top row  When I am viewing row  405592 and I need to check the header for the column  because I forgot to freeze rows when I opened the file   I have to scroll to the top  freeze the top row  then find my way back to row  405592 again  Since I believe this is stupid behavior  I want a macro to correct it  but  like I said  the recorded macro just mimics the same stupid behavior   I am using Office 2011 for Mac OS X Lion  Update  2 minutes later    I found a solution here  http   www ozgrid com forum showthread php t 19692  Dim r As Range  Set r   ActiveCell  Range  A2   Select  With ActiveWindow       FreezePanes   False       ScrollRow   1       ScrollColumn   1       FreezePanes   True       ScrollRow   r Row  End With  r Select

User · Answer

Just hit the same problem    For some reason  the freezepanes command just caused crosshairs to appear in the centre of the screen   It turns oout I had switched ScreenUpdating off  Solved with the following code   Application ScreenUpdating   True Cells 2  1  Select ActiveWindow FreezePanes   True   Now it works fine

[vba] How can I programmatically freeze the top row of an Excel worksheet in Excel 2007 VBA?

Examples related to vba

Examples related to excel

Examples related to ms-office