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?
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

pgc01

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,996
Messages
5,508,644
Members
408,689
Latest member
SamSan78

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top