Locking and unlocking cells based on value in another cell

[eios]

Afternoon all,

This is what I want to happen but I have no idea how to do it.

If C14 = "IV" THEN E14 = Locked AND F14 = Unlocked
If C14 = "RV" THEN E14 = Unlocked AND F14 = Locked
If C14 = "AJ" THEN E14 AND F14 = Unlocked

Another thing I would like to make possible is for this to work throughout cells C14:C450, E14:E450 and F14:F450

Can anyone help me out on this?

 

[AlphaFrog]

Okay, now I'm confused. Why do you want tho change the locked status of the cells in E and F when you don't know what/how that works? I'm not being mean. I just don't want to go through all this just to find out that you really don't want/need it. Lenze had a good point, if the sheet is not protected, then why change the locked status of cells?

 

[eios]

Ahh no worries.

The sheet that has been made up is a ledger card for keeping track of stock going in and out.

IV = Issue Voucher
RV = Reciept Voucher
AJ = Adjusment Voucer

The reason I want those cells locked is so when someone enters in IV they do no enter in the amount issued to someone in the reciept column. I pretty much just want to take out any room for error when it comes to accounting for the stock we hold.

 

[AlphaFrog]

Are you going to eventually Protect the sheet then? Having a cell locked or not makes no difference unless the sheet is protected.

To manually change the locked status of a cell...

• Select the cell
• Right-click on the selected cell
• Select "Format Cells" from the pop-menu
• Select the "Protection" tab on the dialog
• Check\Uncheck the "Locked" check box
• OK

 

[eios]

Thanks for your help guys! With the sheet protected the code works like magic!

 

[URHoya]

Questions....I'm trying to resolve a similar matter; however, the cells are on a different sheet....how would I right the code then? Please help. Thanks.

 

[URHoya]

By different sheet I mean IV, RV and RJ are on Sheet 1 of the Workbook, but the lock/unlock cells are on Sheet 2. Please advise. Thanks a million.

 

[Murman01]

Hello,
I've been able to get this piece of code to work for unlocking/locking one cell based upon the value of another cell in a spreadsheet, but I'm having challenges trying to unlock/lock multiple cells based upon the value of multiple cells in a single spreadsheet.

If C14 = "IV" THEN E14 = Locked AND F14 = Unlocked
If C14 = "RV" THEN E14 = Unlocked AND F14 = Locked
If C14 = "AJ" THEN E14 AND F14 = Unlocked
If D14 = "Open" THEN R14 = Unlocked AND S14 = Unlocked
If D14 = "Close" THEN R14 = Locked AND S14 = Locked

The worksheet I'm using is currently password protected.

Thanks in advance!

 

[AlphaFrog]

Show your code

 

[Murman01]

Apologies. Code below works.

In the same spreadsheet I now also need to check if the user enters "Closed" or "Transfer" in column R. If they do, then I need to lock cells in columns S, T and U of that same row.

Your assistance is greatly appreciated!




Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K9:K4000")) Is Nothing Then
Dim cell As Range
Unprotect Password:="M2Ney1"
For Each cell In Intersect(Target, Range("K9:K4000"))
Select Case cell.Value
Case "Closed"
cell.Offset(0, 1).Locked = False ' allows user to enter into Cell L of the same row and keeps cell unlocked
Case "Transferred"
cell.Offset(0, 1).Locked = False ' allows user to enter into Cell L of the same row keeps cell unlocked
Case Else
cell.Offset(0, 1).Locked = False
cell.Offset(0, 1).Formula = "" ' clears contents of Cell L of the same row and then locks that cell
cell.Offset(0, 1).Locked = True
End Select
Next cell
Protect Password:="M2Ney1"
End If
End Sub

 

[ray_b]

Hi All,

The information and help we find on this forum is amazing. Although Excel is a passion of mine, I am no expect when it comes to VBA and macro codes...

I also have the same problem. I was wondering if someone could help me with the code that would execute the following throughout the cell range F7:F25, I7:I25 and L7:L25.

If F7 = AND(F7>0, F7<1000) THEN I7 = Unlocked AND L7 = Locked
If F7 >= 1000 THEN I7 = Locked AND L7 = Unlocked
If F7 <= 0 THEN I7 AND L7 = Locked

Same for line 8 to 25.

Merry Christmas and best wishes for this holiday season to everyone.

Regards