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

[BizBoy]

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.

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

 

[GirishDhruva]

Hi @BizBoy, it seems your code runs perfectly.

If not please share what error you are facing by debugging the code

Regards
Dhruva

 

[Fluff]

How about

Sub BizBoy()
   With ActiveSheet
      .Cells.Locked = True
      .Range("A1,B5,C10,D15,E20").Locked = False
      .Protect "dc"
      .EnableSelection = xlUnlockedCells
   End With
End Sub

 

[BizBoy]

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'.

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

 

[Fluff]

Not sure why you would want to run that twice, but to do so you need to unprotect the sheet first.

 

[BizBoy]

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.

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

 

[Fluff]

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

 

[BizBoy]

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.

 

[Fluff]

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.

 

[BizBoy]

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.