Protecting cells once data is entered but still enabling autofilter

[s10smith]

Hi Everyone,


I'm a major fan of this website and I thought I could perhaps get some help on something that is stumping me. I have a worksheet that multiple people use and for some columns, once a user enters data I want to lock those cells so that they can't be changed again. I used the following macro:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range


Set MyRange = Intersect(Range("W10:W9900,AB10:AB9900,AM10:AM9900,AS10:AS9900"), Target)

If Not MyRange Is Nothing Then

Sheets("Sheet1").Unprotect Password:="password"

MyRange.Locked = True

Sheets("Sheet1").Protect Password:="password"

End If

End Sub


Problem is that once the macro runs and protects the worksheet, the autofilter option no longer works.


Any help? (I'm very new to VBA)

 

[Luke M]

Welcome to the forum!

When you protect the sheet, need to let XL know that you still want the AutoFilter to work. =)

[pre]

Sheets("Sheet1").Protect Password:="password", AllowFiltering:=True

[/pre]

 

[s10smith]

Hi Luke!


Thanks so much!


This might sound like a silly question but within the macro, would I put the statement you just posted right before the End If?


PS. I hope maybe I can become an excel Ninja!

 

[Luke M]

Correct, whole macro becomes:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range

Set MyRange = Intersect(Range("W10:W9900,AB10:AB9900,AM10:AM9900,AS10:AS9900"), Target)
If Not MyRange Is Nothing Then
Sheets("Sheet1").Unprotect Password:="password"
MyRange.Locked = True
Sheets("Sheet1").Protect Password:="password", AllowFiltering:=True
End If
End Sub

[/pre]

 

[webellr]

Thanks for the macro code

Software for POS http://www.trademeters.com/