• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1047
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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