cell lock

pradyuthal

Board Regular
Joined
Jul 13, 2005
Messages
212
how do lock/protect particular cell(s) containing formulae so that none can delete it by mistake .

Please help me.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

Board Regular
Joined
Jul 13, 2005
Messages
212
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

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

This should protect formula only:

Code:
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:

Code:
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?
 

Forum statistics

Threads
1,136,346
Messages
5,675,228
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top