Value of one cell unlocks another

[Tel]

Hi Guys,

I am creating a spreadsheet which is, essentially, a questionnaire.

I have a cell (D3) with a pick list in it (Yes/No and a blank to avoid false
entries). If the user selects "Yes" in this cell I need this to trigger
unlocking another cell (D5).

I need to be able to repeat this several times over throughout the
spreadsheet and the workbook (i.e. "Yes" in cell D7 will unlock D9 and the
same for D11 and D13 and so on).

Any help you can offer would be greatly appreciated.

I have alot more functionality I wish to add but I'll come back to that
later.

Thanks All

Tel

 

[Jim Thomlinson]

You can use data validation.

In Cell D5
Data -> Validation -> Custom
=D3="Yes"

 

[Tel]

Hi Jim,

Many thanks for your prompt reply. However I already have Data Validation
in there (=$G$4:$G$6) where would your formula be entered in this string.

Also, how does this unlock the cell?

Sorry for being a complete "numpty" but I'm not sure I understand it?

Thanks

Terry

 

[Jim Thomlinson]

Data validation is used to restrict what can be entered into a cell. My
validatoin says that unless D3 is 'Yes' then no entry in D5 is valid. I am
not sure exactly what your validation is doing.

If I were doing this I would use a Condtional Format on the cell to indicate
if the cell is open or not and data validation to restrict entry (or you
could use a macro but that is a bit more work).

 

[Tel]

Hi Again Jim,

My Data Validation is pointing to the source Data for the pick list (i.e.
Blank, Yes or No).

It is my intention to lock all cells except those where the user is intended
to insert data. However, unless the user inserts "Yes" in D3 then there is
no need to insert anything in D5 (which, by the way is blanked out using CF).

I am trying to achieve a situation whereby if the user enters "No" in cell
D3 and then hits "Tab" (as they'll be instructed) it will jump straight to
cell D7. But, if the user enters "Yes" in cell D3 this will not only reveal
the contents of C5 (the question) but will also unlock (unprotect?) cell D5
but also reveal (using the same CF conditions as used in C5) the pick list
which is sourced as before.

In summary, "Yes" in D3 unlocks D5 and the user "Tabs" to it, anything other
than "Yes" keeps cell D5 locked and the user "Tabs" to to cell D7.

I hope you like a challenge ;-)

Terry

 

[Jim Thomlinson]

It is not a matter of being a challenge, but based on your requirements you
are looking at a macro the likes of which you will not necessarily be
comfortable with... that being said here it is. Right click the sheet tab and
select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$D$3"
Call LockUnLockCell(Target, Me.Range("D5"))
Case "$D$11"
Call LockUnLockCell(Target, Me.Range("D13"))
'Add more cases as necessary
End Select
End Sub

Private Sub LockUnLockCell(ByVal Target As Range, ByVal rngLockUnlock As
Range)
Me.Unprotect
With rngLockUnlock
If LCase(Target.Value) = "yes" Then
.Locked = False
.Select
Else
.Locked = True
End If
End With
Me.Protect
End Sub

 

[Gord Dibben]

Assuming WS_RANGE cells are unlocked and all other cells are locked prior to
protecting the sheet.

If "Yes" is chosen in D3 then D5 is unlocked and selected. Edit to suit.

If not "Yes" then D3 remains selected............edit to suit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Const WS_RANGE As String = "D3,D7,D11,D15,D19,D23,D27"
On Error GoTo enditall
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Unprotect Password:="justme"
With Target
If .Value <> "Yes" Then
.Select 'remove if not wanted
Else
With .Offset(2, 0)
.Locked = False
.Select 'remove if not wanted
End With
End If
End With
End If

enditall:
Application.EnableEvents = True

Me.Protect Password:="justme"

End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the above into that module. Alt + q to return to the Excel
window.



Gord Dibben MS Excel MVP

 

[Tel]

Jim,

Thank you so much for your patience and understanding. You're an absolute
Grade A STAR it works a dream (although for future basic users like myself
they should be aware that you need to bring the "Range)" word on the second
Macro Sub onto the same line.

I shall be making much use of this macro in the future.

Terry