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]

You could use an event macro like the one below. This will change the locked status of the cells in columns E and F from the same row as the changed cell in C14:C450

To install the macro...

• Right-click on the sheet tab you want this to work on
• Select View Code from the pop-up menu
• Paste the code th the VBA edit window

If your worksheet is protected, the macro needs to Unprotect it to make the changes to the locked status of the cells. It then re-Protects the sheet. You need to edit the protect sheet password ("Secret") in the code to your own password.

If your sheet is not protected, you need to remove or comment out the two Unprotect\Protect lines

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("C14:C450")) Is Nothing Then
        Dim cell As Range
        [COLOR="Blue"]Unprotect Password:="Secret"[/COLOR]
        For Each cell In Intersect(Target, Range("C14:C450"))
            Select Case cell.Value
                Case "IV"
                    cell.Offset(0, 2).Locked = True     'Column E
                    cell.Offset(0, 3).Locked = False    'Column F
                Case "RV"
                    cell.Offset(0, 2).Locked = False    'Column E
                    cell.Offset(0, 3).Locked = True     'Column F
                Case "AJ"
                    cell.Offset(0, 2).Locked = False    'Column E
                    cell.Offset(0, 3).Locked = False    'Column F
                Case Else
                    cell.Offset(0, 2).Locked = True     'Column E
                    cell.Offset(0, 3).Locked = True     'Column F
            End Select
        Next cell
        [COLOR="Blue"]Protect Password:="Secret"[/COLOR]
    End If
    
End Sub

 

[eios]

Cheers for the code.

I'm getting an error however when I set C14 to either IV, RV or AJ

Run-time error '1004':

Unable to set the Locked property of the Range class

When I go into the debugger mode it has this line highlighted

cell.Offset(0, 2).Locked = True 'Column E

 

[AlphaFrog]

Is the sheet getting unprotected when the code runs?

 

[eios]

I've removed the Unprotect and Protect part as the sheet it not going to be protected. With the code placed back in, it does try to Unprotect it however fails due to there being no password.

Is that what you ment?

 

[lenze]

SO!!! What good does it do to Lock and Unlock cells if the sheet is NOT protected!! Locking has NO affect on Unprotected sheets??
lenze

 

[AlphaFrog]

Yes. If your sheet is not protected, remove the two lines. Also, before you make changes in C14:C450, double-check from the menu and make sure that the sheet is not protected.

 

[eios]

It's doesn't seem to be a problem with the sheet being protected or not.

It seems that the problem is within the range class, being;

cell.Offset(0, 2).Locked = True 'Column E
cell.Offset(0, 3).Locked = False 'Column F

I've tried a few different variences but none of them seem to work. Things I tried where;

cell.Offset(2, E).Locked = True
cell.Offset(3, F).Locked = False

cell.Offset(0, 2).Locked = True 'Column E'
cell.Offset(0, 3).Locked = False 'Column F'

And a few others but none work.

 

[AlphaFrog]

As a test, can you manually change the Locked property for cells E14 and F14 ?

 

[eios]

As a test, can you manually change the Locked property for cells E14 and F14 ?

How would I go about doing that?