Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value of one cell unlocks another
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value of one cell unlocks another
You can use data validation.
In Cell D5 Data - Validation - Custom =D3="Yes" -- HTH... Jim Thomlinson "Tel" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value of one cell unlocks another
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" wrote: You can use data validation. In Cell D5 Data - Validation - Custom =D3="Yes" -- HTH... Jim Thomlinson "Tel" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value of one cell unlocks another
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). -- HTH... Jim Thomlinson "Tel" wrote: 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" wrote: You can use data validation. In Cell D5 Data - Validation - Custom =D3="Yes" -- HTH... Jim Thomlinson "Tel" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value of one cell unlocks another
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" wrote: 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). -- HTH... Jim Thomlinson "Tel" wrote: 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" wrote: You can use data validation. In Cell D5 Data - Validation - Custom =D3="Yes" -- HTH... Jim Thomlinson "Tel" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value of one cell unlocks another
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 -- HTH... Jim Thomlinson "Tel" wrote: 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" wrote: 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). -- HTH... Jim Thomlinson "Tel" wrote: 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" wrote: You can use data validation. In Cell D5 Data - Validation - Custom =D3="Yes" -- HTH... Jim Thomlinson "Tel" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value of one cell unlocks another
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 On Wed, 17 Jun 2009 05:43:02 -0700, Tel wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value of one cell unlocks another
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 "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Tel" wrote: 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" wrote: 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). -- HTH... Jim Thomlinson "Tel" wrote: 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" wrote: You can use data validation. In Cell D5 Data - Validation - Custom =D3="Yes" -- HTH... Jim Thomlinson "Tel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return cell address of a cell based on contents of cell. | Excel Worksheet Functions | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
How can I copy a value from a cell and paste it into another cell while adding it to the previous value in that cell | Excel Worksheet Functions | |||
How can I make a blank cell in a formula cell with a range of cell | Excel Discussion (Misc queries) | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) |