Excel中的动态冻结窗格/冻结行 [英] Dynamic Freeze Pane / Frozen Row in Excel

问题描述

我需要使用Excel中的动态冻结标题行，因为我正在处理的工作表具有多个大表，如果它们位于同一工作表上，则更容易理解.

I needed a dynamic frozen header row in Excel as the sheet I was working with had Several Tables that were large and were easier to understand if they were located on the same sheet.

但是经过无休止的搜索之后，我找不到解决方案，因为没有滚动事件，并且滚动不会更改活动单元格.

But after searching endlessly I could not find a solution as there is no event for scrolling and scrolling doesn't change the active cell.

非常感谢，我找到了解决方法.

Thankfully I figured out a work around.

推荐答案

在寻找了如何确定活动窗口中第一个可见行之后，我能够为我的困境提出一个可接受的解决方案

I was able to come up with an acceptable solution for my dilemma after searching for how to Identify the first visible row in the active window running across

然后，我能够获取该代码并将其转换为可以与Timer事件结合使用的函数，该事件仅在需要冻结行的工作表上激活.

I was then able to take that code and convert it to a function that could be used in combination with a Timer event that is only activated on the sheet I need the frozen row.

工作表代码:

Private Sub Worksheet_Activate()
    StartFreezePaneTimeRefresh
End Sub

Private Sub Worksheet_Deactivate()
    StopFreezePaneTimeRefresh
End Sub

动态冻结窗格模块代码:

Private RefreshTime

Sub SetFreezePane()
    'Check if correct worksheet is active
    If ActiveWorkbook.ActiveSheet.Name = "Data" Then
        If IdentifyTopVisibleRow < 227 Then
            'Check if Frozen Row is the same as the Range to be Copied
            If Range("A1") <> Range("AN1") Then
                'Copy New Headers for Frozen Row
                Range("AN1:BU1").Copy
                Range("A1").PasteSpecial xlPasteValues
            End If
        ElseIf IdentifyTopVisibleRow > 227 Then
            'Check if Frozen Row is the same as the Range to be Copied
            If Range("A1") <> Range("AN2") Then
                'Copy New Headers for Frozen Row
                Range("AN2:BU2").Copy
                Range("A1").PasteSpecial xlPasteValues
            End If
        End If
    Else
        StopFreezePaneTimeRefresh
    End If
End Sub

Sub StartFreezePaneTimeRefresh()
    Call SetFreezePane
    RefreshTime = Now + TimeValue("00:00:01")
    Application.OnTime RefreshTime, "StartFreezePaneTimeRefresh"
End Sub

Sub StopFreezePaneTimeRefresh()
    On Error Resume Next
    Application.OnTime RefreshTime, "StartFreezePaneTimeRefresh", , False
End Sub

Public Function IdentifyTopVisibleRow() As Long
    'This code was found on MSDN at
    'https://social.msdn.microsoft.com/Forums/en-US/a6cff632-e123-4190-8556-d9f48af8fe9a/identify-first-visible-row-of-scrolled-excel-worksheet?forum=isvvba
    Dim lngTopRow As Long ' top row
    Dim lngNumRows As Long ' number of visible rows
    Dim lngLeftCol As Long ' leftmost column
    Dim lngNumCols As Long ' number of visible columns
    With ActiveWindow.VisibleRange
        lngTopRow = .Row
        lngNumRows = .Rows.Count
        lngLeftCol = .Column
        lngNumCols = .Columns.Count
    End With
    IdentifyTopVisibleRow = lngTopRow
End Function

该代码的工作方式是:首先检查正确的工作表是否处于活动状态，然后检查是否是正确的工作表，然后每秒检查一次最可见的行.

The code works by first checking if the correct sheet is active and if it is then it checks the top most visible row every second.

如果顶行大于或小于每个表的开始行，则它将检查是否已经设置了第一个标题，以防止其反复更改值.

If the top row is Greater or Lesser than the beginning rows of each table it then will check to see if the first header is already set to prevent it from changing the values over and over.

如果不是，则根据工作簿中的用户位置更改冻结的行"值.

If not it changes the Frozen Row values based upon the users location in the workbook.

注释:

更改被延迟了1秒，但这对于我所做的事情来说是可以接受的.

The change is delayed by 1 second but that is acceptable for what I am doing this for.

我在此使用的工作表仅是视图，因为如果您有关于如何设置第一行值而不更改选择的想法，它将不断将焦点转移到第一行，这将使这项工作变得很棒.

The sheet I am using this on is view only as this would constantly shift the focus to the first row if you have an idea on how to set the first row values without changing selection that would make this work great.

这篇关于Excel中的动态冻结窗格/冻结行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！