如何在 Excel 2007 VBA 中以编程方式冻结 Excel 工作表的第一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3232920/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I programmatically freeze the top row of an Excel worksheet in Excel 2007 VBA?
提问by LJ.
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 > Freeze Panes > Freeze Top Rowcommand 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.
我希望以编程方式冻结 VBA 中 Excel 工作表的第一行。最终目标是产生与View > Freeze Panes > Freeze Top RowExcel 2007 中的命令相同的效果，以便冻结工作表的顶行，并且用户即使在滚动数据时也可以看到工作表的顶行。
采纳答案by Tomalak
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.
为不同的效果选择不同的范围，与您手动执行的方式非常相似。“冻结顶行”实际上只是 Excel 2007（及更高版本）中的新快捷方式，与早期版本的 Excel 相比，它不包含任何附加功能。
回答by Dirk Vollmar
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.
Tomalak 已经给了你一个正确的答案，但我想补充一点，当你想知道在用户界面中执行某个操作所需的 VBA 代码时，录制宏是个好主意。
In this case click Record Macroon 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:
在这种情况下，单击功能区开发人员选项卡上的录制宏，冻结顶行，然后停止录制。Excel 将为您记录以下宏，它也可以完成这项工作：
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
回答by Dannid
The problem with the recorded macro is the same as the problem with the built-in action: Excel chooses to freeze the top visiblerow, rather than the actual top row where the header information can be found.
录制宏的问题与内置操作的问题相同：Excel 选择冻结顶部可见行，而不是可以找到标题信息的实际顶部行。
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.
在这种情况下，宏的目的是冻结实际的顶行。当我查看行 #405592 并且我需要检查列的标题时（因为我在打开文件时忘记冻结行），我必须滚动到顶部，冻结顶行，然后找到返回再次行 #405592。因为我认为这是愚蠢的行为，所以我想要一个宏来纠正它，但是，就像我说的，录制的宏只是模仿了同样的愚蠢行为。
I am using Office 2011 for Mac OS X Lion
我正在使用 Office 2011 for Mac OS X Lion
Update (2 minutes later):
更新（2分钟后）：
I found a solution here: http://www.ozgrid.com/forum/showthread.php?t=19692
我在这里找到了一个解决方案：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
回答by Tom M
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:
只是遇到了同样的问题......出于某种原因，freezepanes 命令只是导致十字准线出现在屏幕中央。原来我已经关闭了 ScreenUpdating！用以下代码解决：
Application.ScreenUpdating = True
Cells(2, 1).Select
ActiveWindow.FreezePanes = True
Now it works fine.
现在它工作正常。
回答by Tom M
To expand this question into the realm of use outside of Excel s own VBA, the ActiveWindow propertymust be addressed as a child of the Excel.Application object.
要将这个问题扩展到 Excel 自己的 VBA 之外的使用领域，必须将ActiveWindow 属性作为Excel.Application 对象的子对象进行寻址。
Example for creating an Excel workbook from Access:
从 Access 创建 Excel 工作簿的示例：
Using the Excel.Application objectin another Office application's VBA project will require you to add Microsoft Excel 15.0 Object library (or equivalent for your own version).
在另一个 Office 应用程序的 VBA 项目中使用Excel.Application 对象将要求您添加 Microsoft Excel 15.0 对象库（或您自己版本的等效库）。
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 & "\Reports\Report_" & Format(Date, "yyyymmdd") & ".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.
核心过程实际上只是重复以前提交的答案，但我认为当您不在 Excel 自己的 VBA 中时，演示如何处理 ActiveWindow 很重要。虽然这里的代码是 VBA，但它应该可以直接转录到其他语言和平台。
回答by ian0411
Rows("2:2").Select
ActiveWindow.FreezePanes = True
This is the easiest way to freeze the top row. The rule for FreezePanesis it will freeze the upper left cornerfrom 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.
这是冻结顶行的最简单方法。规则FreezePanes是它将冻结您选择的单元格的左上角。例如，如果您突出显示 C10，它将在 B 列和 C 列、第 9 行和第 10 行之间冻结。因此，当您突出显示第 2 行时，它实际上会冻结在第一行和第 2 行之间，即第一行。
Also, the .SplitColumnor .SplitRowwill split your window once you unfreeze it which is not the way I like.
此外，一旦你解冻它，.SplitColumnor.SplitRow就会分裂你的窗口，这不是我喜欢的方式。
