Auto lock cells after it is filled and also be able to use filters

mve153

New Member
Joined
Dec 11, 2019
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hey guys, what's up?

Here at my company we use a sheet where people fill in actions that need to be done and the deadlines. What i need is that the cells to be protected after it is filled and for that i found many codes likes this:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("I3:I1000")) Is Nothing Then
Me.Unprotect ("123")
Target.Locked = True
Me.Protect ("123")
End If
End Sub


That code does work, but after i fill the cell and it gets locked, i'm not able anymore to sort or use filters on the sheet. We really need the filter to see the status of the actions.

Could you help me please?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
You cannot use sort if any cell in the sort range is locked & the sheet is protected, although it is possible to filter.
Which is most important? The ability to sort or protecting the cells
 
Upvote 0
You cannot use sort if any cell in the sort range is locked & the sheet is protected, although it is possible to filter.
Which is most important? The ability to sort or protecting the cells
It's more important to filter after protected, but I cannot even clicl the filter buttons after locked
 
Upvote 0
You mentioned the ability to sort, so can you please answer this question
Which is most important? The ability to sort or protecting the cells
 
Upvote 0
I guess protecting is more important

How about performing the sorting with code which you can assign to a control or a button on the worksheet ? The code would temporarly unprotect the worksheet , do the sorting and protect back the worksheet.
 
Upvote 0
So what is the answer for locking cells that are filled? I have the same problem. I want to lock cells that are not empty and all others can be filled as needed without unlocking. Still need to filter but dont need to sort, in fact I want to make sure it can't be sorted. After password is entered is only time all cells can be edited.
 
Upvote 0
As I said in post#2 you can filter, but not sort.
So you should be ok. :)
 
Upvote 0
So is it the usual password protect on the ribbon I use or do I need a Macro?
 
Upvote 0
Yes you can do it from the ribbon. :)
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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
Back
Top