• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1059
  • Last Modified:

Excel 2003 - lock or unlock a cell based on condition of another cell

I have a workshet taht is password protect. I want to be able to have a cell be disabled or enabled based on if antoher cell has a certain answer selected "yes" or No. I also want the cell contents to clear if No is selected.
0
Pdeters
Asked:
Pdeters
1 Solution
 
viralypatelCommented:
0
 
PdetersAuthor Commented:
I am looking for the cell to be enable or disbale?
0
 
PdetersAuthor Commented:
I want the cell to be either locked or unlocked
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
PdetersAuthor Commented:
I have tried this and it is not working? any suggestions

Private Sub Worksheet_Change(ByVal Target As Range)
If [C18] = "No" Then
    ActiveSheet.Unprotect ("password")
    [S3].Locked = True
    ActiveSheet.Protect ("password")
'Remove locked property if B3's value is anything else or is deleted.
Else
    ActiveSheet.Unprotect ("password")
    [C20].Locked = False
'Optional, reprotect sheet
    ActiveSheet.Protect ("password")
End If
End Sub
0
 
sstampfCommented:
What all cells will have the value "Yes/No" and which cells you want to protect?
0
 
Saurabh Singh TeotiaCommented:

Just insert this code in the sheet module of your workbook..so lets say if you can to do it for sheet1 then copy paste this code in the sheet1 of it and it will do what you are looking for...

Saurabh...
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("C18")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If UCase(Range("C18").Value) = "YES" Then
        ActiveSheet.Unprotect "Your password here"
        Range("C20").Locked = False
        ActiveSheet.Protect "Your password here"
        Range("C20").ClearContents
    ElseIf UCase(Range("c18").Value) = "NO" Then
        ActiveSheet.Unprotect "Your password here"
        Range("S3").Locked = True
        ActiveSheet.Protect "Your password here"
        Range("S3").ClearContents
    End If
Application.EnableEvents = True

End Sub

Open in new window

0
 
PdetersAuthor Commented:
I can not get this to work? Nothing happens
0
 
Saurabh Singh TeotiaCommented:

Change a value in C18 and this will automatically work..also have you inserted the code in the sheet module of that you want this to work..?
0
 
PdetersAuthor Commented:
I have done a right click on the sheet tab and inserted. I did have to change the cell references. Still not working.

G18 is where the Yes or  No goes and then G20 should either lock or unlock. Does this look correct?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("G18")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If UCase(Range("G18").Value) = "YES" Then
        ActiveSheet.Unprotect "pass"
        Range("G20").Locked = False
        ActiveSheet.Protect "pass"
        Range("G20").ClearContents
    ElseIf UCase(Range("G18").Value) = "NO" Then
        ActiveSheet.Unprotect "pass"
        Range("G20").Locked = True
        ActiveSheet.Protect "pass"
        Range("G20").ClearContents
    End If
Application.EnableEvents = True

End Sub
0
 
Saurabh Singh TeotiaCommented:

Yes your code looks correct, However i guess your enable events have turn off by now..so turn it on..run this code before and then change value and lets see if it works or not...



Sub abc()
Application.EnableEvents = True
End Sub

Open in new window

0
 
PdetersAuthor Commented:
Nope.
0
 
Saurabh Singh TeotiaCommented:

Can you post your excel sheet so that i can have a look over it ..plz
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now