How to un-protect multiple cells in Excel worksheet with VBA

BizBoy

Board Regular
Joined
Jul 26, 2012
Messages
118
Hi,

How to un-protect multiple cells in Excel worksheet with coding.
I have a worksheet.
I need to protect entire worksheet apart from few cells.

I tried various solutions from Google but somehow it is not working for me.
For example, I want to keep cell A1, B5, C10, D15, E20 unprotected.
And rest of the worksheet should be protected.

How do I do this
Can anyone please help me in this.

I tried various options for below code but not successful.

Code:
Sub Protect_My_Range()
    Dim TempBook As Workbook
    Dim TempSht As Worksheet
    
    Dim TempRng As Range
    Dim TRange As Range
    
    Set TempBook = ActiveWorkbook
    Set TempSht = TempBook.ActiveSheet
    
    TempSht.Range("F7").Locked = False
    TempSht.Range("N7").Locked = False
    
TempSht.Protect "temp"
End Sub
 
Last edited:

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
How about
Code:
Sub BizBoy()
   With ActiveSheet
      .Cells.Locked = True
      .Range("A1,B5,C10,D15,E20").Locked = False
      .Protect "dc"
      .EnableSelection = xlUnlockedCells
   End With
End Sub
 
Upvote 0
Hi Fluff sir,
Thanks for the help.
Code run fine at first instance.
However am facing bug from second instance onwards.
Could you please help me if you get time.

I am getting bug as 'Unable to set the Locked property of the range class'.

Code:
Sub Protect_My_Range()
    Dim TempBook As Workbook
    Dim TempSht As Worksheet
    
    Dim TempRng As Range
    Dim TRange As Range
    
    Set TempBook = ActiveWorkbook
    Set TempSht = TempBook.ActiveSheet
    
     With TempSht
      .Cells.Locked = True
      .Range("F7,N7,F9,M9,F11,I11,M11,G13,B17,B18,F22,L22,F24,G25,G26,G28,G29,E32,M33,E35,M36").Locked = False
      .Protect "temp"
      .EnableSelection = xlUnlockedCells
   End With
End Sub
 
Upvote 0
Not sure why you would want to run that twice, but to do so you need to unprotect the sheet first.
 
Upvote 0
Hi Fluff sir,
Sorry for making it confusing.
Code run fine without any issue at first.
But post the first run, it is throwing bug.

Please see below code for your reference.
Could you please help if you get time.

Code:
Sub Save()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
        
    
    'Some data processing is done before this line.
    'Have removed that code from here.
    
    TemplateSht.Range("A1").Select
    TemplateSht.Range("A1:S37").Copy
    
    'All the cells in TemplateSht worksheet are unprotected.
    Workbooks.Add
    Dim TempBook As Workbook
    Dim TempSht As Worksheet
    
    On Error Resume Next
        TempBook.Worksheets("Sheet2").Delete
        TempBook.Worksheets("Sheet3").Delete
    On Error GoTo 0
    
    Set TempBook = ActiveWorkbook
    Set TempSht = TempBook.ActiveSheet
    
    'I need to call below module from here
    Call Protect_My_Range
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
Sub Protect_My_Range()
    Dim TempBook As Workbook
    Dim TempSht As Worksheet
    
    Dim TempRng As Range
    Dim TRange As Range
    
    Set TempBook = ActiveWorkbook
    Set TempSht = TempBook.ActiveSheet
    
    With TempSht
        .Cells.Locked = True
        .Range("F7,N7,F9,M9,F11,I11,M11,G13,B17,B18,F22,L22,F24,G25,G26,G28,G29,E32,M33,E35,M36").Locked = False
        .Protect "temp"
        .EnableSelection = xlUnlockedCells
    End With
End Sub
 
Upvote 0
As I said, you need to unprotect the sheet.
Also in order to work on the activesheet you don't need all those variables, you can do it like I showed in post#3
 
Upvote 0
Hi Fluff sir, thanks for the help.
There are few merged cells in my worksheet.

I guess these are causing issues.
Is there a way to protect merged cells too.
 
Last edited:
Upvote 0
I've no idea. I never use merged cells as they are an abomination & should be avoided like the plague.
I would recommend getting rid of them. If they are merged across columns rather than rows look at using "Center across selection" instead.
 
Upvote 0
Hi Fluff sir, thanks for the help.
I too avoid merged cells.
However this user's template and I am not allowed to make changes in this.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top