Lock worksheet is specific cell has value over 1M

Originally Posted by staceynairn

I am trying to lock an worksheet

Lock refers to sheet protection?

Also please confirm the cell reference

Copy the below code and do right click on sheet tab and select view code and paste the below code.

Return to excel and do entry on D29 Cell and check and confirm

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$D$29" Then Exit Sub

Dim vValue As Variant

On Error Resume Next

vValue = UCase(Target.Value)

If InStr(1, vValue, "M") Then
    vValue = Replace(vValue, "M", "")
    If Len(Target.Value) <> Len(vValue) Then
        If IsNumeric(vValue) And vValue >= 1 Then
            Me.Protect "abc"
            Me.Cells.Locked = True
            MsgBox "Sheet Protected", vbInformation, "Reached the Maximum Value"
        End If
    End If
End If

End Sub

Replace the previous code with the below one

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$D$29" Then Exit Sub

If IsNumeric(Target.Value) And Target.Value >= 1000000 Then
    Me.Protect "abc"
    MsgBox "Sheet Protected", vbInformation, "Reached the Maximum Value"
End If

End Sub

Sixthsense, I believe OP meant 1,000,000 by 1M, but I could wrong though.

@ millz,

Even I am also not sure about the exact keyword about how the OP will enter the values in it.

Just attempted to give solution on assumption.

The questioner should be clear in describing the input values to get exact solution.

We wait and see