uvmoon.blogg.se - Excel for mac lock sort

#Excel for mac lock sort how to
#Excel for mac lock sort code
#Excel for mac lock sort password

option, then sharing again after adding the protection. You could temporarily remove the sharing option by going into Review / Share workbook and then unticking the allow other users.

#Excel for mac lock sort code

You cannot protect a sheet or use VBA code on a shared workbook.

Go to the Data ribbon and click the Clear icon in the Sort & Filter group. This is going to be difficult to do in a way that no one could get around.

Click on the Filter icon next to the heading and choose Clear Filter from “Name of Heading”.

To clear the filter, choose one of these options: Check the box next to the criteria you wish to match and click OK. Click on the arrow next to another heading to further filter the data. Click on the arrow next to the heading with which you want to filter, and you will see a list of all the unique values in that column. You will notice that all of your column headings now have an arrow next to the heading name.

Go to the Data ribbon, and then click Filter in the Sort & Filter group.

Go to the Home ribbon, click the arrow below the Sort & Filtering icon in the Editing group and choose Filter.

With filtered data, you can then copy, format, print, etc., your data, without having to sort or move it first. When data is filtered, only rows that meet the filter criteria will display and other rows will be hidden. Become proficient in business-focused Excel Increase speed 3X using keyboard shortcuts Clean and manipulate business data sets Filter and sort to investigate.

I can't find an event handler that recognizes selection of a filter button - so I gave the instruction to my users to first select the filter cell then click the filter button.In addition to sorting, you may find that adding a filter allows you to better analyze your data.

#Excel for mac lock sort password

My solution doesn't use password protection - I don't need it (its a safeguard, not a security feature). 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. 1.Select all the cells you would like the user to be able to sort, including their column headings. This allows users to edit these cells when the worksheet is protected, even if they are locked cells. MsgBox "Your action was undone because it made changes to a locked cell.",, "Action Undone" Add cells we want to sort to a range and make that range editable in Allow Users to Edit Ranges. 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. Select the column data you want to sort, and then click Data > Sort. As an author, you can disable the sort icons in published content. In Excel, you can use the Sort function to sort a column and keep rows. For example, detect and revert changes using Worksheet_Change. Alternatively, go to Worksheet > Clear > Sorts. 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.

#Excel for mac lock sort how to

There are other posts explaining how to do this. 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. There are a number of people with this difficulty.