Membuat Form Isian Data dengan Macros di Excel

Berikut cara membuat Form Isian Data

1. Buka Excel

2. Ubah nama Sheet1 menjadi PARTSDATA

3. Buat Heading Tabel seperti gambar dibawah ini

Simpan File dengan type 'Excel Macro Enabled Workbook' dan File Name 'Data Barang'

4. Pada menu View, pilih Macros lalu klik View Macros

5. Maka akan muncul tampilan seperti ini

Isi Macro Name dengan 'Form' lalu klik 'Create'

6. Lalu akan muncul tampilan seperti ini

7. Insert UserForm

8. Ketika Form sudah muncul, buatlah tampilan seperti ini

dengan cara:

- Gunakan Label pada toolbox untuk Kode, Nama Barang, Satuan dan Harga

Label1 Caption diganti dengan “Kode”
Label2 Caption diganti dengan “Nama Barang”
Label3 Caption diganti dengan “Satuan”
Label4 Caption diganti dengan “Harga”

- Gunakan TextBox sebagai syarat untuk pengisian data

TextBox1 Name diganti dengan “tkode”
TextBox2 Name diganti dengan “tnama”
TextBox3 Name diganti dengan “tsatuan”
TextBox4 Name diganti dengan “tharga”

- Gunakan Command Button untuk membuat 'SIMPAN' dan 'TUTUP'

command button1 Caption diganti dengan “TAMBAH”
command button1 Name diganti dengan “CMDTMBH”
command button2 Caption diganti dengan “TUTUP”
command button2 Name diganti dengan “CMDTTP”

9. Kita lanjutkan dengan pembuatan Code dengan cara, Klik kanan form dan pilih view code maka akan muncul tampilan seperti ini

10. Tuliskan Kode dibawah ini

Private Sub CMDTMBH_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(“PARTSDATA”)

‘menemukan baris kosong pada database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

‘check untuk sebuah kode
If Trim(Me.tkode.Value) = “” Then
Me.tkode.SetFocus
MsgBox “Masukan Kode Barang”
Exit Sub
End If

‘copy data ke database
ws.Cells(iRow, 1).Value = Me.tkode.Value
ws.Cells(iRow, 2).Value = Me.tnama.Value
ws.Cells(iRow, 3).Value = Me.tsatuan.Value
ws.Cells(iRow, 4).Value = Me.tharga.Value

‘clear data
Me.tkode.Value = “”
Me.tnama.Value = “”
Me.tsatuan.Value = “”
Me.tharga.Value = “”
Me.tkode.SetFocus
End Sub

11. Pengisian Kode untuk tombol tutup dituliskan dengan kode dibaawah ini

Private Sub CMDTTP_Click()
Unload Me
End Sub

12. Untuk menampilkan peringatan "MAKE TOMBOL ATUH KANG!" saat seseorang menutup melalui tanda X, buatlah kode berikut

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox “MAKE TOMBOL ATUH KANG!”
End If
End Sub

13. Klik kanan module lalu isikan kode berikut

Sub FORM()
UserForm1.Show
End Sub

14. Setelah selesai melakukan pengisian kode, kembali lagi ke Excel lalu buatlah lingkaran seperti ini dengan menggunakan fasilitas 'Shapes'

15. Setelah selesai, klik kanan tombol lalu pilih 'Assign Marco'

16. Ketika muncul tampilan seperti ini, pilih Form lalu klik OK.

17. Excel akan otomatis menampilkan ini sesuai data yang telah kita buat

18. Sesuai kode yang telah kita tuliskan, ketika seseorang menutup melalui tombol X maka akan muncul peringatan seperti ini

Macros Excel untuk Form Input Data

Macros adalah Sebuah fasilitas yang dimiliki Microsoft Excel yang dapat digunakan untuk merekam semua tindakan dan perintah yang dilakukan pada program Excel. Alat bantu yang digunakan dalam pembuatan perintah maupun perekaman Macro adalah dengan menggunakan Visual Basic Editor.

Berikut langkah-langkah membuat Form Input Data.

1. Buka Excel lalu tekan kombinasi tombolALT+F11 pada keyboard. VBA editor telah aktif dan akan muncul tampilan seperti ini

2. Pada menu insert, pilih UserForm

Lalu akan muncul tampilan seperti ini

Sesuaikan ukuran form dengan space yang dibutuhkan.

3. Lalu isikan form dengan tampilan seperti ini

dengan cara;
- gunakan Label pada ToolBox untuk membuat NIS, Nama Lengkap, Tempat Lahir, dst.

Setiap setelah membuat Label, jangan lupa untuk mengubah caption sesuai dengan data yang dibutuhkan.

- gunakan Combo Box pada ToolBox sebagai syarat untuk pengisian data pada Pendidikan Ibu, Pendidikan Ayah, dan Jenis Kelamin

Untuk ComboBox sganti properties name nya dengan
ComboBox1 ganti dengan CBOKelamin
ComboBox2 ganti dengan CBOPendidikanIbu
ComboBox3 ganti dengan CBOPendidikanAyah


- gunakan TextBox pada ToolBox sebagai syarat untuk pengisian data NIS, Nama Lengkap, Tempat Lahir, dll (kecuali Pendidikan Ibu, Pendidikan Ayah, dan Jenis Kelamin)

Textbox1 ganti dengan    TXTNis
Textbox2 ganti dengan    TXTNama
Textbox3 ganti dengan    TXTTempatLahir
Textbox4 ganti dengan    TXTTglLahir
Textbox5 ganti dengan    TXTALamat
Textbox6 ganti dengan    TXTNISN
Textbox7 ganti dengan    TXTHP
Textbox8 ganti dengan    TXTSKHUN
Textbox9 ganti dengan    TXTIjasah
Textbox10 ganti dengan  TXTNamaIbu
Textbox11 ganti dengan  TXTThnLahirIbu
Textbox12 ganti dengan  TXTPekIbu
Textbox13 ganti dengan  TXTNamaAyah
Textbox14 ganti dengan  TXTThnAyah
Textbox15 ganti dengan  TXTPekAyah
Textbox16 ganti dengan  TXTPengAyah
Textbox17 ganti dengan  TXTAlamatOrtu

- Gunakan CommandButton untuk Simpan, Cari Data Siswa, dan Close

Untuk masing-masing tombol silakan ganti properties name menjadi "TBLSimpan", "TBLCariData", CMDClose"

4. Langkah selanjutnya, View Code dengan cara Klik Kanan pada Userform dan pilih view code.
dan akan muncul tampilan seperti ini

5. Tuliskan script berikut untuk membuat initial pada UserForm

Private Sub UserForm_Initialize()
With CBOKelamin
    .AddItem "Laki-Laki"
    .AddItem "Perempuan"
End With
With CBOPendidikanIbu
    .AddItem "Tidak Sekolah"
    .AddItem "SD"
    .AddItem "SMP"
    .AddItem "SMA"
    .AddItem "D1"
    .AddItem "D2"
    .AddItem "D3"
    .AddItem "S1"
    .AddItem "S2"
    .AddItem "S3"
End With
With CBOPendidikanAyah
    .AddItem "Tidak Sekolah"
    .AddItem "SD"
    .AddItem "SMP"
    .AddItem "SMA"
    .AddItem "D1"
    .AddItem "D2"
    .AddItem "D3"
    .AddItem "S1"
    .AddItem "S2"
    .AddItem "S3"
End With
End Sub

6. Ketikkan script VBA nya di bawah ini untuk script data siswa

Private Sub TBLSimpan_Click()
Dim iRow As Long
Dim Ws As Worksheet
Set Ws = Worksheets("databasesiswa")

'menemukan baris kosong pada database siswa
iRow = Ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check untuk sebuah nis
If Trim(Me.TXTNis.Value) = "" Then
  Me.TXTNis.SetFocus
  MsgBox "Masukan NIS terlebih dahulu Kang.."
  Exit Sub
End If


'copy data ke database siswa
Ws.Cells(iRow, 1).Value = Range("X1").Value
Ws.Cells(iRow, 2).Value = Me.TXTNis.Value
Ws.Cells(iRow, 3).Value = Me.TXTNama.Value
Ws.Cells(iRow, 4).Value = Me.TXTTempatLahir.Value
Ws.Cells(iRow, 5).Value = Me.TXTTglLahir.Value
Ws.Cells(iRow, 6).Value = Me.CBOKelamin.Value
Ws.Cells(iRow, 7).Value = Me.TXTAlamat.Value
Ws.Cells(iRow, 8).Value = Me.TXTNISN.Value
Ws.Cells(iRow, 9).Value = Me.TXTHP.Value
Ws.Cells(iRow, 10).Value = Me.TXTSKHUN.Value
Ws.Cells(iRow, 11).Value = Me.TXTIjasah.Value
Ws.Cells(iRow, 12).Value = Me.TXTNamaIbu.Value
Ws.Cells(iRow, 13).Value = Me.TXTThnLahirIbu.Value
Ws.Cells(iRow, 14).Value = Me.TXTPekIbu.Value
Ws.Cells(iRow, 15).Value = Me.CBOPendidikanIbu.Value
Ws.Cells(iRow, 16).Value = Me.TXTNamaAyah.Value
Ws.Cells(iRow, 17).Value = Me.TXTThnAyah.Value
Ws.Cells(iRow, 18).Value = Me.TXTPekAyah.Value
Ws.Cells(iRow, 19).Value = Me.CBOPendidikanAyah.Value
Ws.Cells(iRow, 20).Value = Me.TXTPengAyah.Value
Ws.Cells(iRow, 21).Value = Me.TXTAlamat.Value

'clear data siswa
Me.TXTNis.Value = ""
Me.TXTNama.Value = ""
Me.TXTTempatLahir.Value = ""
Me.TXTTglLahir.Value = ""
Me.CBOKelamin.Value = ""
Me.TXTAlamat.Value = ""
Me.TXTNISN.Value = ""
Me.TXTHP.Value = ""
Me.TXTSKHUN.Value = ""
Me.TXTIjasah.Value = ""
Me.TXTNamaIbu.Value = ""
Me.TXTThnLahirIbu.Value = ""
Me.TXTPekIbu.Value = ""
Me.CBOPendidikanIbu.Value = ""
Me.TXTNamaAyah.Value = ""
Me.TXTThnAyah.Value = ""
Me.TXTPekAyah.Value = ""
Me.CBOPendidikanAyah.Value = ""
Me.TXTPengAyah.Value = ""
Me.TXTAlamatOrtu.Value = ""
Me.TXTNis.SetFocus

'Simpan data
Application.ActiveWorkbook.Save
End Sub

7. Membuat perintah CLOSE silakan ketikkan script nya dibawah ini dengan cara double klik tombol CLOSE

Private Sub CMDClose_Click()
Unload Me
End Sub

8. Membuat script agar apabila data yang dimasukan pada textbox bukan berupa data angka maka secara otomatis akan menampilkan pesan error

Private Sub HanyaAngka()
    If TypeName(Me.ActiveControl) = "TextBox" Then
        With Me.ActiveControl
            If Not IsNumeric(.Value) And .Value <> vbNullString Then
                MsgBox "Maaf, Masukan data angka saja"
                .Value = vbNullString
            End If
        End With
    End If
End Sub

9. Untuk membuat perubahan warna ketika textbox dipilih

Private Sub TXTNISN_Change()
HanyaAngka
End Sub

Private Sub TXTHP_Change()
HanyaAngka
End Sub

Private Sub txtnis_Enter()
TXTNis.BackColor = &H80000005
End Sub
Private Sub txtnis_Exit(ByVal Cancel As MSForms.ReturnBoolean)
HanyaAngka
TXTNis.BackColor = &HE0E0E0
End Sub

Private Sub txtnama_enter()
TXTNama.BackColor = &H80000005
End Sub
Private Sub txtnama_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTNama.BackColor = &HE0E0E0
End Sub

Private Sub txttempatlahir_enter()
TXTTempatLahir.BackColor = &H80000005
End Sub
Private Sub txttempatlahir_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTTempatLahir.BackColor = &HE0E0E0
End Sub

Private Sub txttgllahir_enter()
TXTTglLahir.BackColor = &H80000005
End Sub
Private Sub txttgllahir_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTTglLahir.BackColor = &HE0E0E0
End Sub

Private Sub txtalamat_Enter()
TXTAlamat.BackColor = &H80000005
End Sub
Private Sub txtalamat_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTAlamat.BackColor = &HE0E0E0
End Sub

Private Sub cbokelamin_Enter()
CBOKelamin.BackColor = &H80000005
End Sub
Private Sub cbokelamin_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CBOKelamin.BackColor = &HE0E0E0
End Sub

Private Sub txtnisn_Enter()
TXTNISN.BackColor = &H80000005
End Sub
Private Sub txtnisn_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTNISN.BackColor = &HE0E0E0
End Sub

Private Sub txthp_Enter()
TXTHP.BackColor = &H80000005
End Sub
Private Sub txthp_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTHP.BackColor = &HE0E0E0
End Sub

Private Sub txtskhun_Enter()
TXTSKHUN.BackColor = &H80000005
End Sub
Private Sub txtskhun_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTSKHUN.BackColor = &HE0E0E0
End Sub

Private Sub txtijasah_Enter()
TXTIjasah.BackColor = &H80000005
End Sub
Private Sub txtijasah_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTIjasah.BackColor = &HE0E0E0
End Sub

Private Sub txtnamaibu_Enter()
TXTNamaIbu.BackColor = &H80000005
End Sub
Private Sub txtnamaibu_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTNamaIbu.BackColor = &HE0E0E0
End Sub

Private Sub txtthnlahiribu_Enter()
TXTThnLahirIbu.BackColor = &H80000005
End Sub
Private Sub txtthnlahiribu_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTThnLahirIbu.BackColor = &HE0E0E0
End Sub

Private Sub txtpekibu_Enter()
TXTPekIbu.BackColor = &H80000005
End Sub
Private Sub txtpekibu_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTPekIbu.BackColor = &HE0E0E0
End Sub

Private Sub cbopendidikanibu_Enter()
CBOPendidikanIbu.BackColor = &H80000005
End Sub
Private Sub cbopendidikanibu_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CBOPendidikanIbu.BackColor = &HE0E0E0
End Sub

Private Sub txtnamaayah_Enter()
TXTNamaAyah.BackColor = &H80000005
End Sub
Private Sub txtnamaayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTNamaAyah.BackColor = &HE0E0E0
End Sub

Private Sub txtthnayah_Enter()
TXTThnAyah.BackColor = &H80000005
End Sub
Private Sub txtthnayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTThnAyah.BackColor = &HE0E0E0
End Sub

Private Sub txtpekayah_Enter()
TXTPekAyah.BackColor = &H80000005
End Sub
Private Sub txtpekayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTPekAyah.BackColor = &HE0E0E0
End Sub

Private Sub cbopendidikanayah_Enter()
CBOPendidikanAyah.BackColor = &H80000005
End Sub
Private Sub cbopendidikanayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CBOPendidikanAyah.BackColor = &HE0E0E0
End Sub

Private Sub txtpengayah_Enter()
TXTPengAyah.BackColor = &H80000005
End Sub
Private Sub txtpengayah_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTPengAyah.BackColor = &HE0E0E0
End Sub

Private Sub txtalamatortu_Enter()
TXTAlamatOrtu.BackColor = &H80000005
End Sub
Private Sub txtalamatortu_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TXTAlamatOrtu.BackColor = &HE0E0E0
End Sub

10. Tahap terakhir, rename Sheet1 dengan 'DataBaseSiswa' untuk menyimpan database. Maka, ketika database disimpan, akan tersimpan pada sheet tsb.

11. Untuk menjalankan userformnya pilih menu Run lalu Runsub/Userform

12. Otomatis akan muncul tampilan seperti ini

13. Ketika anda klik tombol Simpan saat NIS masih kosong, sesuai code akan muncul perintah ini

Cara Set Print Area pada Excel serta mengubah file Excel menjadi PDF

Dalam menggunakan Ms Excel, jika kita hanya membutuhkan area tertentu saja untuk dicetak, kita dapat menggunakan fitur Print Area. Dengan fitur Print Area kita dapat menentukan daerah lembar kerja yang akan kita cetak atau mencetak area sel yang diinginkan.

Berikut langkah-langkah menggunakan Print Area.

1. Blok area sel yang akan dicetak

2. Pada Tab Page Layout, pilih Print Area kemudian klik Set Print Area

3. Maka area yang diblok tadi akan dikelilingi garis putus-putus

4. Kemudian pilih Tab File lalu klik print. Maka akan terlihat di  print preview bahwa hasil yang akan dicetak telah sesuai dengan area yang kita inginkan.

Mengubah file Excel Menjadi PDF

Berikut langkah-langkahnya.

1. Pilih Tab File kemudian klik Save As

2. Pilih lokasi penyimpanan dan isi nama file pada File Name. Pada Save as Type pilih PDF.

3. Maka file akan tersimpan dalam bentuk PDF seperti pada gambar dibawah

Cara Membuat Drop-down List pada Ms. Excel

Drop-down list adalah daftar isian yang dapat memudahkan pengguna untuk memilih salah satu nilai dari daftar. Drop –down list ini dapat digunakan untuk pembuatan formulir atau kuesioner agar jawaban yang diperoleh seragam.

Berikut langkah-langkah membuat Drop-down List

Pertama, buat daftar isian yang akan digunakan di lembar kerja Excel.

Lalu klik pada cell yang akan diisi jawaban, pilih pada Tab Data lalu pilih Data Validation.

Sehingga muncul window box Data Validation. Dalam menu settings, pada criteria Allow pilih List. Kemudian isi Source dengan pilihan jawaban yang diinginkan.

Misal untuk jenis kelamin, isi pada Source :

Laki-laki, Perempuan

[untuk pemisah antar pilihan bisa gunakan tanda koma (,) atau titik koma (;) ]

Maka akan muncul pilihan pada isian untuk jenis kelamin sehingga kita tinggal memilih dengan mengklik pada tanda panah disamping kanan isian.

Lakukan hal yang sama pada isian yang lain, hanya tinggal mengubah source nya saja.

Penggunaan Pivot Table pada data kelas

Pertama, blok semua data pada tabel

Kemudian pilih Pivot Table pada Tab Insert

Lalu pilih lokasi Pivot Table akan ditempatkan. Seperti yang telah dijelaskan pada post sebelumnya, terdapat dua pilihan lokasi yaitu New Worksheet dan Existing Worksheet

Atur Pivot Table Fields sesuaikan dengan keperluan.

Jika menambahkan data baru ke sumber data Pivot Table, setiap Pivot Table yang dibuat berdasarkan sumber data tersebut perlu direfresh. Untuk merefresh PivotTable, pilih Tab Data lalu klik Refresh All.

Cara menggunakan Pivot Table untuk menganalisis data pada Ms. Excel

Pivot Table sangat berguna untuk mengelola serta menganalisis data yang sangat banyak dan rumit yang dapat dibuat ringkasan data dan penyajian data menjadi simpel dan detail.

Berikut langkah-langkah membuat Pivot Table

Contoh yang pertama menggunakan data dari Tabel Daftar Belanja Barang di bawah ini.

1) Daftar Belanja Barang

Pertama, klik Tab Insert kemudian pilih Pivot Table

Lalu akan muncul form create pivot table.

Pada select a table or range blok data yang akan dibuat pivot table (pada contoh ini adalah tabel Daftar Belanja Barang di atas).

Kemudian pilih lokasi hasil pivot table akan ditempatkan pada choose where you want the PivotTable report to be placed.  Disini terdapat 2 pilihan yaitu :

1.    New worksheet apabila ingin menempatkan hasil pivot table pada sheet baru yang masih kosong .

2.    Existing worksheet apabila ingin hasil Pivot Table pada sheet yang sama dengan data tabel Daftar Belanja Barang, dengan memilih sel yang akan ditempatkan, bisa disamping tabel atau dibawah tabel.

Jika pemilihan data dan penempatan data sudah dilakukan, tekan tombol ok untuk membuat dan memilih penyajian data pada pivot table fields.

sebagai contoh, pada report filters, akan saya letakkan field supplier, lalu field nama barang pada row labels, dan field yang akan dicari nilainya yaitu field harga saya tempatkan pada values.

Maka hasilnya akan terlihat seperti pada gambar di bawah.

2) Data Pengeluaran Rumah Tangga

Contoh yang kedua saya akan menggunakan tabel Data Pengeluaran Rumah Tangga berikut.

Seperti pada contoh di atas, pilih Pivot Table pada Tab Insert, dan atur Pivot Table Fields sesuai kebutuhan.

Untuk membuat tampilan penyajian data berupa chart, pilih Pivot Chart pada Tab Insert.

Hasilnya tampak seperti gambar di bawah.

3) Data Penjualan Bulan Januari

Berikut adalah contoh tabel Data Penjualan Bulan Januari

Atur Pivot Table Fields.

Report filters pada tabel yaitu kode barang, dapat dilihat ringkasan data yang berbeda sesuai jenis kode barang misal item 6, dengan cara mengklik tanda panah di sebelah kanan kode barang.