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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
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
 

mve153

New Member
Joined
Dec 11, 2019
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
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
 

mve153

New Member
Joined
Dec 11, 2019
Messages
3
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You mentioned the ability to sort, so can you please answer this question

I guess protecting is more important
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,203
Office Version
  1. 2016
Platform
  1. Windows
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.
 

TammieOtts

New Member
Joined
Jul 21, 2020
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
As I said in post#2 you can filter, but not sort.
So you should be ok. :)
 

TammieOtts

New Member
Joined
Jul 21, 2020
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
So is it the usual password protect on the ribbon I use or do I need a Macro?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Yes you can do it from the ribbon. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,601
Members
417,154
Latest member
gm_jagath

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