Dasar Macro - Membuat Combobox bertingkat

File contoh kali ini masih berhubungan dengan Userform Combobox dan Named Range (Range yang diberi nama), dan menunjukan bagaimana penggunaan Named Range sangat membantu dalam untuk membuat nilai dalam 3 Combobox bisa saling berhubungan.

Pada animasi dibawah dicontohkan bagaimana proses memberi nama pada masing-masing grup komoditas dan jenis-jenis nya yang ada pada file contoh.

Proses menjadikan sebuah Range menjadi Named Range.

Pada saat awal Workbook dibuka, Excel langsung menampilkan Userform dengan ketiga Combobox pilihan nya. Apabila Combobox pertama tidak dipilih, maka Combobox kedua akan terkunci. Begitu juga dengan Combobox ketiga. Pilih satu nilai di Combobox kedua dulu untuk mengaktifkan Combobox ketiga. Hal ini dilakukan melalui pengaturan property Enabled.

Tampilan Userform

Untuk mengatur data dari masing-masing Combobox digunakan statement SELECT CASE untuk memilih RowSource / Sumber datanya. Tinggal di cocok kan sesuai pilihan dengan Named Range yang sudah kita tentukan sebelumnya.

Mudah-mudahan penjelasan ala kadar nya ini dapat diterima ya ? :)

Untuk lebih jelasnya, Silahkan download file contoh nya disini.

Tips Dasar Macro - Cara mudah mengisi multi kolom Combobox

Combobox adalah satu dari sekian banyak control yang paling sering digunakan untuk menampilkan kumpulan data atau mungkin hanya sekedar menampilkan beberapa baris pilihan. 

Ada banyak cara untuk mengupdate / mengisi sebuah Combobox pada sebuah Userform dengan yang ada pada sebuah Range. Coba tuliskan di Google kata "populate Combobox with values from range", hasil nya pasti banyak :)

Di tips kali ini saya hanya akan membahas 2 cara yang saya anggap paling mudah untuk mengisi sebuah Combobox dengan nilai yang berasal dari sebuah Range yang baris nya bersifat dinamis.

Untuk lebih jelas nya, download dulu contoh file nya disini.

Cara pertama (sheet Combobox1) :

Private Sub UserForm_Initialize()

    Application.ScreenUpdating = False 

    

    Dim Baris As Integer 

    

    Baris = WorksheetFunction.CountA(Sheets("ComboBox1").Range("A:A"))

    Me.cbo1.ColumnCount = 3

    Me.cbo1.BoundColumn = 1

    Me.cbo1.ColumnHeads = True

    

    Me.cbo1.RowSource = "=Combobox1!A2:C" & Baris

End Sub

1. Skrip untuk mengisi Combobox diatas, di letakan pada event Form_Initilize. Arti nya, skrip akan di jalankan / di aktifkan di awal form di buka.
2. Inti dari skrip ini ada pada baris ke tiga, yaitu pada baris (yang di highlight kuning) Baris = worksheetFunction...
3. Di baris tersebut menggunakan fungsi Counta, yaitu mencari berapa banyak baris yang tidak kosong dalam Kolom A pada sheet "ComboBox1" dan hasil nya dimasukan ke variabel bernama Baris. Dari sini kita tau sampai sejauh mana banyak data nya.
4. Baris ke 4 - 6 di dalam prosedur berfungsi untuk merubah property dari ComboBox yang bernama cbo1
5. Baris terakhir di dalam prosedur juga merubah property ComboBox, dan untuk kali ini adalah property RowSource.
6. Property RowSource berfungsi sebagai sumber data. Untuk mengetahui sejauh mana data nya berada, maka dengan menggabungkan nya dengan nilai variabel Baris tadi kita dapat menjadikan Combobox nya selalu dinamis.

Cara kedua (sheet Combobox2) :

Cara ini terbilang lebih praktis. Yang paling penting adalah menyiapkan range yang akan digunakan untuk mengisi Combobox terlebih dahulu menjadi sebuah Tabel (Tab Home -> Styles -> Format as Table. Jangan lupa cek tanda "My Table Has Headers") dan range tersebut haruslah dijadikan sebuah range yang mempunyai nama (Named Range). Dalam contoh, nama range nya adalah "DBase".

Dengan menjadikan nya menjadi sebuah Tabel dan range yang mempunyai nama, maka proses mengisi data ComboBox lebih mudah dan dapat menjadi sebuah list yang dinamis.

Selanjutnya penjelasan mengenai skrip nya :

Private Sub UserForm_Initialize()

    Me.Cbo2.ColumnCount = 3

    Me.Cbo2.BoundColumn = 1

    Me.Cbo2.ColumnHeads = True

    

    Me.Cbo2.RowSource = "=DBase"

End Sub

1. Sama seperti cara pertama, skrip diletakan di event Form_Initialize
2. Baris pertama sampai dengan ke tiga adalah bagaimana merubah property control ComboBox bernama Cbo2
3. Inti dari proses mengisi Combobox nya sendiri ada dibaris ke empat yaitu Me.Cbo2.RowSource = "=DBase"
4. Property RowSource dapat langsung tuliskan sesuai nama yang kita berikan sebelumnya, yaitu "DBase"

Silahkan pilih cara mana yang menurut anda lebih mudah :)

Tips - Mendapatkan nilai di "persimpangan" (intersection)

Ternyata, operator yang dapat digunakan di dalam Excel tidak cuma operator yang selama ini kita kenal seperti = + - / * & dan lain-lain nya itu. Karakter spasi (dengan menekan tombol spacebar) bisa digunakan untuk mendapatkan sebuah nilai pada dua buah Range yang saling berpotongan.

Pada contoh screen cap dibawah, saya menggunakan sebuah tabel data penjualan setiap Sales untuk masing-masing produk. Ada Sales, Produk 1 sampai dengan Produk 3 sebagai Header / Judul di Range A1 sampai dengan D1. Dan data nya sendiri mulai dari A2 sampai dengan D5.

Untuk mendapatkan nilai penjualan Sales bernama Joko untuk Produk 2, rumus nya adalah (tanpa tanda kutip) "=c2:c5 A4:D4". Ingat ya.. antara c5 dan A4 ada Spasi. Setelah kita Enter, maka akan muncul nilai 16. 

Untuk lebih jelas nya coba tekan tombol F2. Maka akan terlihat bahwa range dalam rumus saling berpotongan, dan titik temu nya ada di cell C4. Gampang kan ?

Coba bereksperimen dengan nilai lain ya ? :).. have a nice weekend.

Tips - Mengakali (Hack) Data Validation

Keberhasilan mengolah data dengan Excel tidak luput dari data yang di masukan / di input. Artinya, kalau data yang di input salah, maka laporan yang dihasilkan akan salah.

Salah satu metode untuk mengurangi kemungkinan salah input adalah dengan menggunakan Data Validation (Tab Data - Data Validation - List).

Tips kali ini ditujukan untuk mereka yang sudah paham menggunakan Data Validation dan bukan untuk di "salah-gunakan" lho ya ?. Penulis hanya ingin menunjukan bahwa dengan menggunakan Data Validation pun sebenar nya kemungkinan untuk bisa di "akali" masih ada.

Well, sependek pengetahuan penulis memang Microsoft tidak pernah mengklaim bahwa Microsoft Excel adalah aplikasi yang sempurna :)

Untuk mengetahui kelemahan Data Validation cara nya adalah :

1. Siapkan Range Data Validation nya menggunakan metode List
2. Coba lah untuk isi cell dengan Nilai yang tidak ada di dalam List
3. Pada waktu keluar Notifikasi bahwa nilai tersebut tidak Valid, klik Cancel
4. Tuliskan nilai yang tidak ada di dalam List tadi di cell mana saja
5. Copy nilai tersebut dengan melalui Formula Bar (Blok dulu baru Ctrl + C)
6. Paste nilai tersebut pada cell yang di inginkan
7. Enter

Nilai akan tetap bisa di input tanpa ada Notifikasi bahwa nilai yang kita masukan tidak valid.

Tips - Ubah tampilan chart menggunakan file gambar

Ada kalanya kita ingin menampilkan sebuah Chart (khususnya Bar Chart) dengan tampilan yang lain.

Tips kali ini mencoba menggantikan nilai / series dari sebuah Bar Chart dengan menggunakan file gambar (contoh menggunakan gambar Koin) untuk menghasilkan tampilan yang "beda" dari Bar Chart biasa nya.

Berikut adalah langkah-langkah nya :

1. Setelah Bar Chart nya siap, copy file gambar nya (Ctrl + C)
2. Jangan lupa, klik dulu pada nilai / series nya dulu, baru di paste (Ctrl + V)
3. Liat tampilan nya sudah berbeda !
4. Catatan : kalau untuk keperluan presentasi, liat-liat boss nya dulu yah ? bisa ada yang suka, bisa juga nggak :)

Gampang kan ? :).. silahkan dicoba.

tested on Ms Excel 2010 & 2007.

Tips - Membatasi Scroll Area (dengan sedikit Macro)

Masih soal membatasi akses user nih :) 

Hal ini untuk "menyelamatkan" data yang kita sudah buat tentunya supaya aman.

Tips kali ini menunjukan gimana caranya untuk membatasi scroll area / sejauh mana kursor bisa bergerak dalam sebuah Sheet dengan cara menuliskan "sedikit" perintah Macro. Sedikit aja kok.. 

Di contoh kali ini, penulis menggunakan "Sheet1" dan Area nya adalah A1 sampai dengan H9 sebagai batasan nya.

Berikut langkah nya :

1. Pilih Sheet nya
2. Klik kanan pada sheet tersebut
3. Pada menu yang muncul, pilih "View Code"
4. Setelah muncul VB Editor, di jendela kanan atas ada 2 tombol dropdown / Combobox.
5. Pada combobox yang kiri, pilih Worksheet
6. Pada combobox yang kanan, secara default akan muncul tulisan SelectionChange. Klik tombol tersebut dan pilih Activate
7. Yang muncul adalah prosedur Private Sub Worksheet Activate() - kita bahas ini pada bahasan soal Macro ya ?
8. Diantara prosedur itu, tuliskan Me.Scrollarea = "A1:H9"
9. Save file - dan coba untuk buka sheet lain dulu, baru buka sheet tadi supaya Macro nya aktif.

Gampang kan ? 

Apabila ada rumus atau data diluar A1:H9, tetap aman.

Untuk mengembalikan ke keadaan semula atau tanpa batasan scroll area, tinggal hapus macro pada point ke 8.

Selamat mencoba ;)

Tested on Microsoft Excel 2010.

Tips - Menyembunyikan sheet dengan Metode "Very Hidden"

Ada saat nya kita hanya ingin menampilkan Sheet tertentu saja. Sedangkan Sheet yang lain nya, sebagai sumber data tidak perlu ditampilkan.

Lebih parah lagi, saat file kita kirimkan, ada saja user yang penasaran meng edit data pada Sheet sumber data tersebut. Alhasil, laporan jadi berantakan. Walaupun Sheet tersebut sudah di Hide.

Jangan khawatir.. masih ada jalan lain :)

Coba tips berikut :

1. Buka VB Editor dengan cara menekan tombol Alt + F11.
2. Pada kiri atas (namanya Jendela Project Explorer), akan terlihat semua Sheet yang ada pada file kita (plus tulisan ThisWorkbook).
3. Klik Sheet yang ingin kita sembunyikan.
4. Setelah di klik, pada jendela Property (kotak yang kiri bawah), scroll ke bawah sampai ketemu tulisan Visible.
5. Klik combobox nya, pilih pilihan "2 - xlSheetVeryHidden"
6. Untuk mengembalikan ke setting semua, tinggal dirubah pilihan nya ke "1 - XlSheetVisible"

Pada animasi dibawah, Sheet yang di hide adalah Sheet1.

Selamat mencoba ;)

Tips - Mewarnai setiap baris genap

Mudah-mudahan tips berikut bermanfaat untuk yang belum tahu. Di tes di Microsoft Excel 2010.

Bagaimana caranya untuk mewarnai setiap baris genap dengan warna tertentu. Berikut adalah caranya dengan menggunakan Conditional Formating :

1. Pilih range yang ingin di format (di contoh saya pilih range A1:G25)
2. Klik Conditional Formating - New Rule dan Pilih : "Use a formula to determine which cells to format"
3. Pada kotak "Format values where this formula is true :", masukan rumus (tanpa tanda kutip) "=mod(Row(),2)=0"
4. Pilih warna highlight nya dengan mengklik tombol Format
5. Klik Ok.

Pembahasan :

Formula Mod berfungsi untuk mencari sisa dari hasil sebuah operasi pembagian. Kemudian Formula Row() berfungsi untuk menampilkan nomer baris pada cell yang bersangkutan. 

Dengan gabungan rumus kedua nya diatas maka Mod mencari sisa pembagian antara nomer Baris (formula Row) dengan 2, apabila sisa nya Nol maka Conditional Formating di aktifkan.

Semoga bermanfaat dan selamat mencoba :)

Tips - Input data sekaligus di banyak Sheet tanpa Macro

Pernah dihadapkan pada kasus harus input data pada banyak Sheet ?

Dibawah ini adalah langkah-langkah nya :

1. Klik masing-masing Sheet yang akan di input dengan menekan tombol Ctrl
2. Setelah dipastikan Sheet nya terpilih, pastikan Workbook nya bertatus menjadi [Group] 
   
3. Cobalah untuk mulai input data nya dan coba untuk lihat hasil nya.. Gampang kan ? :)

Selamat mencoba.