Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros to hide/protect formulae
(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) 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros to hide/protect formulae
first select all the cells by square button .. i.e the button on th
left side. ctlr + 1 will show format dialogue box. go to protection here deselect the lock.. now again. select the cells you want to protect. ctlr + 1 here select lock cell for allowing to see the forumla but it will no allow to update.. or select hidden to hide the forumula.. Now goto tools protection..protect sheet. give the password. -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros to hide/protect formulae
Thanks for the reply. It's a fairly large model, so to mark all cells as you
suggest might be very time-consuming. More importantly though, whilst I want the user to have "protect/unprotect formulae" buttons (using the macros shown, which I'm happy with, but where of course they can still see the formulae but they cannot change) but also give the user "hide/unhide formulae" buttons (I guess by adapting the macros shown somehow, that protects as before but also hides all formulae from sight in the same operation, with the "unhide" being the reverse of both elements from running the first macro). I want these buttons to be used quickly by users without any need to go in to formatting protection settings on cells manually. Thanks again for any help Mike "nisht " wrote in message ... first select all the cells by square button .. i.e the button on the left side. ctlr + 1 will show format dialogue box. go to protection here deselect the lock.. now again. select the cells you want to protect. ctlr + 1 here select lock cell for allowing to see the forumla but it will not allow to update.. or select hidden to hide the forumula.. Now goto tools protection..protect sheet. give the password.. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect formulae in template | Excel Discussion (Misc queries) | |||
protect cells in excel to prevent users from deleting formulae | Excel Discussion (Misc queries) | |||
Protect cell formulae | New Users to Excel | |||
Macro to protect/hide formulae | Excel Programming | |||
Macro to protect/hide formulae | Excel Programming |