PDA

View Full Version : Solved: Unprotecting Cells



chem101
01-06-2011, 12:44 PM
Hello Everyone,

I have a question about a data entry worksheet. A user will select an item from a drop down window at cell e38. An 'If' formula in cell g38 will display a statement based on the selection made by the user at cell e38. Once the selection is made from the drop down window and the message is displayed the user then enters a value in cell L38. The user enters the data saves and clears the inputs and then starts again. I'm using L38 to collect data from more than one of the items in the drop down window. This is working fine so far. The problem I'm having is with protecting worksheet. I would like cell L38 to protected and unaccessible to the user unless certain items are selected from the drop down window (ex: Product A or Product B. I've locked cell L38 so when the worksheet is protected can't access it. This works fine. The code I'm using to try and unlock the cell when Product A or B is selected is working. This is what I have so far:


If wksUserInput.Range("InputStyle") <> "Product A" Then
wksUserInput.Unprotect Password:="pencil"
wksUserInput.Range("l38").Locked = True
wksUserInput.Protect _
Password:="pencil", _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True
End If
If wksUserInput.Range("InputStyle") = "Product A" Then
wksUserInput.Unprotect Password:="pencil"
wksUserInput.Range("l38").Locked = False
wksUserInput.Protect _
Password:="pencil", _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True
End If

I also have the same code for Product B. The code for both Product A and B work fine independently. That is if I want to use cell L38 to collect data from only one of these items. Can an 'If, Then' statement be constructed to unprotect cell L38 if either Product A or B is selected from the drop down window? I tried using and If Or statement like:
If wksUserInput.Range("InputStyle") = "Product A" or "Product B" Then....
But this didn't work.
Any help you can provide will be greatly appreciated.

Thank you.

Bob Phillips
01-06-2011, 12:58 PM
With wksUserInput

.Unprotect Password:="pencil"

If .Range("InputStyle") = "Product A" Or .Range("InputStyle") = "Product B" Then

.Range("l38").Locked = False

Else
.Range("l38").Locked = Truendif

.Protect Password:="pencil", _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True
End If
End With

chem101
01-06-2011, 01:06 PM
xld,
Your the best!

Thanks again for your help!

Bob Phillips
01-06-2011, 04:20 PM
Logic a bit wrong



With wksUserInput

.Unprotect Password:="pencil"

If .Range("InputStyle") = "Product A" Or .Range("InputStyle") = "Product B" Then

.Range("l38").Locked = False

Else

.Range("l38").Locked = True
End If

.Protect Password:="pencil", _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True
End With