lock/unlock cell based on another cells input

[2funny4words]

basically, I want cell g12 to be locked unless cell h7 is Grass Fire or Timber Fire (H7 is a drop down validated cell)

[royUK]

Make sure you set up the sheet with protection - unlock input cells, etc.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const PW   As String = "secret"
    If Target.Address = "$G$12" Then
        Select Case Cells(7, 8).Value
            Case "Grass Fire", "Timber Fire"
                ActiveSheet.Unprotect PW
                Target.Locked = False
                ActiveSheet.Protect PW
        End Select
        End If
End Sub

Copy the code
Select the worksheet in which you the want the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

[2funny4words]

Make sure you set up the sheet with protection - unlock input cells, etc.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const PW   As String = "secret"
    If Target.Address = "$G$12" Then
        Select Case Cells(7, 8).Value
            Case "Grass Fire", "Timber Fire"
                ActiveSheet.Unprotect PW
                Target.Locked = False
                ActiveSheet.Protect PW
        End Select
        End If
End Sub

Copy the code
Select the worksheet in which you the want the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

This comes up with an ambiguous name selected: worksheet_selectionchange

[DonkeyOte]

You could theoretically use Data Validation on G12 also, no ?

G12: Validation: Custom: =OR(H7="Grass Fire",H7="Timber Fire")

However this won't physically clear contents of G12 as and when H7 is altered to something other than the 2 choices mentioned if that is your intention

In terms of using VBA you can use a Worksheet_Change event to physically lock/unlock the cell but this would require the entire sheet be protected - is that what you're looking to do ? It is unclear (to me at least)

[2funny4words]

You could theoretically use Data Validation on G12 also, no ?

G12: Validation: Custom: =OR(H7="Grass Fire",H7="Timber Fire")

However this won't physically clear contents of G12 as and when H7 is altered to something other than the 2 choices mentioned if that is your intention

In terms of using VBA you can use a Worksheet_Change event to physically lock/unlock the cell but this would require the entire sheet be protected - is that what you're looking to do ? It is unclear (to me at least)

The entire sheet is protected and only unlocked cell can be edited

[2funny4words]

I tried DonkeyOte's idea and it worked but not quite like I wanted it to.

When I tried royUK's code I come up with a compile error: Ambiguous name detected: Worksheet_SelectionChange

here is the code that I have

[Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const PW   As String = "secret"
    If Target.Address = "$G$12" Then
        Select Case Cells(7, 8).Value
            Case "Grass Fire", "Timber Fire"
                ActiveSheet.Unprotect PW
                Target.Locked = False
                ActiveSheet.Protect PW
        End Select
        End If
End Sub]

[VBA Noob]

2funny4words,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

VBA Noob

[2funny4words]

sorry about that I figured that out on my next post

Thanks

[royUK]

The ambiguous name error is caused bevause you have two procedures with the same name, the SelectionChange event might already be in use.

Use this, it will run whenever G12 is changed

[Private Sub Worksheet_Change(ByVal Target As Range)
    Const PW   As String = "secret"
    If Target.Address = "$G$12" Then
        Select Case Cells(7, 8).Value
            Case "Grass Fire", "Timber Fire"
                ActiveSheet.Unprotect PW
                Target.Locked = False
                ActiveSheet.Protect PW
        End Select
        End If
End Sub]

[moxdawg4]

"The ambiguous name error is caused bevause you have two procedures with the same name, the SelectionChange event might already be in use."

Is there possibly another procedure to use if SelectionChange and Change are in use?