Mengunci sel excel secara otomatis setelah entri data

Excel menawarkan perlindungan untuk lembar kerja Anda, artinya Anda bisa memproteksi konten sel sehingga tidak dapat diubah. Persis bagaimana Anda menggunakan perlindungan ini telah dibahas dalam edisi lain dari ExcelTips

Bagaimana jika Anda ingin memperbolehkan sel untuk diedit, tetapi Anda ingin sel tersebut dilindungi segera setelah seseorang memasukkan informasi ke dalam sel?

Tidak ada kemampuan bawaan di Excel untuk melindungi input Anda setelah masuk, namun Anda bisa menciptakan kemampuan tersebut melalui penggunaan makro. Makro berikut adalah contoh bagaimana Anda bisa melakukan ini

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range

    Set MyRange = Intersect(Range("A1:D100"), Target)
    If Not MyRange Is Nothing Then
        Sheets("Sheet1").Unprotect password:="hello"
        MyRange.Locked = True
        Sheets("Sheet1").Protect password:="hello"
    End If
End Sub

Makro ini mengasumsikan bahwa lembar kerja telah dilindungi dan bahwa semua sel tempat Anda ingin memasukkan mungkin tidak terkunci. Apa yang dilakukannya adalah memeriksa apakah input dilakukan dalam rentang sel yang tepat, dalam hal ini di suatu tempat dalam rentang A1. D100. Jika ya, maka lembar kerja tidak terlindungi, sel tempat informasi baru saja dimasukkan dikunci, dan lembar kerja kembali terlindungi

Jika Anda menggunakan pendekatan ini di buku kerja Anda sendiri, Anda perlu memodifikasi kisaran input potensial dan Anda ingin mengubah kata sandi yang digunakan untuk membuka proteksi dan melindungi lembar kerja

Tutorial ini akan menunjukkan cara mengunci sel setelah entri data menggunakan Excel VBA dengan pemberitahuan kotak pesan sebelum mengunci. Jadi pengguna tidak akan dapat mengubah nilai setelah masuk. Jika mereka mencoba, pemberitahuan kotak pesan akan muncul. Terkadang kita perlu mengunci sel setelah entri data. Kami melakukan ini untuk membatasi pengguna agar tidak sering mengubah data

Daftar isi

Download Buku Latihan

Anda dapat mengunduh buku latihan dari sini

Kunci Sel dengan VBA. xlsm

2 Metode Mengunci Sel Setelah Entri Data Menggunakan VBA Excel dengan Notifikasi Kotak Pesan Sebelum Mengunci

Pada artikel ini, kami akan menggunakan 2 metode untuk mengunci sel setelah entri data menggunakan Excel VBA. Setelah mengunci sel, pengguna tidak akan dapat mengubah data. Notifikasi kotak pesan akan muncul jika ada pengguna yang mencoba mengedit data setelah masuk. Untuk mengilustrasikan kedua metode ini kita akan menggunakan dataset berikut. Pada dataset, pengguna akan menginput nilai jumlah penjualan. Namun, mereka dapat memasukkan nilai dalam sel tertentu sekali saja. Jika pengguna mencoba mengubah nilai, pemberitahuan kotak pesan akan muncul

1. Menggunakan Kata Sandi atau Tanpa Kata Sandi untuk Mengunci Sel

1. 1 Menggunakan Kata Sandi

Pertama dan terpenting, kami akan mengunci sel setelah entri data dengan kata sandi. Kami akan melakukan ini menggunakan kode Excel VBA. Setiap upaya untuk mengubah data setelah masuk akan menampilkan pemberitahuan kotak pesan. Mari kita lihat langkah-langkah untuk melakukan metode ini

LANGKAH

• Untuk memulai, pilih rentang data (B4. D9)
• Selain itu, klik kanan pada area yang dipilih. Klik pada opsi Format Sel

• Kemudian, dari kotak dialog Format Cells pergi ke Protection Hapus centang pada opsi Locked
• Sekarang klik OK

• Selanjutnya klik kanan pada tab sheet ‘With Password’. Pilih opsi Lihat Kode

• Perintah di atas akan membuka jendela kode VBA kosong
• Selanjutnya, ketikkan kode berikut di jendela kode itu

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
ActiveSheet.Unprotect Password:="1234"
For Each aCell In Target
If aCell.Value <> "" Then
Trial = MsgBox("Is this value correct? You can not change after entering a value.", _
vbYesNo, "Message Box Notifation")
If Trial = vbYes Then
aCell.Locked = True
Else
well.Value = ""
End If
End If
Next aCell
ActiveSheet.Protect Password:="1234"
End Sub

• Klik tombol Jalankan atau tekan tombol F5 untuk menjalankan kode

• Buat makro untuk menjalankan kode. Kami membuat makro bernama VBA
• Kemudian, pilih VBA dan klik Jalankan

• Setelah itu, masukkan nilai di sel D5
• Sebuah kotak pesan muncul. Ini menanyakan apakah nilainya benar atau tidak. Karena kita tidak bisa mengubah nilainya begitu kita membuat entri di sel itu

• Setelah itu, coba edit sel D5
• Kami akan menerima pesan peringatan seperti gambar berikut

• Selain itu, untuk mengedit nilainya lagi, kita harus membuka proteksi sheet
• Jadi, buka Review > Unprotect Sheet

• Lalu, ketikkan kata sandi yang Anda gunakan di VBA. Dalam contoh ini, kata sandi kami adalah 1234
• Sekarang, klik OK

• Terakhir, klik pada sel D5 Sel D5 tidak terkunci sekarang. Jadi, kita bisa mengubah nilainya setelah melakukan entri

Baca Selengkapnya. Cara Melindungi Sel Excel dengan Kata Sandi (4 Contoh yang Cocok)

1. 2 Tanpa Kata Sandi

Metode ini sebagian besar identik dengan metode sebelumnya. Kami juga akan mengunci sel setelah entri data menggunakan Excel VBA dalam metode ini. Tapi, kali ini kita tidak akan menggunakan password di kode VBA kita. Seperti sebelumnya, akan muncul notifikasi kotak pesan. Ikuti langkah-langkah di bawah ini untuk melakukan tindakan ini

LANGKAH

• Pertama, pilih rentang data (B4. D9)
• Selanjutnya, klik kanan area yang ingin Anda edit. Pilih opsi Format Sel

• Lalu, buka tab Proteksi di kotak dialog Format sel. Hapus centang opsi Terkunci
• Sekarang, klik OK

• Selain itu, klik kanan pada tab lembar 'Tanpa Kata Sandi'. Pilih Lihat Kode

• Tindakan di atas akan membuka jendela kode VBA kosong
• Setelah itu, masukkan kode berikut di jendela kode itu

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
ActiveSheet.Unprotect
For Each aCell In Target
If aCell.Value <> "" Then
Trial = MsgBox("Is this value correct? You can not change after entering a value.", _
vbYesNo, "Message Box Notifation")
If Trial = vbYes Then
aCell.Locked = True
Else
aCell.Value = ""
End If
End If
Next aCell
ActiveSheet.Protect
End Sub

• Untuk menjalankan kode, tekan tombol F5 atau klik tombol Jalankan

• Setelah itu, untuk menjalankan kode, buat makro. Kami membuat makro bernama VBA2
• Jadi, pilih VBA2 dan tekan tombol Run

• Selain itu, masukkan nilai di sel D5
• Hasilnya, kita bisa melihat kotak pesan. Ini menanyakan apakah nilainya sesuai atau tidak. Kami tidak dapat mengedit nilai setelah kami memasukkannya ke dalam sel itu

• Setelah itu, coba edit sel D5 sekali lagi
• Kami akan menerima kotak pesan yang mirip dengan gambar di bawah ini

• Mengikuti, untuk mengedit ulang nilainya, pertama-tama kita harus membuka proteksi sheet
• Jadi, Anda harus membuka Review > Unprotect Sheet

• Kali ini kami tidak memerlukan kata sandi apa pun untuk membuka proteksi lembar
• Terakhir, klik sel D5 sekali lagi. Sel D5 sekarang tidak terkunci. Jadi, setelah memasukkan nilai, kita bisa mengeditnya

Baca Selengkapnya. Lindungi Sel Excel Tapi Izinkan Entri Data (2 Metode Cepat)

Bacaan Serupa

• Cara Membuka Kunci Sel di Excel Saat Menggulir (4 Cara Mudah)
• Excel VBA untuk Mengunci Sel tanpa Melindungi Lembar (4 Contoh Ideal)
• Cara Melindungi Sel Excel dengan Rumus (2 Metode Sederhana)

2. Mengunci Rentang Sel Tertentu

Dalam metode terakhir, kami akan menerapkan Excel VBA untuk rentang sel tertentu. Jadi, kami akan dapat mengunci sel setelah entri data hanya untuk rentang sel tersebut. Selain itu, setiap upaya untuk mengubah setelah memasukkan data dalam rentang tersebut akan menampilkan pemberitahuan di kotak pesan. Mari kita lihat langkah-langkah untuk melakukan tindakan ini

LANGKAH

• Pertama, pilih rentang sel (D5. D9)
• Selanjutnya, klik kanan pada rentang yang dipilih. Pilih opsi Format Sel

• Ketiga, di kotak dialog Format Sel, buka Perlindungan Pastikan kotak Terkunci tidak dicentang
• Sekarang tekan tombol OK

• Selanjutnya, klik kanan pada lembar bernama Range. Pilih opsi Lihat Kode

• Perintah di atas membuka jendela kode VBA
• Kemudian, ketikkan kode berikut di jendela kode kosong itu

Dim zRg As Range
Dim zStr As String
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("D5:D9"), Target) Is Nothing Then
Set zRg = Target.Item(1)
zStr = zRg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aRg As Range
On Error Resume Next
Set aRg = Intersect(Range("D5:D9"), Target)
If aRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="1234"
If aRg.Value <> mStr Then aRg.Locked = True
Target.Worksheet.Protect Password:="1234"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("D5:D9"), Target) Is Nothing Then
Set zRg = Target.Item(1)
zStr = zRg.Value
End If
End Sub

• Sekarang, klik tombol Jalankan atau tekan tombol F5 untuk menjalankan kode

• Selanjutnya buat macro bernama VBA3. Pilih makro itu dan klik tombol Jalankan

• Setelah itu, masukkan nilai di sel D5

• Terakhir, coba edit nilai sel D5. Kotak pesan seperti gambar berikut akan muncul. Tapi kita akan bisa mengedit nilai di luar rentang sel (D5. D9)

Baca Selengkapnya. Cara Melindungi Sel Tertentu di Excel (2 Cara yang Cocok)

Kesimpulan

Kesimpulannya, tutorial ini menunjukkan 2 metode untuk mengunci sel setelah entri data menggunakan Excel VBA. Setiap upaya untuk mengedit nilai sel terkunci akan menampilkan pemberitahuan di kotak pesan. Unduh lembar kerja latihan yang terdapat dalam artikel ini untuk menguji kemampuan Anda. Jika Anda memiliki pertanyaan, silakan tinggalkan komentar di kotak di bawah ini. Tim kami akan berusaha membalas pesan Anda sesegera mungkin. Nantikan solusi Microsoft Excel yang lebih inventif di masa mendatang

Mengapa Excel mengunci sel secara otomatis?

Bagaimana Anda segera mengunci sel secara otomatis setelah data dimasukkan di lembar Google?

Bisakah Anda mengunci sel secara kondisional di Excel?

Bagaimana cara mengunci spreadsheet Excel secara otomatis?