vba 没有选择的excel vba冻结窗格

excel vba freeze pane without select

提问by ChrisB

I have a VBA script in Excel that freezes the panes of an Excel worksheet, but I'm curious to see if this is possible without first selecting a range. Here's by code now which freezes rows 1 through 7:

我在 Excel 中有一个 VBA 脚本，它可以冻结 Excel 工作表的窗格，但我很想知道这是否可行，而无需先选择一个范围。这是现在冻结第 1 行到第 7 行的代码：

ActiveSheet.Range("A8").Select
ActiveWindow.FreezePanes = True

Any suggestions?

有什么建议？

回答by

Record yourself using the View ? Freeze Panes ? Freeze Top Row command and this is what you get for .FreezePanes.

使用视图记录自己？冻结窗格 ? Freeze Top Row 命令，这就是您从.FreezePanes获得的。

With ActiveWindow
    If .FreezePanes Then .FreezePanes = False
    .SplitColumn = 0
    .SplitRow = 1
    .FreezePanes = True
End With

So modifying the .SplitColumnand/or .SplitRowproperties should do it for you regardless on what the ActiveCell propertyis.

因此，无论ActiveCell 属性是什么，修改.SplitColumn和/或.SplitRow属性都应该为您完成。

回答by z32a7ul

There are many things to get wrong about freezing panes. I add my own answer, so I will find it here, and won't have to reinvent it next time.

冻结窗格有很多错误。我添加了我自己的答案，所以我会在这里找到它，下次不必重新发明它。

Public Sub FreezePanesAt(rngDataTopLeft As Range)
    Dim wndCurrent As Window: For Each wndCurrent In rngDataTopLeft.Worksheet.Parent.Windows
        With wndCurrent
            .FreezePanes = False
            If Not ((rngDataTopLeft.Row = 1) And (rngDataTopLeft.Column = 1)) Then
                .ScrollRow = 1
                .ScrollColumn = 1
                .SplitRow = rngDataTopLeft.Row - 1
                .SplitColumn = rngDataTopLeft.Column - 1
                .FreezePanes = True
            End If
        End With
    Next
End Sub

Example usage:

用法示例：

FreezePanesAt ThisWorkbook.Worksheets("Sheet1").Range("B3")
FreezePanesAt ThisWorkbook.Names("Header").RefersToRange

• The input parameter is the top left cell of the bottom right pane; I think this is the most frequent use case: you know the range at which to split and don't care about which workbook / worksheet / window it is in
• If the input parameter is in the first row / first cell but not A1, then there will be only two panes; A1 is a special case, however, Excel would split the window at center of the current view, I prevented this because I can't think of any case where this would be intended
• It iterates through all Windows attached to the workbook / worksheet; indexing into Application.Windows(Windows(Thisworkbook.Name)) won't cause an error if you have more windows to the same workbook (the name would be "MyWorkbook:1"), or Excel attempted (which usually fails) to repair a workbook after a crash (the name would be "MyWorkbook [Repaired]")
• It takes into consideration that panes may already be frozen and the user / another macro might have scrolled to a location in the workbook, and the top left cell in the window is not A1

• 输入参数是右下窗格的左上角单元格；我认为这是最常见的用例：您知道要拆分的范围并且不关心它在哪个工作簿/工作表/窗口中
• 如果输入参数在第一行/第一个单元格但不在 A1 中，则只有两个窗格；A1 是一种特殊情况，但是，Excel 会在当前视图的中心拆分窗口，我阻止了这种情况，因为我想不出任何打算这样做的情况
• 它遍历附加到工作簿/工作表的所有 Windows；置入到Application.Windows（Windows(Thisworkbook.Name)如果你有多个窗口为同一工作簿（名称将是“MyWorkbook：1”））将不会导致错误或Excel企图（这通常失败）崩溃后修复工作簿（国名是“MyWorkbook [已修复]”）
• 考虑到窗格可能已经被冻结并且用户/另一个宏可能已经滚动到工作簿中的某个位置，并且窗口中左上角的单元格不是 A1

回答by Jon Dee

I found the previous answers only worked with some sheets when loopingthrough tabs. I found the following code worked on every tabI loopedthrough (target was a single workbook), despite which workbookwas the activeworkbook.

我发现以前的答案仅在looping通过时适用于某些工作表tabs。我发现以下代码适用于每一次tab我looped通过（目标是单个workbook），尽管它workbook是activeworkbook.

The short of it:

简而言之：

With Application.Windows(DataWKB.Name) 
    Application.Goto ws.Cells(4, 5)
    .SplitColumn = 4
    .SplitRow = 3
    .FreezePanes = True
End With

The code as it is in my Sub: (be aware, I do a lot more formatting in this sub, I tried to strip that out and leave just the code needed here)

我的代码Sub：（请注意，我在这个子文件中做了更多的格式化，我试图去掉它，只留下这里需要的代码）

Sub Format_Final_Report()
Dim DataWKB As Workbook
Set DataWKB = Workbooks("Report.xlsx")
Dim ws As Worksheet

Dim tabCNT As Long
Dim tabName As String
tabCNT = DataWKB.Sheets.Count

For i = 1 To tabCNT
    Set ws = DataWKB.Worksheets(i)
    tabName = ws.Name


    With Application.Windows(DataWKB.Name)
        Application.Goto ws.Cells(4, 5)
        .SplitColumn = 4
        .SplitRow = 3
        .FreezePanes = True
    End With

Next i

End Sub

Hopefully, this will save someone some research time in the future.

希望这会在将来为某人节省一些研究时间。

回答by CETAB

I need to be able to properly refreeze panes (when creating new windows, notably) without losing the activecell or messing up the visible range. It took a lot of playing around but I think I have something solid that works:

我需要能够正确地重新冻结窗格（尤其是在创建新窗口时）而不会丢失活动单元格或弄乱可见范围。这花了很多时间，但我认为我有一些可靠的东西：

Sub FreezePanes(nbLignes As Integer, nbColonnes As Integer, Optional ByVal feuille As Worksheet)
    If feuille Is Nothing Then Set feuille = ActiveSheet Else feuille.Activate
    Error GoTo erreur
    With ActiveWindow
        If .View = xlNormalView Then
            If .FreezePanes Then .FreezePanes = False
            If .Split Then .Split = False

            .SplitColumn = nbColonnes
            .SplitRow = nbLignes

            If .Panes.Count = 4 Then 'rows and columns frozen
                .Panes(1).ScrollRow = 1
                .Panes(1).ScrollColumn = 1
                .Panes(2).ScrollRow = 1 'top right pane
                .Panes(3).ScrollColumn = 1 'bottom left pane
            ElseIf nbLignes > 0 Then .Panes(1).ScrollRow = 1
            ElseIf nbColonnes > 0 Then .Panes(1).ScrollColumn = 1
            Else: GoTo erreur
            End If

            .FreezePanes = True
        End If
    End With
    Exit Sub
erreur:
    Debug.print "Erreur en exécutant le sub 'FreezePanes " & nbLignes & ", " & nbColonnes & ", '" & feuille.Name & "' : code #" & Err.Number & Err.Description
End Sub

回答by TaitK

I know this is old but I came across this tidbit that may be useful... as ChrisB stated, the SplitColumn/SplitRow values represent the last cell above/left of the split BUT of the currently visible window. So if you happen to have code like this:

我知道这是旧的，但我遇到了这个可能有用的花絮......正如 ChrisB 所说，SplitColumn/SplitRow 值表示当前可见窗口的拆分 BUT 上方/左侧的最后一个单元格。所以如果你碰巧有这样的代码：

Application.Goto Worksheets(2).Range("A101"), True
With ActiveWindow
 .SplitColumn = 0
 .SplitRow = 10
 .FreezePanes = True
End With

The split will be between rows 110 and 111 instead of 10 and 11.

拆分将在第 110 行和第 111 行而不是第 10 行和第 11 行之间进行。

edited for clarification and to add more information:
My point is that the values are offsets of the upper left cell, not an address of a cell. Therefore, ChrisB's Dec 4 '15 at 18:34 comment under the main answer only holds if row 1 is visible in the Activewindow.

编辑以澄清并添加更多信息：
我的观点是这些值是左上角单元格的偏移量，而不是单元格的地址。因此，ChrisB 于 2015 年 12 月 4 日 18:34 在主要答案下发表的评论仅在 Activewindow 中第 1 行可见时才成立。

A couple of other points on this:

关于这一点的其他几点：

1. using Application.goto doesn't necessarily put whichever cell you are trying to go to in the upper left
2. the cell that isput in the upper left when using .goto can depend on the size of the excel window, the current zoom level, etc (so fairly arbitrary)
3. it is possible to have the splits placed so that you can not see them or even scroll around in the visible window (if .FreezePanes = true). for example:

1. 使用 Application.goto 不一定会将您要访问的任何单元格放在左上角
2. 该单元被使用时.goto把左上角可取决于Excel窗口，当前缩放级别等的尺寸（因此相当任意的）
3. 可以放置拆分，以便您看不到它们，甚至在可见窗口中滚动（如果 .FreezePanes = true）。例如：

Application.Goto Worksheets(1).Range("A1"), True  
With ActiveWindow  
 .SplitColumn = 100  
 .SplitRow = 100  
 .FreezePanes = True  
End With  

CETAB may be dealing with this in their answer.

CETAB 可能会在他们的回答中处理这个问题。