Locking and unlocking cells based on value in another cell

E

eios

New Member
Joined
Mar 23, 2010
Messages
9
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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
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

Code: 
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Unlock row based on location of active cell?
Replies
5
Views
215
EssKayKay
E
E
  • Question
Unlock Specific Cell if another cell in row has specific value
Replies
1
Views
211
EAB1993
E
D
  • Solved
Lock cells based on calculated value from another cell
Replies
4
Views
450
Dedeke
D
M
  • Question
Formula to populate the next available cell in a column using data populated in another cell.
Replies
4
Views
338
Marshkevuk
M
V
  • Question
If one cell for each row from range A1:D50 is checked or has value>0, lock other cells in the row
Replies
3
Views
433
Joe4
Joe4

Forum statistics

Threads
1,214,978
Messages
6,122,547
Members
449,089
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top