PDA

View Full Version : Protect Cell and Range



fatalcore
02-19-2012, 11:29 AM
Hi,
I have a main sheet called "Master Call". Where I insert the sheet name and cell number or the cell range.
I want a code that when called will lock the particular cells/region of those sheet mapped in the "Master Call" with a password and make it un editable.Please note that there are some multiple merged areas in the report sheets.
Thanks in advance.

mdmackillop
02-19-2012, 01:38 PM
Try this.
The colouring is for test purpose only, and can be deleted.

Option Explicit

Sub Macro1()
Dim ws As Worksheet
Dim Rng As Range
Dim cel As Range, c As Range
Dim i As Long

With Sheets("Master Call")
Set Rng = Range(.Cells(2, 1), .Cells(Rows.Count, 2).End(xlUp))
End With
For i = 1 To Sheets.Count
With Sheets(i)
.Unprotect "test"
.Cells.Locked = False
.Cells.Interior.ColorIndex = xlNone
End With
Next
For Each cel In Rng.Columns(1).Cells
If cel <> "" Then
Set ws = Sheets(cel.Value)
ws.Activate
End If
For Each c In ws.Range(cel.Offset(, 1).Value)
c.MergeArea.Locked = True
c.MergeArea.Interior.ColorIndex = 6
Next
Next
For i = 1 To Sheets.Count
Sheets(i).Protect "test"
Next

End Sub

fatalcore
02-19-2012, 06:29 PM
mdmackillop,That's amazing,Godspeed!!!
Thanks a ton.

fatalcore
02-19-2012, 10:55 PM
Hi mdmackillop,
Just a small help, Can you please update the code a little bit, So that,the code doesn't not lock all the sheets all at a time.
Actually I have a button on every page, when I click it it just simply locks the cell of that sheet only.
Thanks in advance.