From VBA in Outlook, how do I freeze panes in Excel?

Question

I am writing a VBA macro in Outlook that references an Excel workbook and makes changes to that workbook. I want to freeze panes (not "Split") just below row 1.

Here's the code I tried:

With Excel.ActiveWindow
    .SplitColumn = 0
    .SplitRow = 3
    .FreezePanes = True
End With

...and...

Dim excApp As Object
Set excApp = CreateObject("Excel.Application")

With excApp.ActiveWindow
    .SplitColumn = 0
    .SplitRow = 3
    .FreezePanes = True
End With

Both threw run-time error 91 at the line .SplitColumn = 0.

I appreciate any help or pointers. Thanks!

Hambone · Accepted Answer · 2016-11-16T23:20:17.700

4

With freeze panes, it's actually relative to the cursor position, so this is one of the few places where .Select is actually necessary.

This code should do what you seek:

Dim excApp As Excel.Application
Set excApp = GetObject(, "Excel.Application")

excApp.ActiveWorkbook.ActiveSheet.Range("2:2").Select
excApp.ActiveWindow.FreezePanes = True

It presupposes Excel is already open.

-- EDIT --

Per @ChrisB, there is a way to do this without the dreaded .Select:

With excApp.ActiveWindow
    .SplitRow = 3
    .FreezePanes = True
End With

edited Nov 16 '16 at 23:20

answered Nov 16 '16 at 04:40

Hambone

15,600
8
46
69

I actually needed GetObject instead of CreateObject. Your solution works! – ChrisB Nov 16 '16 at 19:55
1

You actually don't need to use `Select`. Check out [excel vba freeze pane without select](http://stackoverflow.com/questions/34094445). `Dim excApp As Excel.Application Set excApp = GetObject(, "Excel.Application") With excApp.ActiveWindow .SplitRow = 3 .FreezePanes = True End With` – ChrisB Nov 16 '16 at 19:59
1

That's great. I learned something today; thanks. I've incorporated your feedback into the answer. – Hambone Nov 16 '16 at 23:20

score 0 · Answer 2 · answered Sep 05 '19 at 21:41

Here is another one. Its a Function which you can also use outside from Excel, e.g. from Access or Outlook:

Public Sub FreezeHeader(Sheet As Excel.Worksheet, _
                        Optional Rows As Long = 1, _
                        Optional Columns As Long = 0)
    If Sheet Is Nothing Then Exit Sub

    Sheet.Activate      ' must be the active sheet!
    With Sheet.Parent.Windows(1)
        If .FreezePanes Then .FreezePanes = False ' always delete the old freeze

        .SplitColumn = Columns
        .SplitRow = Rows

        If Rows > 0 Or Columns > 0 Then .FreezePanes = True
    End With
End Sub

And now use it like this:

Dim EXL As Excel.Application
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet

Set EXL = New Excel.Application
Set Book = EXL.Workbooks.Open("YourFilename.xlsx")
Set Sheet = Book.Sheets(1)

FreezeHeader Sheet       ' Freezes first row (the usual case)
FreezeHeader Sheet, 2    ' Freezes first two rows
FreezeHeader Sheet, 0, 1 ' Freezes first column
FreezeHeader Sheet, 0    ' Unfreezes all

From VBA in Outlook, how do I freeze panes in Excel?

2 Answers2