Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to protect/hide formulae
I have an Excel model that needs to be distributed to a number of different
people. When first distributing to colleagues, the model will be completely unprotected. I want to give colleagues 2 options for them before they sent it externally, depending on their decision about who they're sending it to. First, I want colleagues to have a macro on a separate worksheet that when run protects all formulae (to simply prevent accidental delete/change, etc.). I'm after a macro that will do this. Second, I want colleagues to also have the choice of both protecting and hiding all formulae (as in some cases they'll not want the next, external recipient to even see the formulae) and I'm after a macro to do this also. [When they've chosen which option, they'll then just delete this separate worksheet.] It's a fairly large model, and I could provide versions that I have manually either just protected, or protected and hid, formulae. This is a fairly boring task for me and not very elegant. Is there a relatively straightforward way of doing this via macros? Or would the macros end up with a fair amount of script that effectively just replicates what I would otherwise do myself manually? If anyone has any thoughts they would be much appreciated. [Out of interest, is it possible to have macros to reverse these operations?] For simplicity, let's say I have Sheet 2 and Sheet 3 that contain formulae, both within the ranges (A1:A10). Thanks again Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to protect/hide formulae
If you select all the cells, then in Format Cells, Protection tab,
uncheck Locked and Hidden. Then run this macro: Code: -------------------- Sub Macro1() With Range("A1:A10") .Locked = True .FormulaHidden = True End With Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets ws.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True Next ws ActiveWorkbook.Protect Password:="password", Structu=True, Windows:=False Application.ScreenUpdating = True End Sub -------------------- This will lock, protect and hide the formulas (although results will show). --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to protect/hide formulae
Mike wrote:
(Apologies if this post appears twice: I first posted this a couple of hours ago but it has not shown up - to me at least - as posted, and I am keen to get this posted asap. I'm relatively new to this, so apologies if I'm not following the 'rules' and frustrate people) See my post above. It provides the solution to the first part. You can then attach this macro to a button. Easiest way to do that is to go to View Toolbars Forms Then click on the button, and draw an object on the sheet. You can name it whatever you want. Then select the button, right-click it and choose "Assign macro" and the macro above will be listed. Then choose OK. Try the button by clicking on it. [Out of interest, is it possible to have macros to reverse these operations?] Yes, in the macro above, you can change the True statements to False. To uprotect the sheets: Code: -------------------- Sub UnProtectAll() Application.ScreenUpdating = False ActiveWorkbook.Unprotect Password:="passowrd" Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Unprotect Password:="password" Next ws Application.ScreenUpdating = True End Sub -------------------- This should work for you. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to protect & hide Sheet | New Users to Excel | |||
Protect formulae in template | Excel Discussion (Misc queries) | |||
protect cells in excel to prevent users from deleting formulae | Excel Discussion (Misc queries) | |||
Protect hide worksheet | Excel Worksheet Functions | |||
Protect cell formulae | New Users to Excel |