CODEWITHSUNDEEP
excelvba
Calvin Bonner
Calvin Bonner

Reputation: 580

Lock a Cell Within a Range After Content Has Been Entered in Excel Through VBA

I have a table that is acting as an inventory entry point for a hand scanner. When a code is scanned the scanner automatically hits return and selects the next cell down. I want the cells that have content entered into them to automatically be protected or lock while leaving the empty cells available for editing.

Unfortunately my current VBA code is locking the entire range whenever I enter anything anywhere within that range. Any ideas on what I could do to fix this? I have never worked in VBA before so I am very lost when it comes to this stuff. This code is basically through trial and error and Googling things.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim MyRange As Range
    Set MyRange = Range("A:A")

    If Not MyRange Is Nothing Then
        Sheets("Data In").Unprotect Password:="mypassword"
        MyRange.Locked = True
        Sheets("Data In").Protect Password:="mypassword"
    End If

End Sub

Again, very new to VBA so if I am missing something super obvious please let me know.

Thanks!!

Upvotes: 0

Views: 482

Answers (1)

Marcucciboy2
Marcucciboy2

Reputation: 3257

I believe that your issue is actually that you're not unlocking the rest of the empty range. This will only lock the cells in column A which already have values.

Like BigBen, I would also suggest using Worksheet_Change instead of Worksheet_SelectionChange

Private Sub Worksheet_Change(ByVal Target As Range)

    With Me
        ''unprotect for VBA only
        .Protect Password:="mypassword", UserInterfaceOnly:=True

        ''lock entire column
        .Range("A:A").Locked = True

        ''unlock empty cells in the usedrange
        On Error Resume Next  'xlCellTypeBlanks will error out when there are no blank cells in the UsedRange
        .Range("A:A").SpecialCells(xlCellTypeBlanks).Locked = False

        ''unlock empty cells after the usedrange
        .Range(.Range("A" & UsedRange.Rows.Count + 1), .Range("A" & UsedRange.Rows.Count + 1).End(xlDown)).Locked = False
    End With

End Sub

Upvotes: 1

Related Questions