Protecting and Hiding Formula

[Jade]

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

 

[davesexcel]

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

 

[Jade]

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.

 

[JE McGimpsey]

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.

 

[Udo]

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

 

[Jade]

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

 

[Tom Ogilvy]

I guess you are aware that

ActiveSheet.Protect

protects the sheet.