Excel-VBA : Range – Lock/Unlock Specific Cells

There are two different cases you might deal with,

  • Lock only few specific cells and rest of the cells in the work sheet are not locked(means you can edit them)
  • Lock the Enitre worksheet except few cells

Lock only few specific cells and rest of the cells in the work sheet are not locked

Steps:

  • Lock the selected cells
  • Provide the password

Note: If you wont provide the password then your cells will not be locked.

Code :

Sub sumit() 

  Dim mainworkBook As Workbook

  Set mainworkBook = ActiveWorkbook

  mainworkBook.Sheets("Main").Range("A1:C5").Value = "Locked"

   mainworkBook.Sheets("Main").Range("A1:C5").Locked = True

   ActiveSheet.Protect Password:="xx"
 

End Sub
Cell Lock
Cell Lock

Try editing any cell which is locked.

Cell Lock 2
Cell Lock 2

Lock the Enitre worksheet except few cells

  • Firsr choose the cells which you dont want to lock
  • Then lock the entire worksheet and provide the password.

Code:

Sub sumit()

  Dim mainworkBook As Workbook

  Set mainworkBook = ActiveWorkbook

  mainworkBook.Sheets("Main").Range("A1:C5").Value = "Free"

  mainworkBook.Sheets("Main").Range("A1:C5").Locked = False

   mainworkBook.Sheets("Main").Protect passowrd = "xx"

End Sub

 

unlock specific cells -1
unlock specific cells -1

Change the cells which are free, and try changing rest of the cells

unlock specific cells -2
unlock specific cells -2

 

4 thoughts on “Excel-VBA : Range – Lock/Unlock Specific Cells”

    • Actually I tried your code, but its protecting the whole sheet, I want to lock only particular cells in the sheet, and leave the other cells unlocked.

      Private Sub sumit()

      Dim mainworkBook As Workbook

      Set mainworkBook = ActiveWorkbook

      mainworkBook.Sheets("Sheet1").Range("A1:A20").Locked = True

      ActiveSheet.Protect Password:="abc"

      End Sub

      This the code. Please it would be helpful if you clear my doubts. Thank you in advance.

      Reply
  1. can someone tell me wht is wrong with this procedure. I want certain cells locked if user is not 191

    Function GetUserName() As String
    GetUserName = Environ$(“username”)
    ‘msgbox GetUserName, variable
    End Function
    Sub prtect()
    Dim Audito As range
    range(“a1”, “a1000″).Select
    If Selection.Locked Then
    Selection.Locked = False
    userinterfaceonly = True
    ActiveSheet.Protect Password:=”love”
    End If
    Run GetUserName
    If GetUserName <> “191” Then
    range(“a1”, “a1000″).Select
    Selection.Locked = True
    userinterfaceonly = True
    ActiveSheet.Protect Password:=”love”
    End If
    End Sub

    Reply

Leave a Comment