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

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

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   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

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

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

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

[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