Using VBA Code to prevent editing in certain cells based on the contents of a cell with a drop down

Question

Fair warning: Complete VBA novice here.

I have a sheet that I am sending out. The form has a cell (D8) where a selection is made from a drop down list. Based on the selection in that list, I want to have certain areas of the sheet lock and unlock so that the only part of the form they can fill in is pertaining to their selection in D8. So say they enter "Montana" in the drop down, I only want the section of the form that has areas applicable to that choice to be editable. I will then have various ranges locking and unlocking based on the selection in this drop-down.

Here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)

    If ActiveSheet.Cells(8, 4).Text = "Montana" Then
        ActiveSheet.Range(Cells(14, 1), Cells(15, 6)).Locked = False
    Else
       ActiveSheet.Range(Cells(14, 1), Cells(15, 6)).Locked = True
    End If

End Sub

But when I go and attempt to test wether the range has been locked I can still edit the cells while the sheet is protected even if the condition has been met.

Welcome to Stack Overflow. Please show your latest non-working attempt at solving this problem. It is usually more instructive to see fixes to your code than to read someone else's solution. See https://stackoverflow.com/help/how-to-ask — Spangen, Jun 08 '18 at 13:17
You have to save the file to take effect of lock unlock. So, add codes to save the file like `ActiveWorkbook.Save` — Harun24hr, Jun 08 '18 at 13:33

FunThomas · Answer 1 · 2018-06-08T13:58:10.527

As long as your worksheet is not protected, the locked property doesn't do anything. It is explained in Excel: call Format Cells and choose the tab 'Protection'.

Problem is that once a cell is locked and your sheet is protected, you cannot simply unlock it - you have to remove the protection of the sheet first. So your code could look like:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const MySecretPassword = "Hallo"

    If Intersect(Target, Cells(8, 4)) Is Nothing Then Exit Sub

    On Error GoTo Protect
    ActiveSheet.Unprotect MySecretPassword 
    If ActiveSheet.Cells(8, 4).Text = "Montana" Then
        ActiveSheet.Range(Cells(14, 1), Cells(15, 6)).Locked = False
    Else
        ActiveSheet.Range(Cells(14, 1), Cells(15, 6)).Locked = True
    End If
Protect:
    ActiveSheet.Protect MySecretPassword

End Sub

Note that I added a check if cell(8, 4) was modified and not any other to prevent unnecessary execution of the code.

Note also: As you need the password within the code, you have to protect your VBA project also

And last: Remember to unlock all cells that the user should be able to modify, especially your "Montana" cell.

So if I wanted to add multiple different sections to lock, how would I go about doing that? Just do edits of this with different words? I also don't see where it is referencing the text in D8. — user9914219, Jun 08 '18 at 13:57

Using VBA Code to prevent editing in certain cells based on the contents of a cell with a drop down

1 Answers1