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

Status
Not open for further replies.

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.", 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
 

Attachments

  • Capture.PNG
    Capture.PNG
    30.8 KB · Views: 5

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Status
Not open for further replies.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,553
Messages
5,838,075
Members
430,527
Latest member
MyFace2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top