Hello all I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook Is there a way please to allow filtering or sorting but still lock down the worksheet Hi team. When I lock protect and run the macro, it pops me an error. I hope this give a better picture of my problem. Please see for guidance about the ways you can receive support and provide feedback. In the Protect Sheet dialog, type the password in the Password to unprotect sheet text box, and in Allow all users of this worksheet to list to check Sort and Use AutoFilter options. The default value is True.
Code changes will be fully allowed. This will prevent users from editing the cells. Step 1: Make cells editable so that sorting will work. You will be able to use some AutoFilter functions, but not Sort Ascending or Sort Descending. This will enable everything you want and prevent a user from saving changes. I would have thought selecting 'Sort' in the protection dialog would be sufficient. All users will be able to use AutoFilter, but they will not be able to remove the AutoFilter arrows from the column headers.
. The user will be able to manipulate the data, but they cannt save the file. Get interesting article about Vba Protect Worksheet Allow Autofilter that may help you. Protects a worksheet so that it cannot be modified. Can you help me with this? Thanks and I have learned how to do things on here that I did not even know I wanted to do! Now all specified sheets have been protected but allowed to sort and filter. See screenshot: Then the users can sort and filter in this protected sheet Tip. To start viewing messages, select the forum that you want to visit from the selection below.
If I unprotect the worksheet manually and protect it again using the above code, it works normally. Am I using the protection worksheet the wrong way? Here are the protect method's parameters: sheetName. For example, ascending sorts and descending sorts would have to be written in separate macros. You can definitely use it, if you enable it, but adding filters after protection? Cheers, Shane Devenshire Shane Devenshire. When you protect the sheet there was an option in the Protect Sheet dialog box near the bottom called Use AutoFilter, just check it.
If the Lock Cell command is enabled, click Lock Cell to unlock the selected cell. By default, all cells in an Excel worksheet are locked. The AllowFiltering property allows the user to change filter criteria on an existing AutoFilter. The rest of the worksheet works normally. I unlocked all cells on the page - and then locked only the cells I needed locked - then protected the worksheet. The default value is True. An expression that returns a Worksheet object.
This tutorial is about the steps required for enabling auto filter for a protected worksheet in Excel 2010. Prepare the Worksheet The first step is to unlock cells where changes can be made. In some workbooks, you might want to allow users to make changes to a pivot table, but you need to protect formulas or data in other areas of the worksheet. I assume it is RngAll. Another benefit is that you have the option of allowing only certain users to sort by giving the range a password. I have a Table with several columns and lots of rows.
The default value is False. True allows the user to format any column on a protected worksheet. About Worksheet Protection Properties When you protect a sheet, Excel allows you to select from 15 different permissions you want to give to all viewers of the worksheet. AllowFiltering expression A variable that represents a object. So you need to do nothing to make the header row unupdatebale. How to allow sorting and Filter locked cells in protected sheets? The user cannot create or remove an AutoFilter on a protected worksheet. I don't remember if the name is different on earlier versions of Excel.
Users can set filters on an existing auto filter. If there are multiple sheets needed to protect and allow users to sort and filter, you can apply Protect Worksheet utility of Kutools for Excel to protect multiple sheets at one time. Unprotect ' Unlock row 1. Remarks The AllowFiltering property can be set by using the method arguments. But when I try to run the same autofilter via the vba code, I get the error message: Run time error 1004 - You cannot use this command on a protected sheet. When you protect a worksheet, there is an option to allow filtering AutoFilter.
Then, turn on the worksheet protection. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Then the AutoFilter arrows will appear on the column headers. Is this some sort of macro required to do this? True allows the user to format any row on a protected. Making ranges editable for ranges of cells makes the cells behave like unlocked cells for the most part e. In excel cell the locked propery is set true by default.