Lock/Unlock a specific column RRS feed

  • Question

  • I want a macro which will lock all the sheet  present in the active workbook except Column E(E2:EC200) of Sheet3.


    Saturday, May 23, 2015 6:07 PM


  • The following macro loops through each worksheet within the active workbook, unprotects the worksheet (in case it's protected), locks all cells (in case they are not already locked), and then protects the worksheet.  For Sheet3, it unlocks E2:E200 before protecting the sheet.  Note, if you use or would like to use a password to protect your worksheets, assign a password to the constant "sPASSWORD".  Also, other parameters for Protect/Unprotect can be used as well.  See the help menu for additional information.

    Option Explicit
    Sub test()
        Dim Wks As Worksheet
        Const sPASSWORD As String = "" 'add password, if required
        For Each Wks In ActiveWorkbook.Worksheets
            With Wks
                .Unprotect Password:=sPASSWORD
                .Cells.Locked = True
                If .Name = "Sheet3" Then
                    .Range("E2:E200").Locked = False
                End If
                .Protect Password:=sPASSWORD
            End With
        Next Wks
    End Sub
    Hope this helps!

    Domenic Tamburino Microsoft MVP - Excel - "For Your Microsoft Excel Solutions"

    Saturday, May 23, 2015 11:16 PM