Mengunci dan Memproteksi Sel Berisi Formula
Ketika merancang sebuah template laporan berformat excel, mungkin kita menginginkan user hanya dapat melengkapi data pada sel yang ditentukan saja pada template laporan yang kita buat. Selain itu, aktivitas user harus dibatasi supaya tidak bisa melakukan modifikasi formula yang bisa menyebabkan kesalahan kalkulasi data. Untuk itu maka kita bisa mengatur penguncian (lock) dan proteksi sel.
Metode termudah dan paling umum untuk mengamankan template spreadsheet dari user yang hendak main-main dengan formula adalah dengan cara memproteksi worksheet. Sayangnya, proteksi sheet saja tidak hanya mencegah user melakukan modifikasi terhadap rumus, tetapi juga memberhentikan user dari memasukan apapun segalanya ke dalam worksheet. Terkadang anda tidak menginginkannya. Untuk itu ada 3 solusi yaitu:
1. Mengunci Sel Berisi Formula Saja
2. Gunakan data validation
3. Auto Toggle proteksi sheet dengan VBA
Dalam praktekanya dari ketiga cara di atas tidak ada satu pun yang benar-benar kebal terhadap gangguan. Namun tidak ada salahnya bagi kita untuk memahami dan memanfaatkannya.
Secara garis besar, tahapannya sebagai berikut:
1. Setting semua cell pada posisi unlocked
2. Setting cell locked pada range yang diinginkan saja (sel berisi formula / rumus)
3. Setting proteksi sheet.
Secara default, semua sel dalam worksheet ter-setting dalam posisi terkunci (Locked). Seting Locked tidak berpengaruh apa-apa kecuali proteksi sheet sudah diaktifkan. Berikut adalah cara mudah untuk menerapkan proteksi worksheet sehingga hanya sel berisi formula saja yang dikunci dan di-protect:
Untuk mengunci sel yang berisi formula, ikuti langkah selanjutnya:
Setelah proses di atas, maka semua sel berisi formula akan terseleksi.
Klik kanan pada range terseleksi ➜ Format Cells ➜ masuk ke tab Protection – centang checkbox Locked. Ini akan menyebabkan semua range yang berisi formula saja yang di lock.
Setting locked cell tersebut tidak akan berpengaruh apa-apa jika proteksi worksheet tidak diaktifkan.
Selanjutnya langkah proteksi worksheet.
Excel 2007 ke atas: dari tab Review ➜ klik Protect Sheet ➜ sehingga muncul jendela protect sheet:
Lakukan pengaturan misalnya membuat pasword, dan hal-hal yang diizinkan atau tidak untuk user. Lalu klik OK.
Metode di atas khususnya dapat menghemat banyak waktu serta mengeliminasi kemungkinan error karena kesalahan edit formula oleh user. Sayangnya penerapan metode ini juga menyebabkan user kehilangan kesempatan untuk memanfaatkan fitur-fitur lainnya yang sangat berguna, seperti sorting, formating dan sebagainya bahkan terhadap sel yang tidak di-lock sekalipun.
Penggunaan data validation tidak tergantung pada proteksi worksheet sama sekali, namun dapat dengan mudah mencegah edit formula.
Sayangnya data validation masih jauh dari kata tahan banting untuk mencegah user memasukan data tidak tervalidasi ke sel. User memang tidak bisa mengetik data tidak valid pada sel yang divalidasi. Namun user masih bisa copy paste data tidak valid pada sel yang divalidasi. Bahkan validasi sel bisa terhapus atau berubah sesuai sel yang dicopy paste.
Untuk memahami apa yang dimaksudkan silahkan lakukan langkah – langkah berikut dimulai dengan menyeleksi sel berisi formula yang harus diamankan.
Seleksi satu sel sembarang, kemdian tekan F5 atau Ctr+G untuk masuk ke jendelal Go To,
Langkah selanjutnya lakukan setting data validation
Cara ke-3 ini menggunakan kode VBA untuk proteksi worksheet secara otomatis ketika user sel aktif berisi formula. Dengan metode ini, user masih bisa menggunakan fitur-fitur excel ketika sel aktive sedang berada di sel yang tidak di-lock.
Caranya ikuti langkah berikut:
Jika diperlukan, anda juga bisa menambahkan pasword pada kode VBA. Misalnya paswordnya =
“abc” (tanpa tanda kutip), maka code VBA menjadi sebagai berikut:
Setelah code terpasang maka secara otomatis worksheet akan terproteksi ketika sel aktive berada pada sel yang berisi formula (setting locked). Tentu saja dengan syarat setting macro enabled.
Jika menggunakan tenhik ke-3 ini, jika anda menggunakan versi excel 2007 atau yang lebih baru maka file harus disimpan dengan extensi .xlsm atau .xlsb
Meskipun bekerja cukup baik, cara ini tidak lepas dari kelemahan. Target dalam kode, mereferensikan sel aktif. Jika kita menyeleksi sejumlah range dan sel aktif nya adalah sel yang tidak di lock maka kita bisa menghapus semua data dalam range yang diseleksi meskipun dalam range tersebut ada sel yang di lock.
Sampai disini mudah-mudahan uraian mengenai beberapa cara untuk proteksi formula atau rumus ini bisa mudah difahami. Mau pilih cara yang mana? Silahkan pilih mana saja sesuai preferensi anda. Semoga bermanfaat.
Dapatkan tips excel yang sangat berguna:
Referensi:
David Raina & Hawley 2007, Excel Hack, Tips & Tools for Streamlining Your Spreadsheet, 2nd edition. O’Reilly Media, Inc
Metode termudah dan paling umum untuk mengamankan template spreadsheet dari user yang hendak main-main dengan formula adalah dengan cara memproteksi worksheet. Sayangnya, proteksi sheet saja tidak hanya mencegah user melakukan modifikasi terhadap rumus, tetapi juga memberhentikan user dari memasukan apapun segalanya ke dalam worksheet. Terkadang anda tidak menginginkannya. Untuk itu ada 3 solusi yaitu:
1. Mengunci Sel Berisi Formula Saja
2. Gunakan data validation
3. Auto Toggle proteksi sheet dengan VBA
Dalam praktekanya dari ketiga cara di atas tidak ada satu pun yang benar-benar kebal terhadap gangguan. Namun tidak ada salahnya bagi kita untuk memahami dan memanfaatkannya.
Mengunci Sel Berisi Formula Saja
Secara garis besar, tahapannya sebagai berikut:
1. Setting semua cell pada posisi unlocked
2. Setting cell locked pada range yang diinginkan saja (sel berisi formula / rumus)
3. Setting proteksi sheet.
Secara default, semua sel dalam worksheet ter-setting dalam posisi terkunci (Locked). Seting Locked tidak berpengaruh apa-apa kecuali proteksi sheet sudah diaktifkan. Berikut adalah cara mudah untuk menerapkan proteksi worksheet sehingga hanya sel berisi formula saja yang dikunci dan di-protect:
- Seleksi keseluruhan sel dalam worksheet (misalnya dengan cara tekan Ctrl + A),.
- Kemudian klik kanan ➜ Format Cells ➜ masuk ke tab Protection
- Hilangkan centang pada check box locked
- Klik OK
Untuk mengunci sel yang berisi formula, ikuti langkah selanjutnya:
- Excel 2017 atau yang lebih baru: Tab Home ➜ Find Select ➜ Go To Special.
- Excel 2003 : menu Edit ➜ Go To ➜ Special
- Bisa juga dengan cara menekan shortcut (berlaku untuk semua Versi) : Tekan tombol short cut F5 atau Ctr + G
- Maka akan muncul dialog box Go To Special seperti terlihat dalam gambar di bawah ini.
- Dalam dialog box Go To Special, pilih opsi Formulas, jika diperlukan centang pilihan jenis formula di bawahnya (Numbers, Text, Logicals, Error, Jika anda ingin mengunci semua jenis formula. Biarkan semuanya tercentang). Lalu klik OK.
Setelah proses di atas, maka semua sel berisi formula akan terseleksi.
Klik kanan pada range terseleksi ➜ Format Cells ➜ masuk ke tab Protection – centang checkbox Locked. Ini akan menyebabkan semua range yang berisi formula saja yang di lock.
Setting locked cell tersebut tidak akan berpengaruh apa-apa jika proteksi worksheet tidak diaktifkan.
Selanjutnya langkah proteksi worksheet.
Excel 2007 ke atas: dari tab Review ➜ klik Protect Sheet ➜ sehingga muncul jendela protect sheet:
Lakukan pengaturan misalnya membuat pasword, dan hal-hal yang diizinkan atau tidak untuk user. Lalu klik OK.
Metode di atas khususnya dapat menghemat banyak waktu serta mengeliminasi kemungkinan error karena kesalahan edit formula oleh user. Sayangnya penerapan metode ini juga menyebabkan user kehilangan kesempatan untuk memanfaatkan fitur-fitur lainnya yang sangat berguna, seperti sorting, formating dan sebagainya bahkan terhadap sel yang tidak di-lock sekalipun.
Data Validation
Penggunaan data validation tidak tergantung pada proteksi worksheet sama sekali, namun dapat dengan mudah mencegah edit formula.
Sayangnya data validation masih jauh dari kata tahan banting untuk mencegah user memasukan data tidak tervalidasi ke sel. User memang tidak bisa mengetik data tidak valid pada sel yang divalidasi. Namun user masih bisa copy paste data tidak valid pada sel yang divalidasi. Bahkan validasi sel bisa terhapus atau berubah sesuai sel yang dicopy paste.
Untuk memahami apa yang dimaksudkan silahkan lakukan langkah – langkah berikut dimulai dengan menyeleksi sel berisi formula yang harus diamankan.
Seleksi satu sel sembarang, kemdian tekan F5 atau Ctr+G untuk masuk ke jendelal Go To,
- Klik Special
- Tick Formulas
- Klik OK
Langkah selanjutnya lakukan setting data validation
- Excel 2003: menu Data ➜ Validation
- Excel 2007 atau yang lebih baru: Masuk ke Tab Data ➜ Data validation
- Sehingga muncul jendela Data Validation
- Masuk ke tab setting, pada kotak allow pilih custom dan pada kotak formula, masukan rumus ="" seperti dapat dilihat dalam gambar di bawah ini:
- Kemudian klik OK
Auto – Toggle Worksheet Protection
Cara ke-3 ini menggunakan kode VBA untuk proteksi worksheet secara otomatis ketika user sel aktif berisi formula. Dengan metode ini, user masih bisa menggunakan fitur-fitur excel ketika sel aktive sedang berada di sel yang tidak di-lock.
Caranya ikuti langkah berikut:
- Seleksi semua cell dengan menekan shortcut Ctr + A, kemudian klik kanan ➜ format cells ➜ tab protection ➜ hilangkan centang Locked
- Seleksi range formula yang akan di proteksi. Jika posisi formula tersebar maka lakukan langkah berikut: Tekan Ctrl + G, atau tekan F5 untuk membuka jendela Go To, kemudian klik Special ➜ centang Formula ➜ klik Ok
- Kemudian tekan Ctrl + 1 atau Klik kanan ➜ format cells ➜ Protection ➜ centang Locked. Hal ini akan menerapkan setting locked pada range sel yang diseleksi (range sel berisi formula)
- Selanjutnya Klik kanan tab sheet kemudian klik view Code
- Kemudian masukan kode berikut kedalam modul worksheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
Me.Protect
Else
Me.Unprotect
End If
End Sub
Jika diperlukan, anda juga bisa menambahkan pasword pada kode VBA. Misalnya paswordnya =
“abc” (tanpa tanda kutip), maka code VBA menjadi sebagai berikut:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
Me.Protect pasword:="abc"
Else
Me.Unprotect pasword:="abc"
End If
End Sub
Setelah code terpasang maka secara otomatis worksheet akan terproteksi ketika sel aktive berada pada sel yang berisi formula (setting locked). Tentu saja dengan syarat setting macro enabled.
Jika menggunakan tenhik ke-3 ini, jika anda menggunakan versi excel 2007 atau yang lebih baru maka file harus disimpan dengan extensi .xlsm atau .xlsb
Meskipun bekerja cukup baik, cara ini tidak lepas dari kelemahan. Target dalam kode, mereferensikan sel aktif. Jika kita menyeleksi sejumlah range dan sel aktif nya adalah sel yang tidak di lock maka kita bisa menghapus semua data dalam range yang diseleksi meskipun dalam range tersebut ada sel yang di lock.
Sampai disini mudah-mudahan uraian mengenai beberapa cara untuk proteksi formula atau rumus ini bisa mudah difahami. Mau pilih cara yang mana? Silahkan pilih mana saja sesuai preferensi anda. Semoga bermanfaat.
Dapatkan tips excel yang sangat berguna:
- Cara Membatasi Scroll Area
- Cara Mencegah Save As, Print, dan Insert Sheet
- Co-Cote Senjata Pamungkas Pejuang Insert Comment
Referensi:
David Raina & Hawley 2007, Excel Hack, Tips & Tools for Streamlining Your Spreadsheet, 2nd edition. O’Reilly Media, Inc
Posting Komentar untuk "Mengunci dan Memproteksi Sel Berisi Formula"