Membuat Dropdown/ List box/ Daftar List di Excell dengan VB

CARA MEMBUAT DROPDOWN LIST BERTINGKAT PADA EXCEL

Saat membuat Dropdown List dengan fitur data validation, ada kalanya kita ingin dropdown list untuk pilihan sel tersebut berubah secara otomatis menyesuaikan dengan isi data pada sel lainnya.

Kasus semacam ini sering disebut dengan dropdown list bertingkat (nested dropdown list) atau list validasi bertingkat (nested list validation).

Kasus ini berbeda dengan dropdown list dinamis yang sudah kita pelajari sebelumnya.

Misalnya kita telah membuat list validasi berupa pilihan nama-nama provinsi, kemudian kita menginginkan untuk isian nama kabupaten/kota, dropdown listnya akan menyesuikan dengan isian data provinsi yang telah dipilih. Begitu juga dengan isian data nama kecamatan yang secara otomatis pilihan dropdown listnya akan menyesuaikan dengan data kabupaten yang dipilih.

Dropdown list atau list validasi sendiri digunakan untuk membatasi isi cell di excel dengan beberapa pilihan tertentu. Misalnya membuat pilihan jenis kelamin Laki-laki/Perempuan, pilihan Ya/Tidak dan lain sebagainya.

Untuk membuat pilihan sel dalam bentuk dropdown list atau sering juga disebut combo box, ikuti langkah-langkah berikut :

1. Pilih (seleksi) Sel/Range dimana dropdown list akan ditampilkan.
2. Pilih Menu Data Validation Pada Tab Data -- Group Data Tools atau gunakan Shorcut excel Alt+A+V+V.
   
   

Setelah muncul kotak pilihan (option box) "Data Validation" pada bagian dropdown Allow:pilih "List".

Pada bagian Source: tuliskan list atau daftar data list yang akan diijinkan untuk dimasukkan ke dalam cell.



Misal pada gambar diatas saya memasukkan teks "Laki-laki;Perempuan" untuk mebuat pilihan sel Laki-laki dan perempuan untuk isian jenis kelamin.

Selain menuliskan list data secara langsung, Source dropdown list ini bisa juga mengacu pada range data tertentu atau berupa named range excel.

Pastikan bagian ignore blank dan In-cell dropdown tercentang.

Atur input message dan alert message jika diperlukan. Bagian ini boleh diabaikan. Untuk lebih jelasnya baca pada tutorial sebelumnya.

1. • input message digunakan untuk mengatur pesan saat sel tervalidasi kita pilih.
   • alert message digunakan untuk mengatur pesan yang ditampilkan saat data yang inputkan salah atau tidak sesuai dengan pengaturan validasi excel.
2. Pilih OK dan selesai.

Jika langkah-langkah yang anda lakukan benar maka hasilnya akan nampak seperti gambar dibawah ini:



Namun jika data listnya banyak, maka bisa pada sourcenya menggunakan data, cell yang di buat terlebih dahulu, atau bisa dengan nama range



Misal range buah saya beri nama List_Buahmaka pada source tuliskan seperti digambar ini.



© Kelas Excel | https://www.kelasexcel.web.id/2017/05/cara-membuat-dropdown-list-excel.html?m=1

Ada dua cara yang bisa kita lakukan untuk membuat dropdown list validasi data bertingkat pada excel. Yang pertama dengan menggunakan Fungsi Excel IF dan yang kedua dengan menggunakan Fungsi Excel INDIRECT.

Buatlah sumber data untuk list validasi tingkat pertama yang akan kita gunakan sebagai pilihan data untuk dropdown list.

Siapkan juga sumber data untuk list validasi tingkat kedua. Daftar list ini yang nantinya akan digunakan untuk dropdown list kedua. Sebagai contoh saya akan menggunakan sumber data seperti pada gambar berikut:

Beri nama range untuk masing-masing list data yang telah kita buat. Gunakan named range yang mudah diingat. Cara membuat nama range bisa anda pelajari pada link ini: 3 Cara Memberi Nama Range Pada Excel

Selain nama range untuk masing-masing list, buat lagi satu (1) named range yang mengarah pada referensi sel yang tidak ada isinya (Sel kosong). Misal saya membuat nama range "Kosong" yang mengarah pada sel E1 seperti digambar berikut:



Pada contoh ini range untuk tingkat pertama (A2:A3) Saya beri nama List_Jenis. Sedangkan untuk Range tingkat kedua pertama (C2:C4) saya beri nama List_Tumbuhan dan Range tingkat kedua selanjutnya (D2:D5) saya beri nama List_Hewan. Sedangkan untuk nama range List_Kosong saya gunakan untuk menamai sel E1.

Setelah selesai membuat nama range kita lanjutkan dengan mengatur list validation untuk tingkat pertama. Pada contoh ini saya terapkan pada sel B7.



Cara membuat dropdown list untuk tingkat pertama ini sama seperti cara membuat dropdown list pada umumnya. Hanya saja kali ini kita menggunakan named range sebagai source datanya. Sehingga kita cukup menggunakan nama range untuk source data validation-nya seperti dalam gambar diatas.

=List_Jenis

Selanjutnya kita setting untuk list validation tingkat kedua dengan menggunakan rumus excel berikut pada source list data validationnya:

=IF($B$7="Tumbuhan";List_Tumbuhan;IF($B$7="Hewan";List_Hewan;List_Kosong))

Perhatikan bahwa nama range pada rumus diatas, tidak perlu ditulis diantara tanda petik ganda.

Rumus diatas dapat diartikan bahwa jika sel B7 berisi teks "Tumbuhan" maka data validasi akan menggunakan named range "List_Tumbuhan" sebagai source datanya. Jika B7 berisi teks "Hewan" maka akan menggunakan nama range "List_Hewan" sebagai sumber data list validasinya. Dan apabila B7 tidak berisi teks "Tumbuhan" atau "Hewan" maka akan menggunakan nama range "List_Kosong".

Selain menggunakan fungsi IF seperti cara pertama, kita juga bisa menggunakan fungsi INDIRECT Excel untuk membuat dropdown list bertingkat.

Langkah-langkah untuk membuat dropdown list validasi bertingkat dengan rumus Indirect adalah sebagai berikut:

1. Buat daftar data yang akan kita jadikan sumber list validasi seperti pada cara pertama.
   
   
   
   Untuk cara ke-2 ini judul kolom harus "sama persis" dengan list sumber untuk masing-masing pilihan validasi data.
2. Gunakan fitur Create from Selection pada menu Defined Names untuk menamai masing-masing kolom sumber data tersebut. Caranya:
   
   
   
   • Seleksi range sumber pada kolom pertama.
   • Pilih Tab Formulas -- Create from Selection.
   • Pada kotak opsi Create Names from Selection, centang hanya pada bagian Top row.
   • Klik OK.
   • Ulangi langkah 1-4 di atas untuk kolom-kolom sumber data yang lainnya.

1. Setelah selesai membuat nama range untuk masing-masing kolom sumber list validasi maka anda akan memiliki beberapa nama range seperti yang nampak pada kotak name manager berikut:
   
   
   
2. Setelah selesai membuat nama range, langkah berikutnya adalah men-setting validasi data untuk tingkat pertama, untuk contoh ini pada sel B8. Gunakan rumus berikut pada source list validasinya.
   
   

   =Kabupaten

   
   
   
   Untuk list validasi tingkat pertama ini masih sama seperti cara sebelumnya.
   
3. Gunakan fungsi Indirect Excel untuk source validasi data tingkat kedua. Tuliskan rumus berikut untuk mengisi kolom source list validasinya.
   
   

   =INDIRECT($B$8)

   
   
   
   $B$8 Merupakan sel acuan yang akan menentukan source range mana yang akan digunakan untuk dropdown list tingkat ke-2 ini.
   
4. Atur validasi data tingkat ketiga untuk cell B10. Seperti pada tingkat ke-2 sebelumnya, pada Source list validasinya masukkan rumus excel berikut:
   
   

   =INDIRECT($B$9)

   
   
5. Selesai. Jika langkah yang anda lakukan benar maka hasilnya akan seperti pada gambar dibawah ini.
   
   

Rumu excel "=INDIRECT(Alamat_Sel_Rujukan)" seperti yang kita gunakan di atas berlaku jika list pilihan yang kita gunakan tidak mengandung spasi atau hanya satu kata saja.

Jika mengandung spasi atau lebih dari satu kata maka cara diatas tidak berlaku. Sebab nama range tidak boleh mengandung spasi.

Apabila mengandung spasi maka saat membuat nama range dengan menggunakan cara "Create form Selection" di atas, secara otomatis spasi tersebut akan diganti dengan garis bawah/ underscore (_).

Lalu bagaimana solusinya?

Solusinya adalah dengan menggunakan rumus yang merubah spasi dengan garis bawah tersebut.

Salah satu cara yang bisa kita lakukan adalah dengan menggunakan fungsi SUBSTITUTE. Sehingga rumus excel untuk source validasinya akan seperti dibawah ini: 

INDIRECT(SUBSTITUTE(Referensi_Sel_Acuan;" ";"_"))

Perhatikan contoh berikut:



Dengan data diatas masing-masing source list validasi yang di gunakan adalah:

- Sel B8 : 

=List_Kabupaten

- Sel B9 :

=INDIRECT(SUBSTITUTE($B$8;" ";"_"))

Pada saat menggunakan rumus data diatas mungkin anda akan mendapatkan pesan error berikut.



Hal ini wajar sebab untuk saat ini sel B8 masih kosong sehingga excel menganggap nama range yang dicari tidak ditemukan. Jadi abaikan saja pesan error ini dan lanjut klik YES.

- Sel B10 :

=INDIRECT(SUBSTITUTE($B$9;" ";"_"))

© Kelas Excel | https://www.kelasexcel.web.id/2017/05/cara-membuat-dropdown-list-bertingkat-excel.html?m=1

MEMBUAT LIST BOX ACTIVEX EXCEL VBA

ListBox ActiveX dengan Combo Box ActiveX memiliki fungsi yang sama yaitu berupa list-list data yang bisa dipilih oleh user. Perbedaanya dari bentuknya kalau Combo Box drop down kebawah sementara kalau List Box berupa urutan data.

Cara membuatnya pada prinsipnya sama saja perbedaanya terletak pada codingnya, untuk lebih jelasnya ikuti langkah-langkah berikut ini :

Buka Sheet Microsoft Excel

Kemudian klik menu Developer >> klik Insert >> ActiveX Controls (pilih List Box)

Pastikan sheet pada kondisi Design Mode

Kemudian letakan List Box ActiveX Controls pada sheet Excel seperti contoh pada gambar dibawah ini.

List Box ActiveX Control

Selanjutnya kita buat koding dan setting propertiesnya

Pada List Box ActiveX klik kanan, kemudian pilih View Code. Kita letakan codingnya pada workbook >> Klik ThisWorkBook

Code List Box ActiveX Control

Kemudian kita ketikan koding berikut ini :

Option Explicit

Private Sub Workbook_Open()

With Sheet1.ListBox1
.AddItem “Guru”
.AddItem “Dokter”
.AddItem “Polisi”
.AddItem “Karyawan”
End With

End Sub

Sobat-sobat bisa atur Add Itemnya sesuai kebutuhan

Pada contoh diatas saya membuat List Box untuk jenis pekerjaan, misalnya bisa kita ganti dengan nama kota, nama provinsi dsb sesuaikan dengan kebutuhan.

Selanjutnya kita atur propertiesnya, yang paling penting kita atur yaitu Linked Cell tempat dimana hasil dari pilihan user akan kita letakan. Pada contoh diatas kita letakan pada cell B9. Kita juga bisa atur warna-warnanya, fontnya supaya lebih menarik.

Properties List Box ActiveX Control

Selanjutnya kita coba aktifkan yaitu dengan cara klik tombol Design mode terlebih dahulu.

Disimpan dengan format Save As type : Excel Macro-Enabled Workbook