CODEWITHSUNDEEP
excellockingrangevba
Kody Usie
Kody Usie

Reputation: 11

Lock and unlock with VBA

I have a part lookup file that returns the location from the part number. I also have this file create an upload file based off of the location as certain locations point to a computerized storage system and I am able to upload.

My issue is column A is the part number, B is a control that provides a "1" when the part is in the computerized location and is hidden. column C is open for the count of the part coming in. Since Column C is not necessary for parts outside of the computerized system I am trying to create a VBA code that will lock when Column B is blank and unlock when the value is "1"

The code i have been trying is :

Private Sub Worksheet_Change(ByVal Target As Range)

Dim aCell As Range

Application.EnableEvents = False

If Not Intersect(Range("B:B"), Target) Is Nothing Then
    ActiveSheet.Unprotect "MYPASSWORD" 
    For Each aCell In Range("B:B")
        If Len(Trim(aCell.Value)) = "" Then _
        aCell.Offset(, 1).Locked = True Else _
        aCell.Offset(, 1).Locked = False
    Next
    ActiveSheet.Protect "MYPASSWORD"
End If

End Sub

I would like to get it where when i hit enter the cursor goes to the count column C if the part is in the computerized system and just go to the next line if it is not.

Upvotes: 1

Views: 2463

Answers (1)

Chatty McChatface
Chatty McChatface

Reputation: 13

Instead of unlocking and locking, consider using the UserInterfaceOnly argument. This locks the user interface so that a user cannot interact with the sheet, but allows any macro code to run without having to unlock the worksheet.

The easiest way to do this is by running a loop in the Workbook_Open() event to lock each sheet:

Private Sub Workbook_Open()
    For Each ws In ThisWorkbook.Sheets
        ws.Protect Password:="your password here", UserInterfaceOnly:=True
    Next
End Sub

Upvotes: 1

Related Questions