Problem with blocking a cell when it displays a certain values (VBA)

[benprelf]

I want to be able to block a cell to prevent users from accessing it when displays all options but "mm".

When it displays "mm" I want users to be able to access the cell.

Here's the VBA code I have currently written (however I keep receiving error messages when trying to run it):

Sub ProtectCells()
ActiveSheet.Protect

Cells.Locked = True
If ("E3") = ("mm") Then
Range("G3").Locked = False
ElseIf ("E3") = Range("B3:B5") Then
Range("G3").Locked = True
End If

End Sub

Sub AddNewSheet()
ActiveSheet.Unprotect
Cells.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Run "ProtectCells"
End Sub

"E3" is the cell containing the drop down list from which the user can select options: "a","b","c" and "mm". While "G3" is the cell that I want to block access to when it is not displaying "mm". "G3" is linked to "E3" to display what is currently selected from the drop down.

Cells "B3:B5" contain the data from which the drop down list was created.

 

[steve the fish]

Does this work for you:

Sub ProtectCells()
ActiveSheet.Unprotect
Cells.Locked = True
If Range("E3") = "mm" Then
    Range("G3").Locked = False
Else
    Range("G3").Locked = True
End If
ActiveSheet.Protect
End Sub

 

[benprelf]

Thanks for the reply. The code does block "G3" when it does not contain "mm" however the drop down list in the cell "E3" is also blocked and I would still like the user to be able to access this.

Sorry if I did not make this clear in the original post.

 

[steve the fish]

Try:

Sub ProtectCells()
ActiveSheet.Unprotect
Cells.Locked = True
If Range("E3") = "mm" Then
    Range("G3").Locked = False
Else
    Range("G3").Locked = True
End If

Range("E3").Locked = False
ActiveSheet.Protect
End Sub

 

[benprelf]

The drop down list is now available for the user to select but "G3" is still not available for the user to access when it equals "mm". It also becomes locked when any other option is selected from the drop down which is what I would like. I just can't understand why "G3" is still locked when it contains "mm".

Thanks again

 

[steve the fish]

Ah ok. It will be until the macro is run again after the mm is selected. Right click the sheet you want to use. Press view code and put this in. That better?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$3" Then
    ActiveSheet.Unprotect
    Cells.Locked = True
    If Range("E3") = "mm" Then
        Range("G3").Locked = False
    Else
        Range("G3").Locked = True
    End If
    Range("E3").Locked = False
    ActiveSheet.Protect
End If
End Sub

 

[benprelf]

That's perfect thanks!