4

Is it possible to lock a particular range of cells based on the input from a dropdown in a row of data?

For example, each row of my spreadsheet represents a patient, and the first cell asks a question, to which a "Yes" or "No" response is required (which is selected/entered via a dropdown).

EDIT

The "Yes/No" cell is, in fact, a merge of two cells (G13 & H13). I have updated my example to reflect this.

EDIT ENDS

If the user selects "No", then I wish to lock the remainder of the range of questions (G13-H13:AB13) as there is no need to enter data here. However, if the user selects, "Yes", then the remainder of the cells shall remain available to enter data into.

All cells within each range have data entered via dropdowns only.

Here is what I am hoping to achieve:

If "No" Then lock range G13-H13:AB13 Else If "Yes" Then do nothing i.e. G13-H13 I13-J13 K13-L13 .... .... AB13 | NO | ---- | ---- | ---- | ---- | ---- | (Locked Cells) OR G13-H13 I13-J13 K13-L13 .... .... AB13 | YES | | | | | | (Unlocked Cells)

Once again, I would like to emphasize that all data is entered via dropdown menus and that nothing is to be typed in manually; I would like it so that if G13-H13 = "No", then the remainder of the cells within the range which have dropdowns are blocked or locked from having further information selected from their respective dropdowns.

Please note that the value in G13-H13 can be either "Yes" or "No".

Can this be achieved using VBA and if so, how?

Many thanks.

Improve this question

3 Answers 3

Reset to default
5

EDIT: You can do this without VBA. I based this on another answer here: https://stackoverflow.com/a/11954076/138938

Column G has the Yes or No drop-down.

In cell H13, set up data validation like this:

  1. Data --> Data Validation
  2. Select List in the Allow drop-down.
  3. Enter this formula in the Source field: =IF($G13="Yes", MyList, FALSE)
  4. Copy cell H13 and paste the validation (paste --> pastespecial --> validation) to cells I13:AB13.
  5. Replace MyList in the formula with the list you want to allow the user to select from for each column. Note that you'll need to have the patient answer set to "Yes" in order to set up the validation. Once you set it up, you can delete it or set it to No.
  6. Once you have the validation set up for row 13, copy and paste the validation to all rows that need it.

If you want to use VBA, use the following, and use the worksheet_change event or some other mechanism to trigger the LockOrUnlockPatientCells. I don't like using worksheet_change, but it may make sense in this case.

In order for the cells to be locked, you need to lock them and then protect the sheet. The code below does that. You need to pass it the row for the patient that is being worked on.

Sub LockOrUnlockPatientCells(PatientRow As Long) Dim ws As Worksheet Dim YesOrNo As String Set ws = ActiveSheet YesOrNo = ws.Range("g" & PatientRow).Value ' unprotect the sheet so that we can modify locked settings ws.Unprotect ws.Range("a:g").Cells.Locked = False ' lock row Range("h" & PatientRow & ":AB" & PatientRow).Cells.Locked = True ' unlock the row depending on answer If YesOrNo = "Yes" Then Range("h" & PatientRow & ":AB" & PatientRow).Cells.Locked = False End If ' protect the sheet again to activate the locked cells ws.Protect End Sub

You can test the functionality by using the following, manually adjusting the values for the patient row. Once you get it to work the way you want, get the row from the user's input.

Sub testLockedCells() Dim AnswerRow As Long AnswerRow = 9 LockOrUnlockPatientCells AnswerRow End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thank you so much for this extremely detailed answer; unfortunately, my example shows both possbile states of the row - the value of G13 can be either "Yes" or "No" and, depending on what the value is, the rest of the row will either be available to edit ("Yes") or will be locked ("No").
Ah, I see. I edited the answer to work with a single row instead of two. It also uses column G for the Yes/No instead of column A.
Many thanks. I have tried the new code, but unfortunately I am still able to select options via the dropdown menus in the following cells in the row under scrutiny. What I am hoping to be able to achieve is to disable/disallow the user from selecting any dropdown menu options from any of the cells in the row if "No" is the first criteria selected.
I edited the answer to provide a non-VBA alternative that I really like. Let me know if that works for you.
Glad that worked. I learned a cool trick while answering this!
|
3

This code should get you started. You may need to tweak it to suit your specific needs, but I based my logic on the details in your original post.

Place the module in the respective Worksheet Object in the VBE.

Private Sub Worksheet_Change(ByVal Target As Range) 'assumes cell changed in column G and first row of data entry is 13 If Target.Column = 7 And Target.Row > 12 Then 'change to If Intersect(Target, Range("G13")) Then if all you care about is G13 Application.EnableEvents = False 'stop events from processing Dim blnLock As Boolean Dim r As Integer Select Case Target.Value Case Is = "No" blnLock = True r = 191 Case Is = "Yes" blnLock = False r = 255 End Select Unprotect Password:="myPassword" With Range("H" & Target.Row & ":AB" & Target.Row) 'just a suggestion, fill the cells grey before locking them, or turn them back to no fill if it's unlocked .Interior.Color = RGB(r, r, r) .Locked = blnLock End With .Protect Password:="myPassword" Application.EnableEvents = True End If End Sub
Improve this answer

2 Comments

+ 1 Good One. three suggestions 1) Use Intersect to check if there is a change in value in Cell G13 2) Set EnableEvents to false (with error handling) and set them back on in the end. This is required so that you don't get into a possible endless loop. 3) Since you are using Worksheet_Change you actually do not need to declare a variable wks for your worksheet.
What is the user wants to change their answer to "Yes"? The row is grayed out and locked.
0

Try this : -

Apply a Loop and then check

if "NO"

ws.get_Range(StartCell, EndCell).Locked = true;

// where ws is the worksheet object The only point to be seen is that if the Sheet is Locked you need to unlock it and then proceed

else if "YES"

continue;

Improve this answer

3 Comments

Thank you for the suggestion, this seems logical. How could I implement this in VB?
The answer which i had given is a c# code. For VB you can try the answer given below by Tushar. I hope this should work out in what you are trying to achieve.
@RohitAgrawal, I think you should delete this answer. It doesn't make any sense to have it posted as an answer to a VBA question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.