This all makes perfect sense, however I just can't get it to work. When you switch to any other sheet, the workbook is free again. As for how to achieve 2 i. I am working on a spreadsheet a friend made with some pretty advanced formulas and macros embedded. Then press Ctrl + 1 keys to open the Format Cells dialog box.
Note: ActiveX controls, form controls, shapes, charts, SmartArt, Sparklines, Slicers, Timelines, to name a few, are already locked when you add them to a spreadsheet. Step 4: Save the Excel file for the changes to take effect. Once that permission is revoked, all files within that folder are automatically protected from deletion without having to set permission for each file individually. So even if you deny Delete access to a file, if the parent folder is set to Full Access the user will be able to delete the file all the same. Also you can press Alt + F11 to directly open it. However, I do not want them to have any access to my formulas at all for security purposes.
To lock formulas in Excel, checking the Locked option is not sufficient because the Locked attribute has no effect unless the worksheet is protected. The only way I can prevent the file from being able to delete is if I deny 'full control', but with this I am unable to access the file. If you do not see a Security tab, you may have to disable simple file sharing. Enable worksheet protection Worksheet protection is a two-step process: the first step is to unlock cells that others can edit, and then you can protect the worksheet with or without a password. Replace yourpassword with your real password.
But there is one way by which we can workaround this problem. For clarity: I'm fine with the user deleting some sheets, just not a couple of particular sheets. Because when I try to change a number in a cell, the formula goes away. Here 2 options in this passage are available for you if you need, which will disable delete sheet option in Excel. Format rows Use any of the row formatting commands, including changing row height or hiding rows Home tab, Cells group, Format button. Just that, only deleting, but the user must read, edit, and save the files.
Thank you for your clear explanation for locking and unlocking cells with formulas. Alternative Method: Use third party software If working with file permission is too much for you, you can try — a freeware that prevents deletion of important system files as well as user-selected files. Click Change Permission, and on the next window highlight Administrator and click Edit 5. Volatile Dim i As Long Dim sn As String With ThisWorkbook ReDim sn 1 To. Click the button on the top left corner of worksheet to select the whole worksheet, and then click the Selection Unlock button under the Design tab to unlock all cell of the worksheet. But if you expect the user to update the file, this won't work for you.
Now, go to lock the selected cells with formulas. Format cells Change any of the options in the Format Cells or Conditional Formatting dialog boxes. All contents Copyright 1998-2019 by MrExcel Publishing. Select the formulas you want to lock. The Question SuperUser reader boboes wants to know if there is a built-in method in Windows 7 and newer editions to lock a file and keep it from being overwritten or deleted: Is there a way to lock a file with in-house means so that it cannot be deleted or overwritten? If you applied conditional formatting before you protected the worksheet, the formatting continues to change when a user enters a value that satisfies a different condition. This parameter defaults to False to protect drawing objects it must be explicitly set to True. I thank you for reading and hope to see you again soon! Please remember that locking cells and hiding formulas has no effect until you protect the worksheet a short notice right underneath the Locked and Hidden options on the Format Cells dialog points to the next steps.
Kutools For Excel brings 300+ advanced features Combine workbooks, sum by color, split cell contents, convert date, and so on. Or, right-click any of the selected cells and choose Format Cells from the context menu. Feedback Buttons provided by - Copyright © 2019 DragonByte Technologies Ltd. This is how you can hide and lock formulas in Excel. You may have to before you can post: click the register link above to proceed. The protected files will remain there even if you try to delete the top-level folder.
Still, this isn't crazy complicated. Go ahead and choose Copy, as this will allow us to work with the current permissions and modify them to our liking. To protect the sheet, do the following. If you've before protecting the workbook, you may want to uncheck the Hidden checkbox after unprotecting the worksheet. Sometimes you may not want to go that far. If this is windows, try right-clicking the folder and choosing the Security tab.
With just setting permissions, you will forward those permissions to the files in the folder. So why not take this Excel feature to protect Excel sheets from being deleted? See the subsequent section for more information on how to enable sheet protection. If you meant something other than that, explain your task in more details and email us at with the workbook with data attached. Step 1: Open the Excel file. To make sure of this, select any cell with a formula, and look at the formula bar, the formula will still be there.