Protecting Cells via Macro

[Dave Punky]

Hi all,

I'm not sure if it's possible to do this but I thought I would ask to see the best way of doing it if it was.

Basically I want to protect a block of cells from being edited when a checkbox is ticked. The information can be copied, but not physically edited unless the checkbox was clear.

Essentially this box would make these cells read only when checked, and fully writable when unchecked - I can't do this with the whole spreadsheet as data will continually be entered into it.

For example, the cells I want to protect are A1:G20, and if the box is unchecked they would be completly editable, but if the box is checked, you can only view what's there, copy the information that is there but would be unable to manually edit the information.

Is it possible to do this, or is it just madness?

 

[pgc01]

Hi

Unlock all the cells in the worksheet except A1:G20 and then protect the worksheet. All the cells will be editable except that range. To be able to change them unprotect the worksheet.
You can record a macro that protects, unprotects the worksheet and assign it to a checkbox.

P. S. You forgot to post your excel version.

 

[Dave Punky]

Sorry my bad, I'm using Excel 2007 but saving as a 2003 worksheet as the machine I'm using it on only has 2003.

Thanks for that though, I'll try that and if it's not suitable I'll come back.

 

[Misca]

Two macros:

The first one does the actual protecting. The second one unprotects the given range but leaves the rest of the sheet protected (supposing there's something left to protect).

Sub ProtectRange()

'Protects the given range.
'Comment / delete the Password-parts if you don't want to use passwords.
    
Dim Rng As Range
Dim WS As Worksheet

Set WS = ActiveSheet
Set Rng = WS.Range("A1:B10")

WS.Unprotect Password:="MyPassWord"
With Rng
    .Locked = True
    .FormulaHidden = True
End With

WS.Protect Password:="MyPassWord"
    
End Sub


Sub UnProtectRange()

'Unprotects the given range. Protects the rest of the sheet.

Dim Rng As Range
Dim WS As Worksheet

Set WS = ActiveSheet
Set Rng = WS.Range("A1:B10")

WS.Unprotect Password:="MyPassWord"

With Rng
    .Locked = False
    .FormulaHidden = False
End With

WS.Protect Password:="MyPassWord"

End Sub

If there's nothing you want to leave protected you can skip the second macro and replace it with just one line:

Sub UnprotectSheet()

ActiveSheet.Unprotect Password:="MyPassWord"

End Sub