I suggest protecting the sheet when appropriate and unprotecting it when the filter row (eg Row 1) is selected. I came here recently with the same issue. This is a very old, but still very useful thread. MsgBox "Your action was undone because it made changes to a locked cell.",, "Action Undone"
HIGHLIGHT LOCKED CELLS EXCEL 2007 CODE
Combined with Lorie's answer, I was able to achieve the desired result of being able to sort/filter a protected sheet, while still allowing a user to make changes to an unprotected cell.įollow the instructions in Lorie's answer, then put the following code in the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) It also displays a message explaining why the action was undone. I modified Isaac's code a bit to undo changes if ANY of the cells in the target range are locked. Isaac's answer is great, but doesn't work if the user highlights a range that has both locked and unlocked cells. Lorie's answer is good, but if a user selects a range that contains locked and unlocked cells, the data in the locked/protected cells can be deleted. WsMainTable.Protect Password:=PROTECTION_PASSWORD, UserInterfaceOnly:=True WsMainTable.Unprotect Password:=PROTECTION_PASSWORD Private Sub Worksheet_SelectionChange(ByVal Target As Range) This leaves countless ways the users could mess up the data while also causing some usability issues, but at least reduces the odds of pesky co-workers thoughtlessly making unwanted changes. It's far from an ideal solution however.ĥ) You could keep the sheet protected when the user is selecting the data and unprotected when the user has the header is selected. For example, detect and revert changes using Worksheet_Change.
![highlight locked cells excel 2007 highlight locked cells excel 2007](https://ozvoper.weebly.com/uploads/1/2/4/2/124255299/758902816.jpg)
I think there are two methods, either (1) get the code to unprotect the sheet, apply the sort, then re-protect the sheet, or (2) have the sheet protected using UserInterfaceOnly:=True.ģ) Lorie's answer which does not allow users to select cells ( )Ĥ) One solution that I haven't seen discussed is using VBA to provide some basic protection.
HIGHLIGHT LOCKED CELLS EXCEL 2007 HOW TO
There are other posts explaining how to do this.
![highlight locked cells excel 2007 highlight locked cells excel 2007](https://www.lifewire.com/thmb/wjhduVOtgzbPYkPGOvJMlTMnoRs=/400x0/filters:no_upscale():max_bytes(150000):strip_icc()/how-to-lock-cells-and-protect-data-in-excel-worksheets-3-5c3bad1c4cedfd00016a00d2.jpg)
Your options are:Ģ) Apply protection and create buttons with code to sort using VBA. The prevailing answer is that you can't protect content from editing while allowing unhindered sorting. Make sure the Transition Navigation Keys check box is unchecked.There are a number of people with this difficulty.The advanced options of the Excel Options dialog box. Both of the categories should be near the very bottom of the options.) (See Figure 1.)įigure 1. (Make sure you don't confuse the Lotus Compatibility category with the Lotus Compatibility Settings category. Scroll through the options until you see the Lotus Compatibility category.Click Advanced at the left of the dialog box.In Excel 2010 and later versions, display the File tab of the ribbon and then click Options.) (In Excel 2007 click the Office button and then click Excel Options.
![highlight locked cells excel 2007 highlight locked cells excel 2007](https://www.exceltip.com/wp-content/uploads/2013/07/image-115.png)