Denis Share Share this post on Digg Del.icio.us Technorati Twitter Self-preservation: For when you've got yourself in a jam ------------------------------------------------------ My site contains a number of Excel and Access Resources Reply Is it ethical for a journal to cancel an accepted review request when they have obtained sufficient number of reviews to make a decision? I hope this clears things up a bit. Users cannot apply or remove AutoFilters on a protected worksheet, regardless of this setting. have a peek here
How to react? Back on your sheet put a text box from the Drawing tools and enter some text in it like "Click Here to Sort". I then have a last column that > returns a specific word based on the value in the previous calculation > column. > > I need the first column with room trouble withsort.xlsx in this sheet, the cells are all locked to stop people fiddling and the sheet is protected with the options of selecting locked, unlocked cells, sorting and filtering enabled. http://blog.softartisans.com/2013/10/01/kb-sorting-locked-cells-in-protected-worksheets/
An industrious individual might open up the VB Editor and > see your code and use the password there to unprotect and modify the sheet. > But you can protect your For a version of this tip written specifically for earlier versions of Excel, click here: Sorting Data on Protected Worksheets. Thank you in advance for your help. Regards, DILIPandey
In the Allow Users to Edit Ranges dialog box, click Protect Sheet. Select unlocked cells Moving the pointer to cells for which the Locked check box is cleared on the Protection tab of the Format Cells dialog box. On the Protection tab, select the Locked check box, and then click OK. Vba Allow Sorting Protected Sheet One is protected and the other is not.
Where to Learn Excel & Advice Megathread Submission Guidelines and Sharing Questions Frequently Used Formulas Awarding ClippyPoints Thread flair: unsolved/solved Common VBA Mistakes /r/excel Guides and Write-ups /r/excel Wiki MICROSOFT COLLABORATION How To Filter Locked Cells In Excel The OP should reply to the answer with: Solution Verified This will change the flair to SOLVED and award the user a ClippyPoint TO APPLY CODE FORMATTING Use 4 spaces to An arrow should appear next to each column header. http://stackoverflow.com/questions/10197772/vba-excel-how-to-lock-specific-cells-but-allow-filtering-and-sorting How do I do it? (I do not want to use a macro and have a link they have to click before sorting).Thank you for your time and any help mahrus17
Enter your email address: .NET Blogroll Better Git with PowerShell Brij Hushan Leon Bambrick Mohamed Meligy Rob Conery Scott Guthrie BI Blogroll Andrew Brust Boyan Penev Chris Webb Richard Back Rob Allow Filtering On Protected Sheet Vba Register To Reply 08-09-2006,12:54 PM #5 Cathy Guest RE: How do I sort locked cells in Excel? I learned few things here and hope you also Now, the solution -> VBA (Macros).. I have a calculation column where the numbers in the previous > 5 columns are totaled (hidden column).
Click "OK" Step 2: Prevent users from editing these cells When protecting the worksheet, uncheck "Select Locked Cells" worksheet protection property. This will prevent users from editing the cells. Now, if you used a password to protect the sheet, you are going to have to modify the macro that was created. Excel Sort Locked Cells As royUK wrote The cells must also be unlocked in the table Do I need to use VBA to do this? How To Use Filter In Protected Excel Sheet Regards, DILIPandey
Feel free to give points to as many users in a single thread as you feel appropriate, but we ask you only give each user one point. If you didn't use one, you're good to go > on right now. > > To edit the macro, choose Tools | Macro | Macros and pick the one you > You can also right-click on that text box and choose Format and dress it up by giving it some color. But you can protect your VB Code also, with yet a different password (definitely write it down so you don't forget it because if you lose it, you can't change the Allow Filtering On Protected Sheet
http://www.technipages.com/excel-force-open-as-read-only-prompt permalinkembedsavegive gold[–]MattyB4x4[S] 0 points1 point2 points 1 year ago(1 child)I was under the impression that even though an item was read only, you could still manipulate the data, move it to another the filter option isn't available. I need the first column with room numbers, the hidden column with the calculation, and the last column with the word value to be locked so they cannot be manipulated. You're done there. > > Back on your sheet put a text box from the Drawing tools and enter some text > in it like "Click Here to Sort".
Insert hyperlinks Inserting new hyperlinks, even in unlocked cells. How To Lock Filter In Excel Insert rows Inserting rows. Delete rows Deleting rows.
Helpful Links ExcelTips FAQ ExcelTips Resources Ask an Excel Question Make a Comment Free Business Forms Free Calendars Tips.Net > Excel Home > Sorting > Sorting Data on Protected Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Nov 25th, 2013,12:45 PM #10 Iggy New Member Join Date Mar 2011 Posts 26 Re: Problem acessing 'sort' in Also I cannot expect the users of this sheet to be able to run macros. How To Use Filter In Protected Excel Sheet 2010 To do this, follow these steps: > > Press [Alt]+[F11] to get back into the VB Editor. > Choose Tools | VBAProject Properties > and click on the [Protection] tab >
The answer is quite easy: Excel allows you to specify what users can and cannot do with a protected worksheet. Register To Reply 08-08-2006,11:30 PM #4 JLatham Guest RE: How do I sort locked cells in Excel? The chart continues to reflect changes made to its source data. This finally solved the problem for me, hope it helps everyone who doesn't want to, or can't, use macros.
For example, detect and revert changes using Worksheet_Change. In the Enter the object names to select (examples) box, type the names of the users who you want to be able to edit the ranges. Guess that's why I'm a nurse and not a programmer...... "Redskinsfan" wrote: > Cathy, > > Well im a newbie but i had almost same prob as you with locked cells, EDIT: Btw, I don't think it's possible to add filters once you've protected a sheet.
This sheet HAS TO be protected when its distributed. You have said in your post that The sheet has been protected with the 3 options: Select unlocked cells, Sort and Filter.