Excel blocking cells with macro - in need of Autofilter

[LeysenS]

Hello,

I am looking for an adjustment or solution for my macro in Excel.

This macro blocks cells once they are filled in by a user. We do this to protect the filled in values.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
ActiveSheet.Unprotect ' Password:="xxx"
For Each cel In Target
If cel.Value <> "" Then
cel.Locked = True
End If
Next cel
ActiveSheet.Protect ' Password:="xxx"
End Sub


Problem:

Once a cell is filled in and blocked, the autofilter of the first column of my worksheet is also blocked.
We definitely need this Autofilter, the file contains over 15 000 rows ..

The setting for "Allow users to use Autofilter" doesn't work because the macro overwrides this setting every time.

Is there a way to still use an Autofilter on blocked cells?
Or a way to indicate that a specifief column never gets blocked?

Thank you in advance!

Kind regards,
Leysens

[AlphaFrog]

When you re-protect the sheet, set the AllowFiltering argument to True.

Please Login or Register  to view this content.