Protecting Cells via Macro

Dave Punky

Board Regular
Joined
Jan 7, 2010
Messages
133
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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Board Regular
Joined
Jan 7, 2010
Messages
133
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

Well-known Member
Joined
Aug 12, 2009
Messages
1,697
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).

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

Code:
Sub UnprotectSheet()

ActiveSheet.Unprotect Password:="MyPassWord"

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,132,819
Messages
5,655,468
Members
418,204
Latest member
ElizabethCorrin

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