Belajar Excel Membuat Formulir Input Untuk Mendapatkan Data dan Menyimpannya di Tab Lain di Excel

Cara membuat formulir input pengguna di Excel dan menyimpan data di lembar kerja lain dengan mengklik tombol.

Metode ini menggunakan formulir input sederhana yang dapat dibuat dan dilakukan oleh siapa pun di dalam lembar kerja dan tidak mencakup UserForms yang dibuat di VBA dan itu bisa sangat rumit bagi pengguna untuk dibuat dan dimaintain.

Tutorial ini memang menggunakan makro untuk mendapatkan data dari formulir input ke lembar kerja di mana ia akan disimpan tetapi jangan khawatir, saya akan membahasnya langkah demi langkah.

Buat Formulir Input

Pertama, kita membuat form dimana user akan menginput data. Anda dapat menghabiskan banyak waktu atau sedikit untuk melakukan ini, tetapi yang paling penting adalah mudah bagi pengguna untuk memahami apa yang harus dilakukan.

Berikut adalah beberapa langkah untuk membuat bentuk dasar formulir yang saya suka.

Buat judul dan berikan format seperti judul:

Lewati satu baris lalu masukkan judul bidang formulir. Lewati satu baris di antara setiap bidang formulir. Ini memungkinkan Anda untuk mengontrol jarak antar bidang dan membuatnya lebih mudah digunakan. 

Terapkan juga beberapa pemformatan dasar pada judul; Saya hanya membuat mereka berani

Sesuaikan jarak antara setiap bidang dengan memilih baris kosong di antara mereka sambil menahan tombol Ctrl.

Kemudian, ubah ketinggian satu dan semua akan berubah pada saat yang bersamaan.

Buat kolom A cukup besar untuk menampilkan judul bidang formulir. Kemudian buat kolom B kecil; kita akan menggunakan kolom ini sebagai pemisah antara field input form dan judul.

Klik kotak di kiri atas baris dan kolom atau tekan saja Ctrl + A untuk memilih semuanya.

Lalu klik ikon ember cat di kotak Font pada tab Beranda dan pilih warna keabu-abuan untuk mewarnai latar belakang form yang akan dibuat.

Anda sekarang akan memiliki sesuatu tampilan seperti ini:

Pilih sel di kolom C yang berada di baris yang sama dengan judul bidang formulir dan buat latar belakangnya menjadi putih.

Jika mau, Anda dapat menambahkan batas garis penuh di sekitar setiap sel putih; beberapa orang melakukan seperti itu.

Saatnya menambahkan tombol.

Buka tab Insert dan klik Shapes dan pilih Rounded Rectangle dari daftar.

Kemudian, klik kiri pada mouse, tahan, dan seret hingga Anda memiliki ukuran tombol yang diinginkan.

Pastikan tombol dipilih dan kemudian buka tab Format yang akan muncul dan klik panah bawah di bagian Gaya Bentuk dan pilih yang sudah jadi dari sana jika Anda mau.

Klik tombol lagi dan ketik beberapa teks.

Sekarang kita memiliki bentuk yang cukup bagus.

Sebelum kita melanjutkan, saya ingin menambahkan nama untuk setiap field input. Ini akan membuat pembuatan makro menjadi lebih mudah.

Pilih bidang input putih untuk Nama Depan dan kemudian lihat ke kotak putih tepat di atas di mana dikatakan kolom A, itu adalah Kotak Nama dan di sanalah kita akan mengetikkan nama untuk bidang ini; Saya menamai dengan nama_depan. Setelah Anda mengetik nama, Anda harus menekan Enter. (petunjuk: Anda tidak dapat menggunakan spasi dalam nama)

Ulangi ini untuk bidang lain sampai masing-masing memiliki nama yang unik.

Untuk menyelesaikan tampilan formulir, buka tab View dan lihat di Show section.

Hapus centang Formula bar, Gridlines, dan Headings.

Ubah ukuran windows dan sembunyikan menu Ribbon (Anda dapat menggunakan makro untuk sepenuhnya menyembunyikan menu Ribbon jika Anda mau).

Sekarang Anda punya bentuk form kecil yang bagus.

Anda juga dapat mengunci lembar kerja dan membiarkan hanya 4 bidang input putih yang tidak terkunci jika Anda ingin melindungi formulir dan Anda dapat menggunakan Validasi Data untuk memastikan pengguna memasukkan tipe data yang benar.

Buat Makro untuk Menyimpan Data

Sekarang setelah Anda memiliki formulir, mari buat tombol Kirim itu berfungsi.

Di sini, saya akan menunjukkan cara membuat makro, yang akan mudah diikuti, saya janji.

Cari tahu di mana Anda akan meletakkan data. Anda dapat mengirimkannya melalui email di suatu tempat, dimasukkan ke dalam database nyata, atau hanya disimpan di lembar kerja terpisah. Saya akan membahas opsi terakhir di sini, jadi langkah pertama adalah membuat lembar kerja dan memastikan kita tahu kemana data akan disimpan.

Saya membuat lembar kerja bernama Data dan memberi judul untuk setiap kolom yang akan menampung data.

Anda juga perlu menambahkan baris judul untuk data Anda agar makro yang Anda buat di sini berfungsi.

Saatnya membuat makro. Tekan Alt + F11 untuk membuka jendela VBA. Seharusnya terlihat seperti ini:

Masuk ke Insert > Module.

Sebuah jendela kosong akan terbuka:

Ketik sub lalu spasi dan kemudian nama yang ingin Anda berikan pada makro.

Tekan Enter dan secara otomatis akan diformat seperti ini:

Untuk memulai makro, saya menyimpan nama lembar kerja tempat input akan disimpan, lembar kerja Data. Ini adalah sesuatu yang tidak harus Anda lakukan tetapi akan banyak membantu jika Anda mengubah nama lembar kerja.

Variabel ws_output menyimpan data teks, yang hanya merupakan nama lembar kerja tempat data akan pergi.

Sekarang, kita perlu mencari tahu apa baris kosong berikutnya pada lembar kerja data sehingga kita tidak menimpa apa pun.

Potongan kode ini akan menemukan baris itu dan menyimpannya ke dalam variabel bernama next_row:

 next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

Baris kode ini mungkin tampak membingungkan tetapi, sejujurnya, Anda tidak benar-benar perlu tahu cara kerjanya kecuali bahwa ws_output harus berisi nama lembar kerja tempat Anda ingin meletakkan data dan A adalah kolom tempat Anda akan menempatkan data yang harus selalu diinput oleh pengguna; kolom ini tidak boleh menyimpan data opsional atau Anda akan berakhir menimpa beberapa data jika pengguna memilih untuk mengosongkan bidang itu.

Untuk mempelajari lebih lanjut tentang baris kode ini, buka tutorial di sini: temukan baris kosong berikutnya di lembar kerja

Kita sekarang tahu ke mana data harus pergi dan di mana baris kosong berikutnya; selanjutnya, kita hanya perlu mengambil data dari lembar kerja Input dan memasukkannya ke dalam lembar kerja Data.

Inilah cara melakukannya dengan bidang Nama Depan.

Sheets(ws_output).Cells(next_row, 1).Value = Range("nama_depan").Value

ws_output adalah variabel yang menyimpan nama lembar kerja Data.

Cells(next_row, 1) mengatakan ke mana data harus diletakkan di lembar kerja. next_row berasal dari langkah terakhir dan mengatakan di baris mana data akan ditempatkan. 1 adalah nomor kolom tempat data akan pergi. Nilai Nama Depan akan masuk ke kolom A dan itu adalah kolom pertama, jadi 1 masuk ke sana. Untuk kolom B, kita akan menggunakan 2 sebagai ganti 1 dan kolom C akan menjadi 3, dst.

Range("nama_depan") adalah apa yang memilih nilai dari sel nama depan pada lembar kerja Input. Karena saya menamai sel itu nama_depan, saya tidak perlu merujuk lembar kerja tempat ia berada; Saya cukup memasukkan nama sel di dalam Range() dan itu akan mendapatkan nilai yang benar.

Nilai ada di dua tempat di baris kode ini dan digunakan untuk mendapatkan nilai sel nama_depan dan kemudian memasukkan nilai itu ke sel baru pada lembar kerja Data.

Ini makro dengan baris baru:.

Ulangi Langkah 7 untuk setiap bagian data yang ingin Anda simpan.

Tiga baris baru di sini adalah:

Sheets(ws_output).Cells(next_row, 2).Value = Range("nama_belakang").Value

Sheets(ws_output).Cells(next_row, 3).Value = Range("email").Value

Sheets(ws_output).Cells(next_row, 4).Value = Range("akun").Value

Baris-baris ini akan mendapatkan nama belakang, email, dan nilai akun dan memasukkannya ke lembar kerja Data.

Satu-satunya hal yang diubah di setiap baris adalah kolom tempat data akan ditempatkan pada lembar kerja Data dan nama sel tempat kita mendapatkan data.

Untuk mendapatkan nilai nama belakang dari lembar kerja Input, saya memasukkan nama_belakang dalam metode range: Range("nama_belakang").

Untuk memasukkan nilai nama belakang ke dalam kolom kedua lembar kerja Data, saya memasukkan 2 ke dalam metode sel: Sel (baris berikutnya, 2).

Anda dapat melihat bahwa setiap baris mengikuti pola ini; jumlah kolom dalam metode sel bertambah satu, sehingga setiap bagian data masuk ke kolom berikutnya dan nama sel dalam metode rentang diperbarui untuk mendapatkan bagian data yang diinginkan.

Sekarang, kembali ke Excel, Alt + F11, dan lampirkan makro ke tombol Kirim.

Untuk melakukan ini, klik kanan tombol Kirim dan klik Tetapkan Makro.

Pilih makro dari daftar dan klik OK.

Saatnya untuk menguji semuanya! Mari kita masukan beberapa data.

Klik tombol Kirim, buka tab Data, dan Anda akan melihat datanya.

Wah, jadi itu banyak sekali langkahnya, tetapi Anda sekarang seharusnya memiliki formulir Input dan lembar kerja penyimpanan data yang berfungsi penuh di Excel.

Ada beberapa hal lain yang dapat Anda tambahkan ke makro untuk membuatnya sedikit lebih ramah pengguna, seperti mengosongkan formulir saat entri berhasil dibuat, memperlihatkan pesan keluaran bahwa data telah disimpan, menyembunyikan dan mengunci Lembar kerja data sehingga pengguna tidak dapat dengan mudah mengubahnya dengan tangan, dan lainnya. Tapi, apa yang saya tunjukkan di sini adalah pembuatan fungsionalitas inti dari formulir Input dan sistem penyimpanan.

Dapatkan ini berfungsi terlebih dahulu dan kemudian lanjutkan untuk menambahkan fitur-fitur lainnya. Saya juga akan membahas banyak fitur tersebut di tutorial lainnya.

Jika Anda ingin pemahaman yang lebih baik tentang makro ini, baca bagian dari atas yang disebut Membuat Makro untuk Menyimpan Data.

Catatan:

Tidak banyak yang bisa dikatakan tentang yang satu ini yang belum saya katakan. Saya harap semuanya jelas dan mudah dimengerti tetapi tidak terlalu lambat.

Pastikan untuk mendownload file excel yang dilampirkan pada tutorial ini sehingga Anda bisa mendapatkan semua ini di Excel (maka Anda tidak harus memulai dari awal saat membuat formulir input Anda sendiri).

File yang Dapat Diunduh: File Excel