Macro to hide formulae
 

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

Macro to hide formulae
 

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


"Mike" wrote in message
...
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

Macro to hide 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, 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



"Trevor Shuttleworth" wrote in message
...
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


"Mike" wrote in message
...
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

Macro to hide formulae
 

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


"Mike" wrote in message
...
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



"Trevor Shuttleworth" wrote in message
...
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


"Mike" wrote in message
...
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

Macro to hide formulae
 

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

"Trevor Shuttleworth" wrote in message
...
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


"Mike" wrote in message
...
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



"Trevor Shuttleworth" wrote in message
...
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


"Mike" wrote in message
...
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