Automatically lock a cell after inputed data

[Wagbo]

Hi All

I am trying to protect a cells value once it has been input
The code I am using works. However, it stop the user from using the auto filter function
This is the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As RangeOn Error Resume NextSet xRg = Intersect(Range("A1:F8"), Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="123"
xRg.Locked = True
Target.Worksheet.Protect Password:="123"
End Sub

Can anyone help me to protect the cell and still use the auto filter
Thanks in advance

 

[GraH - Guido]

Hi,
How did you protect the sheet? Try with checking the option Use AutoFilter.

 

[Wagbo]

It is automatically protected using VBA once the data has been input

 

[GraH - Guido]

Hi, I got that from the initial post. Please check the options manually. Then try your code again.
I think it will work then.

 

[Wagbo]

I have tried to do it manually but when the vba locks the cell it reverts back to the original state. I think the code is telling it to lock everything. Maybe I need a another line somehow telling to allow filter

 

[GraH - Guido]

with macro recorder I got

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
  , AllowFiltering:=True

 

[Wagbo]

Would that be put into worksheet_change

 

[GraH - Guido]

Yes, after your initial code

 Target.Worksheet.Protect Password:="123"

put all the parameters you require.
for reference: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-protect-method-excel

 

[Wagbo]

Thanks so much for the help, it works a treat

 

[Wagbo]

Just been testing out and all works well but you can unprotect the cells without a password any ideas

 

[NARAYANK991]

Hi ,

See if this link helps :

https://www.mrexcel.com/forum/excel-questions/449815-vba-password-protect-sheet-allow-filtering.html

Narayan