Macro to hide formulae

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have 2 short macros that protect and unprotect all formulae in a workbook
:

Sub UnprotectAllSheets()

Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="password"
Next n
Application.ScreenUpdating = True

End Sub
Sub ProtectAllSheets()

Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="password"
Next n
Application.ScreenUpdating = True

End Sub


Can these be easily changed so that the formulae are actually hidden?

Thanks in advance

Mike
 
Mike

you don't need to change the macros at all. Just mark all the cells with
formulae as "hidden" in the format | cells | protection tab

When the sheet is protected the formulae will be hidden.

Regards

Trevor
 
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, I want the
user to have "protect/unprotect formulae" buttons (using the macros shown,
but where they would still see the formulae) but also "hide/unhide formula"
buttons (I guess by adapting the macros shown somehow, the protects as
before but also hides all formulae from sight).

Thanks again

Mike
 
Mike

OK, modify your routine thus:

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
With Sheets(n)
.Cells.FormulaHidden = True
.Protect Password:="password"
End With
Next 'n
Application.ScreenUpdating = True
End Sub

Regards

Trevor
 
Trevor

Thanks very much for this - I was sure that it wouldn't be too difficult to
adapt. Will let you know if any problems as not working on it this morning.

Regards

Mike
 
Back
Top