Hi All,
I'm creating a report where the end user will not be allowed to delete rows/columns or change values inside the cells.
Each variation of protecting the worksheet I've tried has resulted in the disabling of sorting. I need for this to be a capability (sorting and filtering will be necessary for this report). Is there a macro I can use to give users the capability to filter and sort while preventing them from deleting/adding rows and columns and editing cell values?
The last two options when protecting a worksheet are to allow users to sort and to use autofilters, so select those options when protecting the sheet.
Bernie Deitrick
Excel MVP 2000-2010
When I check to allow users to sort and use autofilter I still am not able to sort data. Message pops up: "The cell or chart you're trying to change is on a protected sheet"
For filtering, the filter needs to be applied before protection. For sorting, unfortunately, the cells must be unlocked.
You could also use the worksheet change event to undo any changes.
1) Copy this code.
2) Right-Click the sheet tab of interest.
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes as needed
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End Sub
When a worksheet is protected all actions to modify manually are blocked.
However when you have vba code that makes the changes you can add a parameter to the code protecting the worksheet that will allow changes made by a macro and not by the user.
Please Login or Register to view this content.
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks