Pilih beberapa item di daftar drop-down Excel – panduan lengkap
Daftar drop-down Excel adalah alat yang luar biasa untuk memastikan konsistensi data dan kemudahan masuk. Namun, secara default, mereka membatasi Anda untuk memilih satu item saja. Namun bagaimana jika Anda perlu memilih beberapa item dari daftar drop-down yang sama? Panduan komprehensif ini akan mengeksplorasi metode untuk mengaktifkan beberapa pilihan di daftar drop-down Excel, mengelola duplikat, mengatur pemisah khusus, dan menentukan cakupan daftar ini.
- Mengizinkan item duplikat
- Menghapus item yang ada
- Menyetel pemisah khusus
- Menetapkan rentang tertentu
- Mengeksekusi di lembar kerja yang dilindungi
Mengaktifkan Banyak Pilihan di Daftar Drop-Down
Bagian ini menyediakan dua metode untuk membantu Anda mengaktifkan beberapa pilihan dalam daftar drop-down di Excel.
Menggunakan Kode VBA
Untuk mengizinkan banyak pilihan dalam daftar drop-down, Anda dapat menggunakan Visual Basic untuk Aplikasi (VBA) di Excel. Skrip dapat mengubah perilaku daftar drop-down untuk menjadikannya daftar pilihan ganda. Silakan lakukan hal berikut.
Langkah 1: Buka editor Lembar (Kode).
- Buka lembar kerja yang berisi daftar drop-down yang ingin Anda aktifkan beberapa pilihannya.
- Klik kanan tab lembar dan pilih Lihat kode dari menu konteks
Langkah 2: Gunakan kode VBA
Sekarang salin kode VBA berikut dan tempelkan ke jendela lembar pembuka (Kode).
Kode VBA: Aktifkan beberapa pilihan di daftar drop-down Excel.
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Set TargetRange = Me.UsedRange ' Users can change target range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
If Not (xValue1 = xValue2 Or _
InStr(1, xValue1, delimiter & xValue2) > 0 Or _
InStr(1, xValue1, xValue2 & delimiter) > 0) Then
Target.Value = xValue1 & delimiter & xValue2
Else
Target.Value = xValue1
End If
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
Hasil
Saat Anda kembali ke lembar kerja, daftar drop-down akan memungkinkan Anda memilih beberapa opsi, lihat demo di bawah:
Kode VBA di atas:
- Berlaku untuk semua daftar drop-down validasi data di lembar kerja saat ini, baik yang sudah ada maupun yang dibuat di masa mendatang.
- Mencegah Anda memilih item yang sama lebih dari sekali di setiap daftar drop-down.
- Menggunakan koma sebagai pemisah untuk item yang dipilih. Silakan menggunakan pembatas lainnya lihat bagian ini untuk mengubah pemisah.
Menggunakan Kutools for Excel dalam beberapa klik
Jika Anda merasa tidak nyaman dengan VBA, alternatif yang lebih mudah adalah Kutools untuk Excel's Daftar Drop-down multi-pilih fitur. Alat yang mudah digunakan ini menyederhanakan pengaktifan beberapa pilihan dalam daftar drop-down, memungkinkan Anda menyesuaikan pemisah dan mengelola duplikat dengan mudah untuk memenuhi berbagai kebutuhan Anda.
Setelah menginstal Kutools untuk Excel, buka Kutools tab, pilih Daftar tarik-turun > Daftar Drop-down multi-pilih. Maka Anda perlu mengkonfigurasi sebagai berikut.
- Tentukan rentang yang berisi daftar drop-down dari mana Anda perlu memilih beberapa item.
- Tentukan pemisah untuk item yang dipilih di sel daftar drop-down.
- Klik OK untuk menyelesaikan pengaturan.
Hasil
Sekarang, ketika Anda mengklik sel dengan daftar drop-down dalam rentang yang ditentukan, kotak daftar akan muncul di sebelahnya. Cukup klik tombol "+" di sebelah item untuk menambahkannya ke sel drop-down, dan klik tombol "-" untuk menghapus item yang tidak Anda inginkan lagi. Lihat demonya di bawah ini:
- Periksalah Bungkus Teks Setelah Memasukkan Pemisah opsi jika Anda ingin menampilkan item yang dipilih secara vertikal di dalam sel. Jika Anda lebih memilih daftar horizontal, biarkan opsi ini tidak dicentang.
- Periksalah Aktifkan pencarian opsi jika Anda ingin menambahkan bilah pencarian ke daftar drop-down Anda.
- Untuk menerapkan fitur ini, silakan unduh dan instal Kutools untuk Excel pertama.
Lebih banyak operasi untuk daftar drop-down multi-pilihan
Bagian ini mengumpulkan berbagai skenario yang mungkin diperlukan saat mengaktifkan beberapa pilihan di daftar drop-down Validasi Data.
Mengizinkan item duplikat dalam daftar drop-down
Duplikat bisa menjadi masalah ketika beberapa pilihan diperbolehkan dalam daftar drop-down. Kode VBA di atas tidak mengizinkan item duplikat di daftar drop-down. Jika Anda perlu menyimpan item duplikat, coba kode VBA di bagian ini.
Kode VBA: Izinkan duplikat dalam daftar drop-down validasi data
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Set TargetRange = Me.UsedRange ' Users can change target range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
Target.Value = xValue1 & delimiter & xValue2
End If
Application.EnableEvents = True
On Error GoTo 0
End SubHasil
Sekarang Anda dapat memilih beberapa item dari daftar drop-down di lembar kerja saat ini. Untuk mengulang item di sel daftar drop-down, lanjutkan memilih item tersebut dari daftar. Lihat tangkapan layar:
Menghapus item yang ada dari daftar drop-down
Setelah memilih beberapa item dari daftar drop-down, terkadang Anda mungkin perlu menghapus item yang sudah ada dari sel daftar drop-down. Bagian ini menyediakan potongan kode VBA lainnya untuk membantu Anda menyelesaikan tugas ini.
Kode VBA: Hapus semua item yang ada dari sel daftar drop-down
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRngDV As Range
Dim TargetRange As Range
Dim oldValue As String
Dim newValue As String
Dim delimiter As String
Dim allValues As Variant
Dim valueExists As Boolean
Dim i As Long
Dim cleanedValue As String
Set TargetRange = Me.UsedRange ' Set your specific range here
delimiter = ", " ' Set your desired delimiter here
If Target.CountLarge > 1 Then Exit Sub
' Check if the change is within the specific range
If Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRngDV = Target.SpecialCells(xlCellTypeAllValidation)
If xRngDV Is Nothing Or Target.Value = "" Then
' Skip if there's no data validation or if the cell is cleared
Application.EnableEvents = True
Exit Sub
End If
On Error GoTo 0
If Not Intersect(Target, xRngDV) Is Nothing Then
Application.EnableEvents = False
newValue = Target.Value
Application.Undo
oldValue = Target.Value
Target.Value = newValue
' Split the old value by delimiter and check if new value already exists
allValues = Split(oldValue, delimiter)
valueExists = False
For i = LBound(allValues) To UBound(allValues)
If Trim(allValues(i)) = newValue Then
valueExists = True
Exit For
End If
Next i
' Add or remove value based on its existence
If valueExists Then
' Remove the value
cleanedValue = ""
For i = LBound(allValues) To UBound(allValues)
If Trim(allValues(i)) <> newValue Then
If cleanedValue <> "" Then cleanedValue = cleanedValue & delimiter
cleanedValue = cleanedValue & Trim(allValues(i))
End If
Next i
Target.Value = cleanedValue
Else
' Add the value
If oldValue <> "" Then
Target.Value = oldValue & delimiter & newValue
Else
Target.Value = newValue
End If
End If
Application.EnableEvents = True
End If
End SubHasil
Kode VBA ini memungkinkan Anda memilih beberapa item dari daftar drop-down dan dengan mudah menghapus item apa pun yang telah Anda pilih. Setelah memilih beberapa item, jika Anda ingin menghapus item tertentu, cukup pilih lagi item tersebut dari daftar.
Menyetel pemisah khusus
Pembatas disetel sebagai koma pada kode VBA di atas. Anda dapat memodifikasi variabel ini ke karakter pilihan mana pun untuk digunakan sebagai pemisah pilihan daftar drop-down. Inilah cara yang dapat Anda lakukan:
Seperti yang Anda lihat, semua kode VBA di atas memiliki baris berikut:
delimiter = ", "Anda hanya perlu mengubah koma menjadi pemisah apa pun sesuai kebutuhan. Misalnya, Anda ingin memisahkan item berdasarkan titik koma, ubah barisnya menjadi:
delimiter = "; "delimiter = vbNewLineMenetapkan rentang tertentu
Kode VBA di atas berlaku untuk semua daftar drop-down di lembar kerja saat ini. Jika Anda hanya ingin kode VBA diterapkan pada rentang daftar drop-down tertentu, Anda dapat menentukan rentang pada kode VBA di atas sebagai berikut.
Seperti yang Anda lihat, semua kode VBA di atas memiliki baris berikut:
Set TargetRange = Me.UsedRangeAnda hanya perlu mengubah baris menjadi:
Set TargetRange = Me.Range("C2:C10")Mengeksekusi di lembar kerja yang dilindungi
Bayangkan Anda telah memproteksi lembar kerja dengan kata sandi "123" dan atur sel daftar drop-down ke "unlocked" sebelum mengaktifkan perlindungan, sehingga memastikan bahwa fungsi multi-pilih tetap aktif setelah perlindungan. Namun, kode VBA yang disebutkan di atas tidak dapat berfungsi dalam kasus ini, dan bagian ini menjelaskan skrip VBA lain yang dirancang khusus untuk menangani fungsionalitas multi-pilih dalam lembar kerja yang dilindungi.
Kode VBA: Aktifkan banyak pilihan dalam daftar drop-down tanpa duplikat
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Dim isProtected As Boolean
Dim pswd As Variant
Set TargetRange = Me.UsedRange ' Set your specific range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
' Check if sheet is protected
isProtected = Me.ProtectContents
If isProtected Then
' If protected, temporarily unprotect. Adjust or remove the password as needed.
pswd = "yourPassword" ' Change or remove this as needed
Me.Unprotect Password:=pswd
End If
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then
If isProtected Then Me.Protect Password:=pswd
Exit Sub
End If
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
If Not (xValue1 = xValue2 Or _
InStr(1, xValue1, delimiter & xValue2) > 0 Or _
InStr(1, xValue1, xValue2 & delimiter) > 0) Then
Target.Value = xValue1 & delimiter & xValue2
Else
Target.Value = xValue1
End If
End If
Application.EnableEvents = True
On Error GoTo 0
' Re-protect the sheet if it was protected
If isProtected Then
Me.Protect Password:=pswd
End If
End SubDengan mengaktifkan beberapa pilihan di daftar drop-down Excel, Anda dapat meningkatkan fungsionalitas dan fleksibilitas lembar kerja Anda secara signifikan. Apakah Anda merasa nyaman dengan pengkodean VBA atau lebih memilih solusi yang lebih mudah seperti Kutools, Anda kini memiliki kemampuan untuk mengubah daftar drop-down standar Anda menjadi alat yang dinamis dan multi-pilih. Dengan keterampilan ini, Anda kini diperlengkapi untuk membuat dokumen Excel yang lebih dinamis dan mudah digunakan. Bagi mereka yang ingin mempelajari lebih dalam kemampuan Excel, situs web kami menawarkan banyak tutorial. Temukan tips dan trik Excel lainnya di sini.
Alat Produktivitas Kantor Terbaik
Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu. Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...
Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh 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!
Daftar Isi
- Mengaktifkan banyak pilihan
- Menggunakan kode VBA
- Menggunakan Kutools for Excel dalam beberapa klik
- Lebih banyak operasi
- Mengizinkan item duplikat
- Menghapus item yang ada
- Menyetel pemisah khusus
- Menetapkan rentang tertentu
- Mengeksekusi di lembar kerja yang dilindungi
- Artikel terkait
- Alat Produktivitas Kantor Terbaik
- komentar