Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting and Hiding Formula
Hello Everyone,
I'm a novice with VBA coding; however, I would like to find a way to protect all the formulas within my worksheet without having to set a password protection on the sheet. If this can be achieved using a VBA coding I would greatly appreciate any assistance anyone can offer. Thanks, Jade |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting and Hiding Formula
Are you talking about protecting formulas so a user cannot delete them ? If you select the cells that are to have data put into them, select format cells go to protection and uncheck locked, now you can protect the sheet and the cells that you unlocked will be able to have data inputted into them, the cells with the formulas cannot be altered -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=519071 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting and Hiding Formula
Hi Dave,
Actually I want to hide the formula from viewing by other users and would prefer not to protect the sheet. Is there a vba code that can hide the formula then I will password protect the vba project via properties. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting and Hiding Formula
XL's Formula hiding only works if the worksheet is protected. There's no
way to use VBA to hide the formula without worksheet protection. You could use the Worksheet_Calculate event macro to calculate your cells in code, instead of using formulae. However, you should be aware that bypassing the VBA project password (like worksheet and workbook passwords) is a trivial exercise, so you shouldn't expect it to provide any actual security. In article .com, "Jade" wrote: Hi Dave, Actually I want to hide the formula from viewing by other users and would prefer not to protect the sheet. Is there a vba code that can hide the formula then I will password protect the vba project via properties. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting and Hiding Formula
Hi Jade,
there are at least two possibilities: 1) If the formulas you want to protect are used for providing data which will be used by other formulas (i.e. that the result of the formula is not needed to be visible) then simply do the following: in the standard menu select Format - Cell - Number - User defined and type in ";;;". Then the cell appears to be empty. Only if you select it, the formula is visible. 2) The formulas can be protected by running the following macro. It checks all cells and if they do not contain a formula they are unprotected. In the other case this cell is protected. Then the whole sheet is protected. Effect: all cells can be modified but those with a formula. Sub ProtectFormula() Dim rngActiveCell As Range Application.ScreenUpdating = False ActiveSheet.Unprotect For Each rngActiveCell In ActiveSheet.UsedRange If Not rngActiveCell.HasFormula Then rngActiveCell.Locked = False ElseIf rngActiveCell.HasFormula = True Then rngActiveCell.Locked = True End If Next ActiveSheet.Protect Application.ScreenUpdating = True End Sub Of course, you can combine both and extend the macro which makes the cells containing formulae appearing blank. Hope this was of any help for you Udo |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting and Hiding Formula
Hey Udo,
Thanks for the coding. Truth be told, I'm still a novice at this VBA programming. I'm basically looking to hide the formula from viewing by other users so the coding you provided should do the trick. Thanks for the guidance. I'm happy I joined this site already :-]....I've been hammering it out on my own and am thankful to find a forum of individuals who've been doing the same things and getting results. Once again, thanks for the recommendation. Warm regards, Jade |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protecting and Hiding Formula
I guess you are aware that
ActiveSheet.Protect protects the sheet. -- Regards, Tom Ogilvy "Jade" wrote in message ups.com... Hey Udo, Thanks for the coding. Truth be told, I'm still a novice at this VBA programming. I'm basically looking to hide the formula from viewing by other users so the coding you provided should do the trick. Thanks for the guidance. I'm happy I joined this site already :-]....I've been hammering it out on my own and am thankful to find a forum of individuals who've been doing the same things and getting results. Once again, thanks for the recommendation. Warm regards, Jade |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding and protecting formula | Excel Discussion (Misc queries) | |||
Protecting or Hiding VBa Code In a Workbook | Excel Programming | |||
Protecting and Hiding formulas | New Users to Excel | |||
Protecting (hiding) the Code in an Add-in | Excel Programming | |||
Protecting / Hiding the Code | Excel Programming |