MrExcel.com - Your One Stop for Excel Tips & Solutions

Protecting cells with formula, but retaining the ability to use the "data, group" function

Posted by Patrick Desbois on August 22, 2001 4:39 PM

I know how to partially solve a problem, regarding cell protection. It's easy to protect a whole sheet or specific cells, BUT if you have a series of columns / rows that are group, you'll lose the ability to view them grouped or ungrouped, since you're protecting cells within.

I know a way that uses "Data Validation", which is ALMOST perfect. This forbids anyone from inserting, by accident, a value where there's a formula (and you retain the ability to view grouped rows / columns). BUT it is possible to use the kys "DELETE" and "BACKSPACE" to destroy cell contents and formulae.

Does anyone know how to completely protect formual cells from changes and delete / backspace, while keeping the ability to view group rows columns?

Thanx,

Patrick Desbois

Posted by Ivan F Moala on August 23, 2001 1:38 AM

Patric
One way to do this is via VBA.
Using the sheets caculate event & a small
macro that will Allow/Disallow you to make
changes.

In the sheets code module
Get to this via Right clcik sheet Tab
select view code.....place this code in.

Option Explicit

Public ProtectionFlag As Boolean

Private Sub Worksheet_Calculate()
Static ProtectionFlag

If ProtectionFlag Then Exit Sub

On Error Resume Next
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End Sub


'This code will be activated when ever the calculate is activated eg. change in formula
or referenced formula cells etc.

The in a code module....
avail via
1) Alt F11
2) Ctrl R (Project explorer)
3) right click excel object
4) Select insert Module
Then paste this code in

Sub ChangeProtectMode()
Static ProtectionFlag

Dim PW As String
'Run this routine again to Protect the sheet 'IFM
If ProtectionFlag Then
ProtectionFlag = False
Range("IV1").FormulaR1C1 = "=COUNTA(R[1]C:R[65535]C,C[-255]:C[-1])"
Exit Sub
Else
PW = InputBox("Password......")
If PW <> "test" Then End
'Delete formula
Range("IV1") = ""
ProtectionFlag = True
End If
End Sub


Run the above code whenever you want to change
the sheet yourself. Run it again to protect it
from input / deletions.


'This formula counts the cells that are not empty
'within the sheet...so any time an entry is made
'the calculate event is triggered
'Range("IV1").FormulaR1C1 = "=COUNTA(R[1]C:R[65535]C,C[-255]:C[-1])"


HTH


Ivan