提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何鎖定或凍結Excel中的工作表選項卡?

假設您有一個包含多個工作表的工作簿,則有一個名為Main-sheet的工作表作為工作簿中的第一個選項卡。 而現在,您想要嘗試鎖定或凍結此工作表選項卡,以使其始終可見,即使在多個工作表中滾動時也是如此。 實際上,沒有直接凍結選項卡的方法,但是,您可以使用解決方法來處理此問題。

使用VBA代碼鎖定或凍結特定的工作表選項卡


凍結/解凍窗格多個工作表:
Kutools for Excel's 凍結/解凍窗格多個工作表 只需點擊一下,就可以幫助您凍結或解凍工作簿中的所有工作表。
文檔凍結多張工作表1

箭頭藍色右泡 使用VBA代碼鎖定或凍結特定的工作表選項卡


在Excel中,我們可以應用以下VBA代碼,使特定的工作表始終保存在當前的單擊工作表選項卡之前,以便在滾動任何其他工作表選項卡時始終可以看到此工作表。 請做如下操作:

1。 按住 ALT + F11 鍵,然後打開 Microsoft Visual Basic for Applications窗口.

2。 然後選擇 的ThisWorkbook 從左邊 項目瀏覽器 窗格中,雙擊它打開 模塊,然後將以下VBA代碼複製並粘貼到空白模塊中:

VBA代碼:凍結或鎖定特定的工作表選項卡

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Update 20150306
Application.EnableEvents = False
Application.ScreenUpdating = False
If Application.ActiveSheet.Index <> Application.Sheets("Main-sheet").Index Then
    Application.Sheets("Main-sheet").Move Before:=Application.Sheets(Application.ActiveSheet.Index)
    Application.Sheets("Main-sheet").Activate
    Sh.Activate
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

DOC-冷凍片狀製表1

3。 然後保存並關閉此代碼,現在,當您單擊任何工作表選項卡時,此特定工作表將始終位於所單擊工作表選項卡的前端,請參閱截圖:

DOC-冷凍片狀製表2
-1
DOC-冷凍片狀製表3

備註:在上面的代碼中,主表格是要凍結的表格名稱,您可以將其更改為您的需要。


相關文章:

如何在Excel 2010中凍結窗格?

如何一次將凍結/解凍窗格應用於多個工作表?


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Xuan · 1 years ago
    chăng đc gi cả
  • To post as a guest, your comment is unpublished.
    Jesse · 1 years ago
    As Thuyen pointed out 2 years ago, you can't copy data between sheets while this code is active. Furthermore, the code is needlessly complicated. The sheet that you activate is passed to the procedure as the parameter "Sh". This makes the frequent calls to "ActiveSheet" unnecessary, and could cause problems for someone who's trying to modify the code but isn't very experienced.

    Here's my versions that corrects those issues, and even shows how to add a 2nd "Main" sheet (similar to what Dzingai posted):
    -----------------------------------------------------------------

    'These 2 lines aren't necessary if you use the sheets' codenames, which I recommend.
    Set shtMain1 = Worksheets("Main-Sheet-1")
    Set shtMain2 = Worksheets("Main-Sheet-2")

    If Application.CutCopyMode = False Then
    If Sh.Index <> shtMain1.Index And Sh.Index <> shtMain2.Index Then
    shtMain1.Move before:=Sh
    shtMain2.Move before:=Sh
    Sh.Activate
    End If
    End If
  • To post as a guest, your comment is unpublished.
    Sangs · 2 years ago
    This code worked well. Only problem is...if we close the file & open it again it goes off.
    • To post as a guest, your comment is unpublished.
      Dzingai · 2 years ago
      [quote name="Sangs"]This code worked well. Only problem is...if we close the file & open it again it goes off.[/quote]
      Try saving document as Macro-Enabled Workbook. I think it should work well that way.
  • To post as a guest, your comment is unpublished.
    Pablo · 3 years ago
    Is it possible to create one with multiple arguments? Like instead of just moving the one main sheet to the front of where you are working, is it possible to move three tabs in front of what you are working on?
    • To post as a guest, your comment is unpublished.
      dzingai · 2 years ago
      Yes, it is possible, you just have to add more arguments to the if clause using the "AND" like this

      IF Application.ActiveSheet.Index Application.Sheets("Main-sheet").Index AND Application.ActiveSheet.Index Application.Sheets("Other-Main-sheet").Index and so on...
      Then
      Application.Sheets("Main-sheet").Move Before:=Application.Sheets(Application.Sheets("Other-Main-sheet").Index)
      Application.Sheets("Main-sheet").Activate
      Application.Sheets("Other-Main-sheet").Move Before:=Application.Sheets(Application.ActiveSheet.Index)
      Application.Sheets("Other-Main-sheet").Activate
      Sh.Activate
      This will place the Main-Sheet, then the Other-Main-Sheet in front of your active sheet.
  • To post as a guest, your comment is unpublished.
    MEESHA · 3 years ago
    Could not get your code to work, but this one did :)

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim sc As Long ' count of sheets
    Dim NewPos As Long ' index of serlected sheet

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If ActiveSheet.Index 1 Then
    sc = Sheets.Count
    NewPos = ActiveSheet.Index
    For i = 2 To NewPos - 1
    Sheets(2).Move After:=Sheets(sc)
    Next i
    Sheets(1).Activate
    Sheets(2).Activate
    End If

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub
  • To post as a guest, your comment is unpublished.
    Thuyen · 4 years ago
    When I use VBA, I cannot copy data from Main-Sheet to another sheet
    Please help me fix this bug