Loncat ke daftar isi utama

Bagaimana cara mengatur urutan tab sel atau sel yang tidak terkunci di Excel?

Misalkan Anda perlu mengatur urutan tab untuk sel yang Anda tentukan sendiri, misalnya, tekan tombol Tab akan berpindah dari sel B5 ke C6, dari C6 ke D7, dari D7 ke E8, lalu kembali ke B5 saat meninggalkan sel E8 di lembar kerja seperti gambar di bawah ini ditampilkan. Bagaimana cara mencapainya? Artikel ini menyediakan metode VBA untuk Anda.

Atur urutan tab sel atau sel yang tidak terkunci dengan kode VBA

Atur urutan tab sel atau sel yang tidak terkunci dengan kode VBA

Silakan jalankan metode VBA di bawah ini untuk mengatur urutan tab sel di lembar kerja normal atau sel yang tidak terkunci di lembar kerja yang dilindungi.

1. Pada lembar kerja Anda perlu mengatur urutan tab, klik kanan tab lembar dan klik Lihat kode dari menu konteks

2. Dalam Microsoft Visual Basic untuk Aplikasi jendela, salin dan tempel kode VBA di bawah ini ke file Kode jendela.

Kode VBA: Atur urutan tab sel atau sel yang tidak terkunci

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

Catatan:

1) Dalam kode, B5, C6, D7, E8 adalah urutan sel input, dan Anda harus menggunakan huruf besar alamat sel. Anda dapat mengubahnya berdasarkan kebutuhan Anda.
2) Untuk lembar kerja yang diproteksi, sel input harus dibuka kuncinya.

3. tekan lain + Q kunci untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

Mulai sekarang, setelah memasukkan data di B5, kursor menuju ke C6 dengan menekan tombol Tab atau Enter, lalu ke D7 saat keluar dari C6, lalu berpindah ke D7, E8 di lembar kerja saat ini.

Terkait artikel:

Alat Produktivitas Kantor Terbaik

Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria  |   VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar   |  Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel   |  Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas:  12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Canggih untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

tab kte 201905

Office Tab Menghadirkan antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
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