Loncat ke daftar isi utama

Bagaimana cara melindungi sel berdasarkan tanggal di Excel?

Biasanya, kami dapat melindungi lembar kerja untuk mencegah orang lain mengedit atau mengubah nilai sel, tetapi, terkadang, Anda perlu melindungi sel berdasarkan tanggal. Misalnya, saya ingin melindungi sel lain tetapi hanya mengizinkan nilai sel dari tanggal hari ini untuk dimodifikasi seperti gambar berikut yang ditampilkan, artikel ini akan berbicara tentang cara melindungi sel berdasarkan tanggal.

doc lindungi pada tanggal 1

Lindungi semua baris kecuali baris tanggal hari ini dengan kode VBA

Lindungi semua baris yang tanggal telah berlalu dengan kode VBA

panah gelembung kanan biru Lindungi semua baris kecuali baris tanggal hari ini dengan kode VBA

Hanya izinkan baris yang sama dengan tanggal hari ini untuk dimodifikasi, kode berikut dapat membantu Anda, lakukan seperti ini:

1. Klik kanan tab lembar yang ingin Anda proteksi sel berdasarkan tanggal, lalu pilih Lihat kode dari menu konteks, di-pop-out Microsoft Visual Basic untuk Aplikasi jendela, silakan salin dan tempel kode berikut ke dalam Modul kosong:

Kode VBA: Lindungi semua baris kecuali baris tanggal hari ini:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20161025
    If Range("E" & Selection.Row).Value <> Date Then
        ActiveSheet.Protect Password:="111111"
        MsgBox "Only today's date row can be edited!", vbInformation, "Kutools for Excel"
    ElseIf Range("E" & Selection.Row).Value = Date Then
        ActiveSheet.Unprotect Password:="111111"
        ActiveSheet.EnableSelection = xlNoRestrictions
    End If
End Sub

doc lindungi pada tanggal 2

Note: Dalam kode di atas, huruf E adalah tajuk kolom tempat tanggalnya berada, "111111"Adalah kata sandi untuk melindungi lembar ini. Anda dapat mengubahnya sesuai kebutuhan Anda.

2. Kemudian simpan dan tutup jendela kode ini.

(1.) Jika Anda mengklik sel lain di luar baris tanggal hari ini, kotak prompt akan muncul untuk mengingatkan Anda bahwa sel tidak dapat diedit, lihat tangkapan layar:

doc lindungi pada tanggal 3

(2.) Jika Anda mengklik dan mengedit baris sama dengan tanggal hari ini, itu akan berhasil diubah, lihat tangkapan layar:

doc lindungi pada tanggal 4

panah gelembung kanan biru Lindungi semua baris yang tanggal telah berlalu dengan kode VBA

Jika Anda perlu melindungi semua baris yang telah lewat tanggal, hanya izinkan baris tanggal hari ini dan yang akan datang untuk diubah, harap terapkan kode VBA berikut:

1. Klik kanan tab lembar yang ingin Anda proteksi sel berdasarkan tanggal, lalu pilih Lihat kode dari menu konteks, di-pop-out Microsoft Visual Basic untuk Aplikasi jendela, silakan salin dan tempel kode berikut ke dalam Modul kosong:

Kode VBA: Lindungi semua baris tanggal telah berlalu:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updateby Extendoffice 20161025
  Dim xRow As Long
  xRow = 2
  ThisWorkbook.ActiveSheet.Unprotect Password:="111111"
  ThisWorkbook.ActiveSheet.Cells.Locked = False
  Do Until IsEmpty(Cells(xRow, 5))
    If Cells(xRow, 5) < Date Then
      Rows(xRow).Locked = True
    End If
    xRow = xRow + 1
  Loop
  ThisWorkbook.ActiveSheet.Protect Password:="111111"
End Sub

doc lindungi pada tanggal 5

Note: Pada kode di atas, angka 5 adalah nomor kolom tempat tanggal berada, "111111"Adalah kata sandi untuk melindungi lembar ini. Anda dapat mengubahnya sesuai kebutuhan Anda.

3. Kemudian simpan dan tutup jendela kode ini.

(1.) Jika Anda mengklik tanggal sel telah berlalu, kotak prompt akan muncul untuk mengingatkan Anda bahwa sel tidak dapat diedit, lihat tangkapan layar:

doc lindungi pada tanggal 6

(2.) Jika Anda mengklik sel baris untuk mencoba mengubah nilai pada tanggal hari ini atau tanggal yang akan datang, itu akan berhasil dimodifikasi, lihat tangkapan layar:

doc lindungi pada tanggal 7

Alat Produktivitas Kantor Terbaik

Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria  |   VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar   |  Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel   |  Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas:  12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Canggih untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

tab kte 201905

Office Tab Menghadirkan antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (30)
No ratings yet. Be the first to rate!
Miguel
Miguel
This comment was minimized by the moderator on the site
Hello, I would like to protect future days and allow editing current and past days. How do I do that?
Reply
0
0
skyyang
skyyang     Miguel
This comment was minimized by the moderator on the site
Hello, Miguel,To protect the future days, please apply the below code:<div data-tag="code">Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updateby Extendoffice
Dim xRow As Long
xRow = 2
ThisWorkbook.ActiveSheet.Unprotect Password:="111111"
ThisWorkbook.ActiveSheet.Cells.Locked = False
Do Until IsEmpty(Cells(xRow, 5))
If Cells(xRow, 5) > Date Then
Rows(xRow).Locked = True
End If
xRow = xRow + 1
Loop
ThisWorkbook.ActiveSheet.Protect Password:="111111"
End SubPlease try, hope it can help you!
Reply
Report
0
0
avtandil
avtandil
This comment was minimized by the moderator on the site
hello i did it but when i press cell message shows that it can not be edited but when i press keyborad i still can write in the cell
Reply
0
0
mouzahem
mouzahem
This comment was minimized by the moderator on the site
Hello, I tried to do the same but to Protect all columns except today’s date column with VBA code by replacing "Selection.Row" with "Selection.Columns" and "E" with "5" (row number where dates are), but I keep receiving error 1004!

Any help please?

Thank you
Reply
Report
0
0
skyyang
skyyang     mouzahem
This comment was minimized by the moderator on the site
Hello,
To apply this code to columns that you need, please use the following VBA code:(Note: In the code, 5 is the row number contains the date, please change it to your need.)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
Set xRg = Target.Worksheet.Cells(5, Target.Column)
If xRg <> Date Then
ActiveSheet.Protect Password:="111111"
MsgBox "Only today's date row can be edited!", vbInformation, "Kutools for Excel"
ElseIf xRg.Value = Date Then
ActiveSheet.Unprotect Password:="111111"
ActiveSheet.EnableSelection = xlNoRestrictions
End If
End Sub

Please try, hope it can help you!
Reply
Report
0
0
rohit Khanna
rohit Khanna     skyyang
This comment was minimized by the moderator on the site
Hi Im facing one problem, once i close the file and reopens it again on the same date, code doesnot work. pls help im stuck
Reply
0
0
skyyang
skyyang     rohit Khanna
This comment was minimized by the moderator on the site
Hi, rohit,
To keep the code, you should save your workbook as Excel Macro-Enabled Workbook format, please try, thank you!
Reply
Report
0
0
rohit Khanna
rohit Khanna     skyyang
This comment was minimized by the moderator on the site
Thanks for your help, Thanku so much buddy, It is a great help. and solved my tension.
Be safe
RegardsRohit Khanna
Reply
0
0
youssef
youssef     skyyang
This comment was minimized by the moderator on the site
Thank you! Sorry, I did not get your reply until now, it might be a problem in my email.

It worked! Can't say thank you enough! I really appreciate that!
Reply
0
0
skyyang
skyyang     youssef
This comment was minimized by the moderator on the site
Hi,
Glad it can help you!
Reply
Report
0
0
mouzahem
mouzahem     skyyang
This comment was minimized by the moderator on the site
Hi,

I am trying to expand this to protect columns that do not include this current month or/and this current week. I tried to replace "Date" with "month" or "week", but did not work.

I have attached a sample for weeks sheet where user cannot edit or type in columns other than this week.

Any advice on that? or shall I use actual dates to reflect a day in the month?
Reply
Report
0
0
Youssef
Youssef
This comment was minimized by the moderator on the site
Hello, I tried to do the same but to Protect all columns except today’s date column with VBA code by replacing "Selection.Row" with "Selection.Columns" and "E" with "5" (row number where dates are), but I keep receiving error 1004!

Any help please?

Thank you
Reply
0
0
deokarkaran
deokarkaran
This comment was minimized by the moderator on the site
Hi,

while running this code I am getting an error as shown in the figure
Also i am attaching a snapshot my excel where i want to make changes..

Can u guide in my code where should i make changes according to my excel file so that cells that contain only todays & future date can be editied ???
View Attachments (2 / 5)
Reply
Report
0
0
skyyang
skyyang     deokarkaran
This comment was minimized by the moderator on the site
Hello, Karan,
Because there are merged cells in your table, the above code can not be applied correctly in merged cells table.
If you have any other good ideas, please comment here. Thank you!
Reply
Report
0
0
Niall
Niall
This comment was minimized by the moderator on the site
As someone who is relatively new to VBA, would you mind explaining why xRow = 2 in Line 4?



Thanks
Reply
0
0
Piotr
Piotr     Niall
This comment was minimized by the moderator on the site
because in row no. 1 you have header
Reply
0
0
Bobby
Bobby
This comment was minimized by the moderator on the site
This works great. but how can I switch it to unlock a column based on dates in row 3?
Reply
0
0
tajindia52@gmail.com
This comment was minimized by the moderator on the site
Hello.....


I am using the code for Protect all rows except today’s date row........



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20161025
If Range("A" & Selection.Row).Value <> Date Then
ActiveSheet.Protect Password:="111111"
MsgBox "Only today's date row can be edited!", vbInformation, "Kutools for Excel"
ElseIf Range("E" & Selection.Row).Value = Date Then
ActiveSheet.Unprotect Password:="111111"
ActiveSheet.EnableSelection = xlNoRestrictions
End If
End Sub


After i save i and click on cell i get message only today's date row can be edited....but if i double click the cell the i can edit...pls help
Reply
Report
0
0
lmg777
lmg777
This comment was minimized by the moderator on the site
hi is it possible to lock certain cells depending on the date that is directly above them in a column?
Reply
Report
0
0
skyyang
skyyang     lmg777
This comment was minimized by the moderator on the site
Hi, Lusis,
Can you give your problem more specifically? Or you can attach a screenshot as your problem.
Thank you!
Reply
Report
0
0
lmg777
lmg777     skyyang
This comment was minimized by the moderator on the site
Hi skyyang

Yes here is a screenshot. Hope this helps.
Reply
Report
0
0
lmg777
lmg777     lmg777
This comment was minimized by the moderator on the site
Here it is.
View Attachments (1 / 5)
Reply
Report
0
0
skyyang
skyyang     lmg777
This comment was minimized by the moderator on the site
Hello, Lucis,
Do you mean to lock the columns which date is past, and only the columns of today and future days can be modified?
View Attachments (1 / 5)
Reply
Report
1
0
lmg777
lmg777     skyyang
This comment was minimized by the moderator on the site
Correct! How would I go about that?
Report
0
0
skyyang
skyyang     lmg777
This comment was minimized by the moderator on the site
Hi, Luis,
How about the date cell in A1? Is it entered manually or entered with a formula =now()? If it is a formula cell, the locked columns will be changed with the date changes.
Looking forward to your reply!
Report
0
0
lmg777
lmg777     skyyang
This comment was minimized by the moderator on the site
Hi Skyyangg

yes the cell A1 has the =now() formula.

thanks!
Report
0
0
Lmg
Lmg
This comment was minimized by the moderator on the site
Hi is it possible to lock columns by the date directly above the columns?
Reply
0
0
Deltran cu preto
Deltran cu preto     Lmg
This comment was minimized by the moderator on the site
Private Sub worksheet_Change(ByVal Target As Range)

'Subscribe to http://youtube.com/excel10tutorial

'Support the channel by donating on http://patreon.com/excel10tutorial

Dim col As Range

'Set the correct sheet name here:

With ThisWorkbook.Sheets("AGOSTO-22")

.Unprotect "ABCDE"

For Each col In .UsedRange.Columns

col.EntireColumn.Locked = col.Range("A1").Value <> Date

Next col

.Protect "ABCDE"

.EnableSelection = xlNoRestrictions

End With

End Sub
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations