Sadhu

Sadhu

Form Input untuk Tabel Excel

Penjelasan untuk project ini akan diuraikan dalam beberapa segmen seperti berikut:

1. Membuat layout dan menambahkan Controls.

2. Program validasi data inputan.

3. Memindahkan Data

4. Menyimpan Data.

5. Mari kita coba.

Perlu dicatat, pada project sample, kami tidak akan menjelaskan dasar-dasar VBA. Untuk mencari tahu tentang dasar-dasar VBA, silahkan cari di bagian Tutorials.

Project Sample akan selalu memiliki file yang dapat di download pada bagian akhir artikel.

Oke, jadi kita siap untuk membuat project sesuai permintaan.

1. Membuat layout dan menambahkan Controls.

Jika pemahaman kami benar, maka lay-out berikut mendekati permintaan. Kami modifikasi sedikit agar lebih mudah dan enak dilihat. Lihat gambar berikut:

Kami buat tabel (Tabel1) yang memiliki 11 baris dan saya bagi menjadi 3 ruang. Ruang1 memiliki 100 kolom, Ruang2 memiliki 5 kolom dan Ruang3 memiliki 12 kolom.

Tabel ini kita buat mulai dari Cell "F3" sampai dengan Cell "DR13". Baris ke 11 (Row 13) nantinya akan kita gunakan sebagai Total Data.

Sebelah kiri adalah tempat untuk Controls. Lihat gambar berikut:

Kita membutuhkan 3 buah OptionButton1 s/d 3 untuk menentukan Ruang. Sebuah SpinButton1 untuk memilih Kolom dan sebuah Label1 untuk menampilkan kolom yang dipilih oleh SpinButton. Dua buah CommandButton1 & 2 untuk perintah memindahkan data dan menyimpan. Dan terakhir, sebuah combobox1untuk memilih nama orang di Tabel2.

Semua controls yang kita gunakan adalah ActiveX controls sehingga membutuhkan penulisan baris program untuk menggunakannya.

Kita memiliki 2 buah sheet (Sheet1 untuk "Form" dan Sheet2 untuk "Data"). Pada Sheet Data, kita buat satu tabel lagi (Tabel2).

Tabel2 berisi kolom Nomor, Nama dan kolom-kolom ruang seperti pada tabel1.

Oke, lay-out dan Control sudah kita letakkan pada tempatnya. Segmen pertama selesai.

2. Program Validasi Data Inputan.

Setelah Lay-out dan controls sudah siap, selanjutnya adalah membuat code atau baris program agar controls bisa kita gunakan.

Buka Visual Basic Editor dan tampilkan code editor di Sheet1. Caranya, klik dua kali pada object Sheet1 di project explorer. Lihat gambar berikut:

Kalo code editor sudah muncul, kita harus pikirkan, bagian apa yang harus kita buat programnya terlebih dahulu.

Kita berfikir mudah saja. Kita runut sesuai proses saat menggunakan Macro ini nantinya. Kira-kira, bagian mana yang akan kita akses lebih dahulu?

Hmm.. Bagian apa ya?? ... Hmm ... Apa mengisi angka pada Cell C4?

Yup.. Itu dulu aja kita buatin programnya.

Sesuai permintaan, data yang akan diinput adalah Bilangan Asli. Well, kata Wikipedia, bilangan asli itu bilangan 1,2,3,4 dst. Ada juga yang bilang, dimulai dari 0. Tapi yang pasti, bilangan asli itu tidak pake koma. Misalkan 2,5 (dua setengah) tapi cuma mengenal 2 dan selanjutnya 3, dst.

Jadi, yang pertama akan kita lakukan adalah, membuat sebuah validasi data sehingga pengguna (user) tidak salah meng-input data. Bilangan asli itu adalah bagian positif dari Tipe Data Integer pada Visual Basic. Jadi, kita akan buat supaya, program akan memberi informasi jika user mengisi data yang salah.

Lihat Code berikut:

Private Sub Worksheet_Change(ByVal Target As Range)
'Data validation pada cell inputan C4.
If Not Intersect(Range("C4"), Target) Is Nothing Then
If IsNumeric(Range("C4")) Then
If Range("C4").Value - Int(Range("C4").Value) 0 Then
MsgBox "Masukkan Bilangan Asli"
Range("C4").Value = ""
Worksheets(1).OptionButton1.Enabled = False
Worksheets(1).OptionButton2.Enabled = False
Worksheets(1).OptionButton3.Enabled = False
Else
Worksheets(1).OptionButton1.Enabled = True
Worksheets(1).OptionButton2.Enabled = True
Worksheets(1).OptionButton3.Enabled = True
End If
Else
MsgBox "Masukkan Angka dan merupakan Bilangan Asli"
Range("C4").Value = ""
Worksheets(1).OptionButton1.Enabled = False
Worksheets(1).OptionButton2.Enabled = False
Worksheets(1).OptionButton3.Enabled = False
End If
End If
End Sub

Code di atas, kita copy di code Editor object Sheet1 (Form). Code ini akan bekerja jika ada event terhadap Sheet1 berupa "Change". Jadi, jika ada perubahan pada sheet1, maka code ini akan bekerja.

If Not Intersect(Range("C4"), Target) Is Nothing Then

Baris diatas, digunakan untuk melokalisir sensitifitas sheet terhadap perubahan. Jadi, code hanya akan di eksekusi jika ada perubahan pada Cell C4 saja.

Code berikutnya berfungsi untuk mengmastikan data yang diinput adalah Bilangan Asli. Jika bukan, maka akan muncul message: "Masukkan Bilangan Asli".

Dan jika salah, maka Cell C4 akan berisi kosong. Juga, kita buat OptionButton Enable agar bisa di klik jika sudah ada inputan.

3. Meng-aktifkan OptionButton!

Proses berikutnya setelah kita meng-inputkan data pada cell C4, adalah memilih Ruang. OptionButton1 s/d 3, masing-masing mewakili Ruang1 s/d 3. Dan saat kita memilih Ruang, maka sekaligus kita akan menentukan Angka Minimum dan Maximum yang boleh dipilih oleh Pengguna untuk menentukan Kolom. Ingat! Ruang1 berisi 100 kolom, Ruang2 berisi 5 kolom dan Ruang3 berisi 12 Kolom.

Lihat Code berikut:

 

'optionbutton1 untuk Ruang 1
isiRuang = 1
Worksheets(1).SpinButton1.Max = 100
Worksheets(1).SpinButton1.Min = 1
Worksheets(1).SpinButton1.Value = 1
Worksheets(1).SpinButton1.Enabled = True
Worksheets(1).Label1.Caption = SpinButton1.Value
Worksheets(1).CommandButton1.Enabled = True
End Sub

Private Sub OptionButton2_Click()
'optionbutton1 untuk Ruang 2
isiRuang = 2
Worksheets(1).SpinButton1.Max = 5
Worksheets(1).SpinButton1.Min = 1
Worksheets(1).SpinButton1.Value = 1
Worksheets(1).SpinButton1.Enabled = True
Worksheets(1).Label1.Caption = SpinButton1.Value
Worksheets(1).CommandButton1.Enabled = True
End Sub

Private Sub OptionButton3_Click()
'optionbutton1 untuk Ruang 3
isiRuang = 3
Worksheets(1).SpinButton1.Max = 12
Worksheets(1).SpinButton1.Min = 1
Worksheets(1).SpinButton1.Value = 1
Worksheets(1).SpinButton1.Enabled = True
Worksheets(1).Label1.Caption = SpinButton1.Value
Worksheets(1).CommandButton1.Enabled = True
End Sub

Hasil code diatas adalah, jika OptionButton1 yang dipilih, maka SpinButton1 akan memiliki value Maximum 100 dan seterusnya.

Code diatas juga membuat SpinButton dan CommandButton Enable sehingga bisa dieksekusi.

5. Apa yang dilakukan oleh SpinButton?

SpinButton1, hanya akan kita beri code untuk memindahkan value-nya ke Label1 sehingga bisa dilihat oleh pengguna.

Lihat Code berikut:

Private Sub SpinButton1_Change()
'Spinbutton untuk memilih kolom
Worksheets(1).Label1.Caption = SpinButton1.Value
End Sub

3. Memindahkan Data

Jika Data sudah kita input-kan, ruang sudah kita pilih dan kolom sudah kita tentukan, proses selanjutnya adalah memindahkan data di Cell C4 ke Tabel1 sesuai dengan Ruang, kolom dan baris yang telah ditentukan (Data akan ditempatkan pada baris yang kosong).

Lihat Code berikut:

Private Sub CommandButton1_Click()
'commandbutton untuk memindahkan data
'cek dulu apakah semua inputan sudah siap. Sebenarnya gak perlu karena Command button aktif jika inputan siap.
If Range("C4").Value "" And isiRuang 0 And Worksheets(1).Label1.Caption "" Then
Data = Range("C4").Value

'cek kolom pada excel berapa yang akan diisi
If isiRuang = 1 Then
kolom = 5 + Worksheets(1).SpinButton1.Value
ElseIf isiRuang = 2 Then
kolom = 5 + 100 + Worksheets(1).SpinButton1.Value
ElseIf isiRuang = 3 Then
kolom = 5 + 100 + 5 + Worksheets(1).SpinButton1.Value
End If

'cek baris keberapa yang kosong
If Cells(3, kolom).Value = "" Then
baris = 3
Else
If Cells(4, kolom).Value = "" Then
baris = 4
Else
Range(Cells(3, kolom), Cells(3, kolom)).Select
Selection.End(xlDown).Select
baris = 1 + Selection.Row
End If
End If

'pindahkan data
If baris < 13 Then
Cells(baris, kolom).Value = Data

'melakukan penjumlahan kolom
isisubtotal = WorksheetFunction.Sum(Range(Cells(3, kolom), Cells(12, kolom)))
Cells(13, kolom).Value = isisubtotal

'setelah data dipindahkan, reset form inputan
Range("C4").Value = ""
Worksheets(1).OptionButton1.Value = False
Worksheets(1).OptionButton2.Value = False
Worksheets(1).OptionButton3.Value = False
Worksheets(1).SpinButton1.Value = 1
Worksheets(1).Label1.Caption = ""
isiRuang = 0

'men-disable semua control
Worksheets(1).OptionButton1.Enabled = False
Worksheets(1).OptionButton2.Enabled = False
Worksheets(1).OptionButton3.Enabled = False
Worksheets(1).SpinButton1.Enabled = False
Worksheets(1).CommandButton1.Enabled = False

'mengisi combobox1
'hitung jumlah orang terdaftar
If Worksheets(2).Cells(2, 2).Value = "" Then
jumlahorang = 0
Else
Set myRange = Worksheets(2).Range("B:B")
jumlahorang = Application.WorksheetFunction.CountA(myRange) - 1
End If
'mulai isi combobox1
If jumlahorang > 0 Then
Worksheets(1).ComboBox1.Clear
For i = 1 To jumlahorang
Worksheets(1).ComboBox1.AddItem (Worksheets(2).Cells(i + 1, 2).Value)
Next i
End If

Else
MsgBox "Maaf, anda telah memasukkan 10 baris!"
End If
End If

End Sub

Yang dilakukan code diatas adalah:

- Menentukan kolom keberapa data akan dituliskan. Jika pilihan kita tadi Ruang3, maka Data akan kita tuliskan di kolom: 5 (tempat controls) + 100 (kolom-kolom ruang1) + 5 (kolom-kolom ruang2) + value SpinButton.

- Selanjutnya, dibaris keberapa data akan dituliskan. Data akan dituliskan dibaris yang masih kosong pada kolom pilihan. Maka, code diatas mencari baris terakhir yang memiliki "Value". Jadi, data akan ditulis pada baris terakhir yang memiliki "Value" + 1.

- Jika Kolom dan Baris telah dihitung, maka code: Cells(baris, kolom).Value = Data, berfungsi untuk menyalin data di Cell C4 ke Cell dengan baris dan kolom yang kita hitung sebelumnya.

- Berikutnya, kita harus menjumlahkan data tiap-tiap kolom (dari baris 1 hingga ke 10) menjadi Total Data dan ditampilkan ke Baris ke sebelas (Row 13).

- Dan baris maksimum adalah 10 baris! Sehingga kita batasi dengan code: If baris < 13 Then. Jika baris masih dibawah 13 (mulai dari row 3, sehingga jumlah baris adalah 10). Jika telah melewati, maka kita beri pesan: MsgBox "Maaf, anda telah memasukkan 10 baris!".

- Jika data telah dipindahkan, maka semua controls harus dimatikan. Semua harus diset kembali ke kondisi inisial.

- Selain itu, kita harus menyiapkan combobox1. Kita harus mengisi (additem) combobox1 dengan nama-nama orang di Tabel2 (di sheet "Data"). Sehingga, saat di klik, maka dropdown akan berisi nama-nama orang.

4. Menyimpan Data.

Proses selanjutnya, jika data telah komplit adalah menyimpannya ke Database sesuai dengan nama orang-orang tertentu.

Yang perlu dilakukan pengguna hanyalah memilih nama orang di Combobox dan selanjutnya menekan CommandButton2. Maka otomatis, data akan tersimpan di tabel2 pada Sheet "Data".

Lihat Code berikut:

Private Sub CommandButton2_Click()
'commandbutton ini untuk menyimpan data
If Worksheets(1).ComboBox1.Value "" Then
'caribaris tempat nama yang dituju di tabel 2
Namaorang = Worksheets(1).ComboBox1.Value
With Worksheets(2).Range("B:B")
Set c = .Find(Namaorang, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
barisorang = c.Row

Loop While Not c Is Nothing And c.Address firstAddress
End If
End With

'ambil data total (baris 13)

For i = 1 To 117
Worksheets(2).Cells(barisorang, i + 2).Value = Cells(13, 5 + i).Value
Next i

Range("F3:DR13").ClearContents
MsgBox "Data telah disimpan. Terima kasih"
Else
MsgBox "Pilih nama!!"
End If
End Sub

Code diatas melakukan hal-hal berikut:

- Memastikan combobox1 berisi nama (pengguna sudah memilih nama). Jika tidak berisi apa-apa, maka kita memberikan pesan: MsgBox "Pilih nama!!".

- Selanjutnya, code diatas akan mencari nama yang tertera pada combobox1. Jika nama ditemukan, maka code mencatat letak nama tersebut di baris keberapa.

- Dengan melakukan looping sebanyak 100 + 5 + 12 (sesuai jumlah kolom Ruang1 + Ruang2 + Ruang3) : 117 kali, data satu-persatu disalin dari Baris ke 11 (Row 13) pada sheet1 dan dicopy ke Sheet2.

- Setelah semua telah disalin di sheet2, selanjutnya Cell F3 sampai dengan DR13 di "Clear"-kan dari contents.

- Dan terakhir, kita memberikan pesan "Data telah tersimpan".

5. Mari kita coba.

Sebenarnya, pembuatan Macro telah selesai. Namun, agar macro lebih stabil, kita harus memastikan semua controls telah siap saat pertama kali File dibuka.

Untuk kebutuhan tersebut, maka kita meletakkan code pada Event Sheet1: Activate. Jadi, saat Sheet1 dibuka/di-activate, maka code dibawah akan dieksekusi. Code ini hanya untuk mennyiapkan controls.

Misalkan, mematikan (men-disable) OptionButton, mengkondisikan Label dan Cell C4 menjadi kosong, dan juga mengisi combobox1.

Lihat Code berikut:

Private Sub Worksheet_Activate()
'Pada saat Sheet Form aktif, membuat inisial value semua control
Range("C4").Value = ""
Worksheets(1).OptionButton1.Value = False
Worksheets(1).OptionButton2.Value = False
Worksheets(1).OptionButton3.Value = False
Worksheets(1).SpinButton1.Value = 1
Worksheets(1).Label1.Caption = ""
isiRuang = 0

'Pada saat Sheet Form aktif, men-disable semua control
Worksheets(1).OptionButton1.Enabled = False
Worksheets(1).OptionButton2.Enabled = False
Worksheets(1).OptionButton3.Enabled = False
Worksheets(1).SpinButton1.Enabled = False
Worksheets(1).CommandButton1.Enabled = False

'mengisi combobox1
'hitung jumlah orang terdaftar
If Worksheets(2).Cells(2, 2).Value = "" Then
jumlahorang = 0
Else
Set myRange = Worksheets(2).Range("B:B")
jumlahorang = Application.WorksheetFunction.CountA(myRange) - 1
End If
'mulai isi combobox1
If jumlahorang > 0 Then
Worksheets(1).ComboBox1.Clear
For i = 1 To jumlahorang
Worksheets(1).ComboBox1.AddItem (Worksheets(2).Cells(i + 1, 2).Value)
Next i
End If

End Sub

Selain itu, kita juga harus mempertimbangkan jika pengguna menembah atau mengurangi nama orang pada tabel2 (sheet2). Maka, setelah di-update, combobox1 yang memuat nama-nama orang pun harus diisi ulang.

Code berikut ditulis pada code editor di Object Sheet2.

Lihat Code berikut:

Private Sub Worksheet_Change(ByVal Target As Range)
'untuk mengupdate isi combobox jika ada perubahan nama.
If Not Intersect(Range("B:B"), Target) Is Nothing Then
'mengisi combobox1
'hitung jumlah orang terdaftar
If Worksheets(2).Cells(2, 2).Value = "" Then
jumlahorang = 0
Else
Set myRange = Worksheets(2).Range("B:B")
jumlahorang = Application.WorksheetFunction.CountA(myRange) - 1
End If
'mulai isi combobox1
If jumlahorang > 0 Then
Worksheets(1).ComboBox1.Clear
For i = 1 To jumlahorang
Worksheets(1).ComboBox1.AddItem (Worksheets(2).Cells(i + 1, 2).Value)
Next i
End If

End If

End Sub

Well, semua code sudah kita tulis. Sekarang, waktunya mencoba..!