CARA MEMBUAT DATA VALIDATION LIST EXCEL 2 KOLOM [MACRO VBA EXCEL TUTORIAL SERIES] – LANJUTAN – Belajar Microsoft Excel : Tips, Tricks & Tutorial

CARA MEMBUAT DATA VALIDATION LIST EXCEL 2 KOLOM [MACRO VBA EXCEL TUTORIAL SERIES] – LANJUTAN

Assalamualaikum Warohmatullahi Wabarokatuh

Sodara2 sekaliYan para pembatja jang boediman … sesuai judul artikel ini menyambung tulisan saya sebelumnya yang bisa sOdara baca dengan ceKlik disini …. langkah-langkah validasi data tipe List yang bisa menampilkan daftar yang bersumber dari 2 kolom tanpa menggunakan macro VBA Excel telah dibahas pada artikel tersebut …. sebagai kelanjutannya artikel ini akan membahas langkah membuat validasi datanya jika menggunakan VBA

jika sOdara telah membaca artikel sebelumnya dan telah mendownload contoh file Validasi2Kolom.xlsm namun masih ada kesulitan mempelajarinya secara mandiri sOdara bisa mengikuti langkah-langkah membuat validasi data tipe List yang bersumber dari 2 kolomlangkah pertama yang perlu sOdara lakukan adalah bikin validasi list untuk kolom2 seperti gambar2 berikut ini (dalam file contoh ada pada sheet “Validasi List + VBA”)

Validasi Data 2 Kolom

Kolom B sodara pakai formula yang sama seperti validasi pada Sheet “Validasi List Standar” kolom G … bedanya pada sheet ini sOdara harus UnCheck In-Cell dropdown agar dropdownnya tidak tampil saat cell aktip

Validasi Data 2 Kolom

Kemudian pada kolom D pakai formula yang sama seperti validasi pada Sheet “Validasi List Standar” kolom I … jangan lupa harus UnCheck In-Cell dropdown

Validasi Data 2 Kolom

berikutnya Kolom F sodara bikin validasi yang sama seperti kolom B … dan kolom G validasinya sama dengan kolom D …. tambahannya pada tab Input Message Uncheck “Show input message…..” dan pada bagian title isikan  angka 1 untuk Kolom F dan angka -1 untuk kolom G

Angka 1 dan -1 diatas digunakan sebagai pengaturan untuk menentukan letak kolom pasangannya … seperti sOdara lihat kolom F dan G berpasangan dan letaknya bersebelahan … dan angka 1 dan -1 tersebut nanti akan diproses dalam prosedur VBA untuk menentukan letak kolom pasangannya … penjelasannya sebagai berikut

angka 1 untuk Kolom F maksudnya bahwa kolom pasangannya (Kolom G) terletak 1 kolom ke kanan

angka -1 untuk Kolom G maksudnya bahwa kolom pasangannya (Kolom F) terletak 1 kolom ke kiri

Berikutnya karena dropdown bawaan Data Validation List tidak bisa menampilkan item2 dari 2 kolom yang berbeda … maka sOdara bisa menggantinya dengan control ComboBox yang ada pada Form Controls … caranya ceKlik Tab Developer >> Controls >> Insert >> Form Controls >> ComboBox … seperti gambar berikut’ ini

Nama control yang baru ditambahkan untuk mengganti dropdown bawaan Validation List adalah “Drop Down 1” kalau sOdara kasih nama lain juga ndak apa2 … setelah itu masuk ke VB Editor … boleh tekan Alt + F11 untuk shortcutnya

setelah masuk ke VB Editor … pada Sheet “Validasi List + VBA” event Selection Change dan Before Double Click bikin prosedur untuk mengisi List pada “Drop Down 1” yang baru ditambahkan dan menampilkannya jika Cell Aktip divalidasi tipe List dengan formula … seperti penampakan kode berikut

VBA Excel Data Validation

Kode dan terjemahan singkatnyah sebagai berikut

Option Explicit
➡ pilihan agar semua Variabel harus/wajib dideklarasikan dengan statemen Dim atau Redim … jika ada variabel yang tidak dideklarasikan akan memicu error … syntax diatas tidak ditulis maka variabel boleh dideklarasikan boleh tidak … sebaiknya sih deklarasikan semua variabel

——————————————————————————————————————————–

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
➡ prosedur yang akan dijalankan saat event BeforeDoubleClick terjadi … yaitu sesaat sebelum sheet di dobel ceKlik

Call Worksheet_SelectionChange(Target)
➡ panggil/jalankan prosedur Worksheet_SelectionChange

End Sub
➡ akhir prosedur Worksheet_BeforeDoubleClick

——————————————————————————————————————————–

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
➡ prosedur yang akan dijalankan saat event SelectionChange terjadi … yaitu saat Active Cell / cell aktip berubah posisinya … misal dari A1 ke B5

Dim i As Integer
➡ deklarasi variabel i dengan type data Integer

On Error GoTo Akhir
➡ jika terjadi error prosedur akan loncat ke baris yang ditandai dengan “Akhir:”

With ActiveSheet
➡ pembatasan pada ActiveSheet / Sheet Aktip

.Shapes(“Drop Down 1“).Visible = False
➡ sembunyikan obyek Drop Down 1

If Target.Validation.Formula1 <> “=ID_LIST” And _
Target.Validation.Formula1 <> “=OFFSET(ID_LIST;0;1)” Then Exit Sub
➡ jika cell aktip TIDAK dipasang validasi dengan formula “=ID_LIST” atau “=OFFSET(ID_LIST;0;1)” maka keluar / akhiri prosedur

With .Shapes(“Drop Down 1”).ControlFormat
➡ pembatasan pada ControlFormat obyek “Drop Down 1”

.RemoveAllItems
➡ hapus semua item dari obyek “Drop Down 1”

i = 0
➡ tetapkan nilai variabel i

Do
➡ lakukan pengulangan prosedur antara Do … Loop

If [ID_LIST].Offset(i, 0).Resize(1, 1).Value _
= Empty Then Exit Do
➡ jika ditemukan cell kosong pada kolom ID maka keluar / akhiri pengulangan Do … Loop


.AddItem [ID_LIST].Offset(i, 0).Resize(1, 1) & ” | ” & _

[ID_LIST].Offset(i, 1).Resize(1, 1)
➡ tambahkan item “ID | Nama” ke List obyek “Drop Down 1”

i = i + 1
➡ tetapkan nilai variabel i berikutnya

Loop
➡ batas prosedur yang akan diulang dengan Do … Loop

End With
➡ akhiri pembatasan pada ControlFormat obyek “Drop Down 1”

.Shapes(“Drop Down 1”).Top = Target.Top
.Shapes(“Drop Down 1”).Left = Target.Left
➡ sesuaikan posisi obyek “Drop Down 1” sesuai dengan posisi cell aktipsesuai jarak cell aktip dari tepi atas dan dari tepi kiri

.Shapes(“Drop Down 1”).Height = ActiveCell.Height
➡ sesuaikan tinggi obyek “Drop Down 1” dengan tinggi cell aktip

.Shapes(“Drop Down 1”).Width = Application.Max(ActiveCell.Width + 15, _
[ID_LIST].Parent.[A:B].Width)
➡ sesuaikan lebar obyek “Drop Down 1” dengan tinggi cell aktip

.Shapes(“Drop Down 1”).Visible = True
➡ tampilkan obyek “Drop Down 1”

End With
➡ akhiri pembatasan pada ActiveSheet / Sheet Aktip

Akhir:
➡ penanda baris “Akhir:” (lihat kodeOn Error GoTo Akhir” diatas)

End Sub
➡ akhir prosedur Worksheet_SelectionChange

dengan prosedur diatas kita sudah bisa menampilkan satu kontrol ComboBox yang bisa menggantikan DropDown List yang berasal dari Data Validation Excel … seperti penampakan berikut

Data Validation 2 Columns

namun prosedur yang diperlukan tidak berhenti sampai disini saja … kita masih perlu bikin prosedur untuk memasukkan data item yang dipilih dari obyek Drop Down 1 ke cell yang sesuai … saya bikin prosedurnya di Module1 dan saya kasih nama WriteIt … seperti biasa kalau sodara pengen nama laen jugak bisa

Sub WriteIt()
➡ Nama prosedur untuk memasukkan data ID dan/atau NAMA ke kolom yang sesuai

Dim iSplit As Integer, Kol1 As Variant, Kol2 As Variant
➡ deklarasi variabel iSplit untuk menampung nilai posisi tanda “|” pada item list yang dipilih … variabel
Kol1 untuk menampung nilai ID … variabel Kol2 untuk menampung data NAMA

With ThisWorkbook.Sheets(“Validasi List + VBA”). Shapes(“Drop Down 1”).ControlFormat
➡ batasi pada format kontrol obyek Drop Down 1

iSplit = Application.WorksheetFunction.Search(” | “,.List(.ListIndex), 1)
➡ tetapkan nilai variabel iSplit

Kol1 = Trim(Left(.List(.ListIndex), iSplit))
➡ tetapkan nilai variabel Kol1

Kol2 = Trim(Right(.List(.ListIndex), Len(.List(.ListIndex)) – iSplit – 2))
➡ tetapkan nilai variabel Kol2

If ActiveCell.Validation.Formula1 = “=ID_LIST” Then
➡ periksa apakah pada cell aktip diterapkan validasi list dengan formula “=ID_LIST” ???

ActiveCell.Value = Kol1
➡ jika pada cell aktip diterapkan validasi list dengan formula “=ID_LIST” masukan nilai variabel Kol1 ke cell aktip

If ActiveCell.Validation.InputTitle <> “” Then
➡ periksa apakah Input Title pada validasi cell aktip tidak kosong ???

ActiveCell.Offset(0, CInt(ActiveCell.Validation.InputTitle)).Value = Kol2
➡ jika Input Title tidak kosong masukan nilai variabel Kol2 ke cell pada baris yang sama kolom pasangannya dengan … Posisi cell pasangannya berdasarkan nilai Input Title

End If
➡ akhiri If ActiveCell.Validation.InputTitle <> “” Then

ElseIf ActiveCell.Validation.Formula1 = “=OFFSET(ID_LIST;0;1)” Then
➡ periksa apakah pada cell aktip diterapkan validasi list dengan formula “=OFFSET(ID_LIST;0;1)” ????

ActiveCell.Value = Kol2
➡ jika pada cell aktip diterapkan validasi list dengan formula “=OFFSET(ID_LIST;0;1)” masukan nilai variabel Kol2 ke cell aktip

If ActiveCell.Validation.InputTitle <> “” Then
➡ periksa apakah Input Title pada validasi cell aktip tidak kosong ???

ActiveCell.Offset(0, CInt(ActiveCell.Validation.InputTitle)).Value = Kol1
➡ jika Input Title tidak kosong masukan nilai variabel Kol1 ke cell pada baris yang sama kolom pasangannya dengan … Posisi cell pasangannya berdasarkan nilai Input Title

End If
➡ akhir If ActiveCell.Validation.InputTitle <> “” Then

End If
➡ akhir If ActiveCell.Validation.Formula1 = “=ID_LIST” Then

ThisWorkbook.Sheets(“Validasi List + VBA”).Shapes(“Drop Down 1”).Visible = False
➡ sembunyikan obyek Drop Down 1

End With
➡ akhir With ThisWorkbook.Sheets(“Validasi List + VBA”). Shapes(“Drop Down 1”).ControlFormat

End Sub
➡ akhir prosedur  WriteIt

prosedur diatas kemudian diAssign ke obyek Drop Down 1 … sehingga saat salah satu item dalam List dipilih kode tersebut diatas akan dijalankan

langkah-langkah membuat validasi data tipe List yang bersumber dari 2 kolom dengan VBA telah berakhir sudah sampai disini 🙂 🙂 🙂 🙂 … sOdara akan bisa menampilkan sebuah dropdown list yang mirip Data Validation List bawaan excel dengan satu kelebihan dapat menampilkan List yang bersumber dari 2 kolom … dan juga item yang dipilih nantinya bisa diatur data yang akan dipakai … data ID saja (kolom B) … data NAMA saja (Kolom D) … atau dua2nya  ID dan NAMA yang akan dimasukkan ke dua kolom yang berbeda (Kolom F dan G) … untuk perbandingannya bisa sOdara lihat penampakan berikut

selanjutnya jika sOdara masih sedikit bingung bagaimana cara kerjanya … silahkan sOdara ikuti jalan cerita dari kisah yang panjang diatas 🙂  🙂  🙂 sebagai berikut

  • pada saat sheet Validasi List + VBA aktip dan terjadi perubahan posisi cell aktip misal dari A1 ke B5 maka prosedur Worksheet_SelectionChange akan terpicu
  • obyek Drop Down 1 akan disembunyikan
  • program kemudian akan memeriksa apakah cell aktip / Target Cell dipasang validasi dengan formula “=ID_LIST” atau “=OFFSET(ID_LIST;0;1)” … jika Tidak prosedur akan berakhir disini … jika Ya prosedur akan dilanjutkan ke baris berikutnya
  • hapus semua item dari obyek Drop Down 1
  • isi List baru pada obyek Drop Down 1 dengan daftar yang ada pada kolom ID dan Nama
  • atur posisi obyek Drop Down 1 sesuai letak cell aktip
  • atur ukuran obyek Drop Down 1 sesuai ukuran cell aktip
  • tampilkan obyek Drop Down 1 
  • pilih salah satu item dalam daftar … langkah ini akan memicu prosedur WriteIt untuk dijalankan
  • prosedur WriteIt akan memisahkan data item yang dipilih menjadi data ID (variabel Kol1) dan NAMA (variabel Kol2)
  • prosedur kemudian akan memeriksa validasi data yang diterapkan pada cell aktip
  • jika formula yang digunakan “=ID_LIST” data yang dimasukkan ke cell aktip adalah  ID (variabel Kol1) … dan jika “=OFFSET(ID_LIST;0;1)” data yang dimasukkan ke cell aktip adalah NAMA (variabel Kol2)
  • selanjutnya Input Title dari validasi cell aktip juga akan diperiksa … jika tidak kosong seperti pada validasi Kolom F dan G maka prosedur akan memasukkan data pasangannya ke kolom yang sesuai
  • sembunyikan obyek Drop Down 1
  • dah selesai 🙂

lalu kapan prosedur Worksheet_BeforeDoubleClick beraksi ❓  ❓ ❓ ❓ … prosedur ini berguna saat kita pengen mengedit data yang terlanjur di klik … misal pada cell A5 baru saja kita klik item yang salah dan obyek Drop Down 1 terlanjur disembunyikan … untuk menampilkannya lagi sodara bisa lakukan dobel ceKlik pada cell A5 … insyaAllah obyek Drop Down 1 akan nongol lagi … lebih mudah dari pada sOdara memindahkan kursor ke cell lain dan kemudian mengembalikannya ke cell A5 😉

demikiYan lah artikel lanjutan tentang langkah-langkah membuat validasi data tipe List yang bersumber dari 2 kolom menggunakan macro VBA Excell … contoh file Validasi2Kolom.xlsm … silahkan di download gratis dari folder 2017 >> 09_SEP … sekiyan semoga manpaat dan MDLMDL  🙂 🙂 🙂 😉

bingung cara downloadnya ??

Baca aja halaman download dengan klik icon di samping

feed , email and my social media

Jika sOdara menemukan sesuatu yang bermanpaat di marih .... silahkan kasih komennya