VBA Code to lock the cell after data entering with maintaining the filter option

Tariq384

New Member
Joined
Jul 23, 2020
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
I have a protected worksheet and need to have specific cells locked after data is entered. I would also like to keep the filter option working after locking the cell( protect the sheet). I currently have a working code that does what I'm looking for but it covers ALL the unlocked cells on the sheet where data can be entered, I only need to keep the filter option working

Please advise

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect Password:="01234"
For Each cl In Target
If cl.Value <> "" Then
check = MsgBox("Is this entry correct? This cell cannot be changed after entering this value. For assistance call Ext: 301", vbYesNo, "Cell Lock Notification")
If check = vbYes Then
cl.Locked = True
Else
cl.Value = ""
End If
End If
Next cl
ActiveSheet.Protect Password:="01234"
End Sub
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Watch MrExcel Video

Forum statistics

Threads
1,118,795
Messages
5,574,348
Members
412,587
Latest member
Krucial155
Top