Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulae, conditional formatting & macro security | Excel Discussion (Misc queries) | |||
Macros to hide/protect formulae | Excel Programming | |||
Macro to protect/hide formulae | Excel Programming | |||
Macro to protect/hide formulae | Excel Programming | |||
Formulae for Macro based files | Excel Programming |