Excel Protection (2000 SR1)

Topic

New Reply

I am needing to protect a portion of a sheet. I have used Format Cells and unselected the Lock option for the cells that I don’t want protected. After protecting the sheet I can alter the unlocked cells but I can’t sort them. Is there a way to sort after protecting a sheet?

Also, is there a way to allows users to make changes to cells but not delete rows or columns?

Reply | Quote

Replies

Once a sheet is protected, then sort is disabled. The only way to sort would be to unprotect, sort, and then protect it again. You could do all of that in a macro.

If you protect anything on a sheet, then deleting rows and columns should be disabled.

Reply | Quote

Thank you. I found a trigger on the worksheet that will fire is someone should change the sheet. Is there a way to limit it to just rows or columns being deleted.

Reply | Quote

What event are you looking at? If it is the worksheet change event, I do not think it fires on a delete.

Reply | Quote

I was looking at the change event and was hoping that I could get it to recognize ‘delete’.

Reply | Quote

Is the functionality of Data|Filter|Autofilter good enough for your purposes ? If yes, I can give you macro which will allow to use this feature on a protected worksheet.

Reply | Quote

Could you please post your solution for others. I am looking for a method to allow one of my users to use auto filtering on a protected sheet. Your solution may fit my needs as well.

Reply | Quote

You can enable Autolist and Grouping by VBA. However this is not saved with your workbook. Therefore the simplest way is to put this code on ThisWorkbook level of your workbook and adapt MySheetName to your database worksheet. If you want all sheets to be processed make a Loop.

Private Sub Workbook_Open()
‘Make sure that Grouping and Autofilter in MySheetName are operational/reset
With Worksheets(“MySheetName”)
.EnableOutlining = True
.Protect contents:=True, DrawingObjects:=True, userInterfaceOnly:=True
.EnableAutoFilter = True
‘If you want to expand the autofilter when starting, adapt and activate next line
‘.Range(“PutName_OR_YourRange”).AutoFilter Field:=1
MsgBox “Autofilter and Grouping in ” & .Name & ” now operational” ‘inactivate if too bothersome
End With
End Sub

Reply | Quote