☰

Excel，使用相同设置创建新窗口

tag-icon • tag-icon microsoft-excel
Excel，使用相同设置创建新窗口

为现有工作簿创建新窗口时，某些设置不会转移到新窗口。对我来说最重要的是冻结窗格、行和列设置，以及网格线的可见性。其他人报告说没有观察到其他设置。有些人描述了使用宏的解决方案，但这似乎为我希望有一个简单的解决方案的事情增加了额外的复杂性。

这是在现有工作簿上使用“视图 | 新建窗口”命令时发生的。

答案1

这是 Excel 的工作方式，可能是一个错误或“功能”。无法使用 Excel GUI 解决此问题，因此需要 VBA 宏。

文章 网格线和冻结窗格设置在新窗口中丢失 - 如何修复 描述问题并包含解决方案作为执行以下操作的 VBA 宏：

  • 创建新窗口
  • 循环遍历新窗口中的所有工作表并应用原始窗口中的以下内容：网格线、冻结窗格和标题。
  • 在原始窗口和新窗口中激活原始工作表
  • 以垂直并排分屏视图排列窗口
  • 滚动到工作簿中的活动选项卡，以便您可以查看它。

为了防止该文章将来消失，这里是 VBA 代码：

Sub New_Window_Preserve_Settings()
'Create a new window and apply the grid line settings
'for each sheet.

Dim ws As Worksheet
Dim i As Long
Dim iWinCnt As Long
Dim bGrid As Boolean
Dim bPanes As Boolean
Dim bHeadings As Boolean
Dim iSplitRow As Long
Dim iSplitCol As Long
Dim iActive As Long
Dim iZoom As Long
Dim sSep As String

  Application.ScreenUpdating = False

  'Store the active sheet
  iActive = ActiveSheet.Index

  'Create new window
  ActiveWindow.NewWindow
  iWinCnt = ActiveWorkbook.Windows.Count
  
  'Set the separator based on the version of Excel
  'Office 365 now using a dash
  If InStr(":", ActiveWorkbook.Name) > 0 Then
    sSep = ":"
  Else
    sSep = "  -  "
  End If
  
  'Loop through worksheets of original workbook
  'and apply grid line settings to each sheet.
  For Each ws In ActiveWorkbook.Worksheets
    Windows(ActiveWorkbook.Name & sSep & "1").Activate
    ws.Activate
    
    'Store the properties
    bGrid = ActiveWindow.DisplayGridlines
    bHeadings = ActiveWindow.DisplayHeadings
    iZoom = ActiveWindow.Zoom
    
    'Get freeze panes
    bPanes = ActiveWindow.FreezePanes
    If bPanes Then
       iSplitRow = ActiveWindow.SplitRow
       iSplitCol = ActiveWindow.SplitColumn
    End If
    
    'Activate the new window and sheet in loop
    Windows(ActiveWorkbook.Name & sSep & iWinCnt).Activate
    Worksheets(ws.Index).Activate
    
    'Set properties
    With ActiveWindow
      .DisplayGridlines = bGrid
      .DisplayHeadings = bHeadings
      .Zoom = iZoom
      If bPanes Then
        .SplitRow = iSplitRow
        .SplitColumn = iSplitCol
        .FreezePanes = True
      End If
    End With
  Next ws
    
  'Activate original active sheet for the new window
  Worksheets(iActive).Activate
  
  'Activate the original active sheet for the original window
  Windows(ActiveWorkbook.Name & sSep & "1").Activate
  Worksheets(iActive).Activate
  
  'Split Screen View (optional)
  'The following section can be commented out if you don't want split screen.
  
    'Turn screen updating on for split screen
    Application.ScreenUpdating = True
     
    For i = iWinCnt To 1 Step -1
      Windows(ActiveWorkbook.Name & sSep & i).Activate
    Next i
    
    'Split view side-by-side vertical
    ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlVertical
    
    'Scroll to active tab in original window
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    ActiveWindow.ScrollWorkbookTabs Sheets:=iActive
    
    'Scroll to active tab in new window
    Windows(ActiveWorkbook.Name & sSep & iWinCnt).Activate
    DoEvents
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    ActiveWindow.ScrollWorkbookTabs Sheets:=iActive
  
End Sub

本文还包含一个 VBA 宏来关闭附加窗口，这样它们就不会被错误地保存并覆盖原始工作簿：

Sub Close_Additional_Windows()
'Close additional windows and maximize original

Dim i As Long
Dim sSep As String

  'Set the separator based on the version of Excel
  'Office 365 now using a dash
  If InStr(":", ActiveWorkbook.Name) > 0 Then
    sSep = ":"
  Else
    sSep = "  -  "
  End If
  
  If ActiveWorkbook.Windows.Count > 1 Then
    For i = ActiveWorkbook.Windows.Count To 2 Step -1
      Windows(ActiveWorkbook.Name & sSep & i).Close
    Next i
  End If
  
  Windows(ActiveWorkbook.Name).WindowState = xlMaximized

End Sub

本文还包含有关在工作簿中安装宏的部分，以防您以前从未使用过 VBA。

相关内容