Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

  1. #1
    Board Regular
    Join Date
    Jul 2012
    Location
    India
    Posts
    91
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by BizBoy; Sep 19th, 2019 at 07:44 AM.

  2. #2
    Board Regular
    Join Date
    Mar 2019
    Location
    India
    Posts
    196
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Hi @BizBoy, it seems your code runs perfectly.

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

    Regards
    Dhruva

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    India
    Posts
    91
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    Not sure why you would want to run that twice, but to do so you need to unprotect the sheet first.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Jul 2012
    Location
    India
    Posts
    91
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Jul 2012
    Location
    India
    Posts
    91
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by BizBoy; Sep 19th, 2019 at 10:23 AM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Jul 2012
    Location
    India
    Posts
    91
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •