Results 1 to 5 of 5

Thread: Lock Cells After Data Entered

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10

    Lightbulb Lock Cells After Data Entered

    Hi All,

    Here is VBA method to lock the cell after entering data in the cell.

    Code:
    Dim blnUnlockedAllCells As Boolean
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        Const RangeToLock As String = "A2:D1000" '<<  adjust to suit
        
        If Target.Cells.Count > 1 Then Exit Sub
        
        If Not blnUnlockedAllCells Then
            Me.Cells.Locked = False
            On Error Resume Next
            Me.Range(CStr(RangeToLock)).SpecialCells(2).Locked = True
            On Error GoTo 0
            blnUnlockedAllCells = True
            Me.Protect Password:="pwd", userinterfaceonly:=True
        End If
        
        If Not Application.Intersect(Target, Me.Range(CStr(RangeToLock))) Is Nothing Then
            If Len(Target) Then Target.Locked = True
        End If
        
    End Sub
    Note: adjust the RangeToLock to suit. This code goes in the sheet module of the sheet in question.

    I hope this helps !
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Junior Member
    Join Date
    Feb 2013
    Posts
    1
    Rep Power
    0
    The above code will fail as you don't unprotect the worksheet anywhere!


    Regards,
    Simon Lloyd
    Office Application Help

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Would fail on a worksheet, which is protected before applying the code. Once the code is applied, you don't need to unprotect the sheet since you are using userinteface=TRUE. And yes, I should have been mentioned that in my post
    Last edited by Admin; 02-07-2013 at 05:39 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Junior Member
    Join Date
    Jun 2013
    Posts
    1
    Rep Power
    0

    Protected worksheet

    I only want to lock the cells that I enter data into. I have a protected worksheet and I do not want to unlock all cells. How do I modify the code to accomplish this?

    Thanks
    Mike

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    If you do not have a specific range to lock, put A1:XFD1048576 in the variable RangeToLock.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Select Group Of Cells Using Data Validation
    By rich_cirillo in forum Excel Help
    Replies: 7
    Last Post: 06-09-2013, 05:55 PM
  2. How To Lock Or Protect Cell Using Data Validation
    By mahmoud-lee in forum Excel Help
    Replies: 4
    Last Post: 06-06-2013, 01:57 AM
  3. Replies: 4
    Last Post: 05-05-2013, 04:01 AM
  4. don't copy filtered data if no active cells
    By xander1981 in forum Excel Help
    Replies: 29
    Last Post: 11-01-2012, 06:47 PM
  5. Lock cells on the basis of date VBA
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 22
    Last Post: 09-27-2011, 03:56 PM

Tags for this Thread

Posting Permissions

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