跳到主要內容

如何在Excel中設置單元格或未鎖定單元格的標籤順序？

假設您需要為自己定義的單元格設置 Tab 鍵順序，例如，按 Tab 鍵將從單元格 B5 移動到 C6，從 C6 移動到 D7，從 D7 移動到 E8，然後在工作表中離開單元格 E5 時返回到 B8如下圖所示。 如何實現？ 本文為您提供了一個 VBA 方法。

使用VBA代碼設置單元格或未鎖定單元格的選項卡順序

使用VBA代碼設置單元格或未鎖定單元格的選項卡順序

請運行下面的VBA方法來設置普通工作表中單元格的選項卡順序或受保護工作表中的未鎖定單元格。

1.在工作表中，您需要設置選項卡順序，右鍵單擊工作表選項卡，然後單擊 查看代碼 從上下文菜單。

2。 在裡面 Microsoft Visual Basic for Applications 窗口，將下面的VBA代碼複製並粘貼到 推薦碼 窗口。

VBA代碼：設置單元格或未鎖定單元格的選項卡順序

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tabArray As Variant
    Dim i As Long
    tabArray = Array("B5", "C6", "D7", "E8")
    Application.ScreenUpdating = False
    For i = LBound(tabArray) To UBound(tabArray)
        If tabArray(i) = Target.Address(0, 0) Then
            If i = UBound(tabArray) Then
                Me.Range(tabArray(LBound(tabArray))).Select
            Else
                Me.Range(tabArray(i + 1)).Select
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

筆記:

1）在代碼中，B5，C6，D7，E8是輸入單元格的順序，您必須使用單元格地址的大寫字母。 您可以根據需要進行更改。
2）對於受保護的工作表，必須將輸入單元解鎖。

3。 按 其他 + Q 關閉鍵 Microsoft Visual Basic for Applications 窗口。

從現在開始，在B5中輸入數據後，按Tab或Enter鍵將光標移至C6，在離開C7時將光標移至D6，然後在當前工作表中移至D7，E8。

相關文章:

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

增強您的 Excel 技能 Kutools for Excel，體驗前所未有的效率。 Kutools for Excel 提供 300 多種高級功能，可提高工作效率並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905

Office Tab 為 Office 帶來選項卡式界面，讓您的工作更輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (14)
No ratings yet. Be the first to rate!
Anita
Anita
This comment was minimized by the moderator on the site
I have copied your code into my worksheet, but as soon as I close it and reopen it to use it again, the code is gone. How do I save this?
Reply
0
0
crystal
crystal     Anita
This comment was minimized by the moderator on the site
Hi Anita,
If you want to keep the VBA code for feature use, after adding the VBA code, save your workbook as an Excel Macro-enabled workbook as shown in the screenshot below. Then use the newly saved file in the future.
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/Excel_macro-enabled_workbook.png
Reply
Report
0
0
RNA
RNA
This comment was minimized by the moderator on the site
Olá, o código VBA para definir a ordem das células desejadas funcionou perfeitamente. Eu percebi que para que ele funcione, é necessário entrar com um dado na célula e pressionar TAB ou ENTER.
PERGUNTA: Você tem como fazer este código percorrer todas as células (no meu caso são 31, sendo algumas vazias e outras com valores padrão) sem ter que entrar com dados nelas, somente pressionando TAB ou ENTER. Como eu tenho algumas células vazias e outras com valores, por padrão, eu gostaria de percorrer as células sem precisar entrar com dados em todas elas novamente, mas, somente alterando as células que forem preciso alterar.att

Text translated by Google Translator
Hello, the VBA code to set the order of the desired cells worked perfectly. I realized that for it to work, it's necessary to enter data into the cell and press TAB or ENTER.
QUESTION: You can make this code go through all the cells (in my case there are 31, some being empty and others with default values) without having to enter data in them, just pressing TAB or ENTER. As I have some empty cells and others with values, by default I would like to cycle through the cells without having to enter data in all of them again, but only changing the cells that I need to change.att

Reply
Report
0
0
Glenn Davis
Glenn Davis
This comment was minimized by the moderator on the site
Hello, New here The VBA code works until I hit a Pull Down Box, I get to the first on make my selection , hit Tab and it moves to a locked cell, It acts like a cell I did not enter any data in. Any suggestions? GlennD24
Reply
0
0
Dan Maycock
Dan Maycock
This comment was minimized by the moderator on the site
Hello, is there a way to just hit enter without entering anything and have it go to the next selection?
Reply
0
0
naturistaneptali@gmail.com
This comment was minimized by the moderator on the site
No me funcionó, lo coloqué en la hoja de trabajo, debe haber algún error, me podrían ayudar, necesito implementarlo en mi trabajo,

atte,

Collins Neptali Arráiz López
Reply
Report
0
0
Jeanne
Jeanne
This comment was minimized by the moderator on the site
Thanks for the code on enter order. I am working on a spreadsheet entering barcode information and I need to span up to 300 cells and the program only lets me enter 142 cells. Is there a way to add more so I can get up to 300?



Thanks
Reply
0
0
crystal
crystal     Jeanne
This comment was minimized by the moderator on the site
Hi Jeanne,
Thanks for your comment.
But the code can’t be optimized to meet your needs. Sorry about that.
Reply
Report
0
0
Evan
Evan
This comment was minimized by the moderator on the site
First select which cells you want to have unprotected.

When you protect your sheet it will automatically set the tab order according to your unprotected cells. It will Tab forward from Left to Right, and Top to Bottom; Shift+Tab backward from Right To Left, and Bottom to Top.
Reply
0
0
crystal
crystal     Evan
This comment was minimized by the moderator on the site
Dear Evan,
Thank you for sharing.
Reply
Report
0
0
Nat
Nat
This comment was minimized by the moderator on the site
With the order that you have listed them excel does without VBA code. Make them go backwards then you got something.
Reply
0
0
Jeannie
Jeannie
This comment was minimized by the moderator on the site
Hello and thank you for the VBA code to set the tab order in a protected sheet where the form control labels and form control drop-down boxes are unlocked and the user can tab through and fill out the information in a label, and when tabbing to a drop down list select the number of items the instructions give him, and I have made progress with your code. At first I could only tab through each of the label form controls, now I sometimes hit the drop down lists. I would really enjoy a turorial on how tro do tis
Reply
0
0
Spinaz
Spinaz
This comment was minimized by the moderator on the site
Is it possible to use this same code to force tab across each row to each unprotected cell? I need to tab starting at cell A15, and then move to the next unprotected cell (C15), then E15, then G15. Once here, I need to tab to A16 and repeat the process for the remainder of the form. The reason I need this is that data is entered by barcode scanner, and some are programmed to tab, and others to enter. Thanks for your help on this. Spinaz
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations