Macro to hide formulae

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
 
T

Trevor Shuttleworth

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
 
M

Mike

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
 
T

Trevor Shuttleworth

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
 
M

Mike

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top