cell lock

[pradyuthal]

how do lock/protect particular cell(s) containing formulae so that none can delete it by mistake .

Please help me.

 

[Domski]

If you use the Tools...Protection option all cells on a worksheet are then locked by default.

To unlock cells that you still want people to still be able to edit select the cell or range of cells and then use the Format cells...Protection option and untick the Locked box.

 

[pradyuthal]

I have solved the problem with the help of the following vb code .
It finds all the cells containing formulae only and protect it with a predefined password .

I would like to unprotect these cells in the same way .
please help me .


Sub Protect_formulasOnly()
Dim Frm 'Formulas
Dim oConst 'text,constants ie work data
Dim All
Dim oCell

Set Frm = Range("A1").SpecialCells(xlCellTypeFormulas, 23)
Set oConst = Range("A1").SpecialCells(xlCellTypeConstants, 23)
Set All = Application.Union(Frm, oConst)

For Each oCell In All.Cells
If oCell.HasFormula Then
oCell.Locked = True
Else
oCell.Locked = False
End If
Next
ActiveSheet.Protect password:="test", userinterfaceonly:=True
End Sub

 

[Domski]

Hi,

This should protect formula only:

Sub Lock_Formula()

Dim myRange As Range

ActiveSheet.Unprotect Password:="Test"

For Each myRange In ActiveSheet.UsedRange

    If myRange.HasFormula Then

        myRange.Locked = True

    Else

        myRange.Locked = False

    End If

Next myRange

ActiveSheet.Protect Password:="Test"

End Sub

And this will reverse the process:

Sub Unlock_Formula()

Dim myRange As Range

ActiveSheet.Unprotect Password:="Test"

For Each myRange In ActiveSheet.UsedRange

    If myRange.HasFormula Then
    
        myRange.Locked = False

    End If

Next myRange

ActiveSheet.Protect Password:="Test"

End Sub

Is that what you're after?