Combo Box dengan fitur Search Engine

Selamat Malam Pembaca yang budiman,

Pada kesempatan kali ini saya mencoba memaparkan bagaimana cara membuat combo box yang lebih praktis, mengingat untuk data yang jumlahnya cukup banyak (diatas 20 lebih) maka kegiatan penggunaan combo box tidak lagi praktis dan efisien.

Permasalahan ini bermula dari artikel saya sebelumnya yaitu pembuatan stock card digital, dimana jika digunakan untuk lebih dari 100 jenis barang maka penggunaan combo box tidak lagi praktis dan efisien. Maka diperlukan trik tambahan agar bisa mempercepat hal ini. Yang ingin dicapai kali ini adalah bagaimana agar combo box bisa diketik dan muncul suggestion sesuai input yang kita masukkan.

Combo box sebenarnya bisa di modifikasi lebih lanjut agar bisa berfungsi seperti field search yang biasa anda temui pada mesin pencari google.

Kita akan melakukan modifikasi tambahan pada combo box yang ada di excel agar bisa menampilkan hasil pencarian berdasarkan karakter yang kita input.

Langkah pertama

Silahkan download file contoh yang berisikan daftar pusat pemerintahan di Indonesia (jumlahnya 400 kota) disini

Langkah ke dua

Masukkan combo box ke dalam kertas kerja kita dengan cara pilih menu developer > Pilih Insert > Pilih Combo box ( ActiveX Control)

Atur letak combo box yang telah dibuat agak kekanan , karena kita akan menggunakan kolom B, C, dan D untuk memunculkan kata-kata sugestion/rekomendasi.

Langkah ke tiga

Kita akan membuat combo box tersebut berhubungan dengan cell B1. Caranya lakukan klick kanan pada combo box > lalu pilih Properties , kemudian akan muncul windows properties,

lalu carilah baris yang bertuliskan LinkedCell, dah beri isian B1 ( artinya kita membuat hubungan antara combo box yang kita buat dengan cell B1)

Langkah ke empat

Pada Cell B2 beri rumus seperti dibawah ini

=--ISNUMBER(IFERROR(SEARCH($B$1;A2;1);""))

Lalu copy paste dari cell B2 s/d B401

Sampai disini jika ada yang bingung maksud rumus di atas, ijinkan saya membahasnya sebentar, maksud rumus diatas adalah agar setiap karakter yang ada di B1 akan dicarikan ke daftar data, untuk kemudian menghasilkan angka 1 dan 0

Rumus IFERROR berfungsi sebagai penyaring, jika terdapat kesalahan maka outputnya di rubah menjadi tidak ada,

Sedangkan maksud dari tanda minus (-) dua kali didepan ISNUMBER adalah agar merubah output TRUE dan FALSE menjadi angka 1 & 0

Langkah ke lima

Pada cell C2 masukkan fungsi seperti dibawah ini dan copy paste sampai ke bawah baris C401:

=IF(B2=1;COUNTIF($B$2:B2;1);"")

Maksud rumus diatas adalah apabila baris B bernilai 1 maka akan dihitung jumlahnya jika tidak bernilai 1 maka tidak ada hasil.

Langkah ke enam

Pada cell D2 masukkan fungsi seperti dibawah ini dan copy paste sampai ke bawah baris D401 :

=IFERROR(INDEX($A$2:$A$401;MATCH(ROWS($C$2:C2);$C$2:$C$401;0));"")

Maksud rumus diatas adalah agar terbentuk populasi daftar yang terfilter berdasarkan karakter yang di input pada B1.

Langkah Ke Tujuh

Buatlah name manager dengan cara mengakses menu FORMULAS > lalu pilih Name Manager

Selanjutnya window Name Manager akan muncul, lalu klick tombol New…

Selanjutnya isikan field yang kosong seperti dibawah ini :

·         Name : rangepopulasi

·         Scope : Workbook

·         Refers to : =$D$2:INDEX($D$2:$D$401;MAX($C$2:$C$401);1)

Lalu Klick OK

Fungsi “refers to “ diatas berfungsi untuk membuat dynamic range dengan bantuan rumus index.

Langkah ke Delapan

Dilangkah ke delapan ini kita kembali lagi ke combobox yang telah kita buat sebelumnya.

Klick kanan combo box lalu akses kembali menu properties

Lakukan perubahan terhadap 4 item dibawah ini

• AutoWordSelect: False
• LinkedCell: B1
• ListFillRange: rangepopulasi
• MatchEntry: 2 – fmMatchEntryNone

Tips : Jika anda tidak bisa melakukan klick kanan pada combo box, masuklah ke Design mode dengan cara melakukan klick Design Mode di menu DEVELOPER

Langkah ke Sembilan

Ini adalah langkah terakhir, yang tinggal anda lakukan adalah klick kanan tabulasi worksheet> pilih View Code

Jendela Visual basic akan terbuka dan paste kan perintah dibawah ini :

Private Sub ComboBox1_Change()

ComboBox1.ListFillRange = "rangepopulasi"

ComboBox1.DropDown

End Sub

Lalu perhatikan Hasilnya, kita lakukan ujicoba dengan melakukan pengetikan Huruf “Ag” pada combo box, hasilnya seperti dibawah ini :

Selamat saya ucapkan kepada anda apabila telah menyelesaikannya. Untuk memperindah tampilan sebaiknya semua komponen yang mengandung rumus di hide atau di beri font berwarna putih.

Seperti biasa file diatas yang telah jadi dapat anda download disini.