Creating Password Function in Code

[diddy47]

Hello, say I have this data set Apple (123) Banana(456) Carrot (789) Mango (222) Peach(333) Berry

I want to have a cell say C12 be a cell where any number can be entered but it locks if a correct number corresponding to a fruit is entered and displays that fruit name in C11. If 123 is entered in C12 the word Apple is shown in cell C11.

How can i have it that ONLY C12 is locked after an entry corresponding to a fruit. If a number that does not match say 156 is entered in C12 nothing happens, C11 remains blank or just shows FALSE.

D12, E12 and so on should remain unlocked until a correct value is entered.

I had patched together a formula for testing the code for C11 (the formula will be the same for D11, E11 and so on) not sure if it is the best.

=IF(C12=123,"Apple",IF(C12=456,"Banana",IF(C12=789,"Carrot",IF(C12=222,"Mango",IF(C12=333,"Peach",IF(C12=444,"Berry"))))))

Basically I want no one to be able to change C12 after a matching value has been entered.

Please and thank you

 

[Yongle]

Try this - uses sheet protection
Remember to unlock any cells in sheet requiring user input beforehand

When one of the 5 numbers is entered in row 12 (ignoring columns A & B)
- values in row 11 updated
- cell is locked

place in SHEET module

[I]Private Sub Worksheet_Change[/I](ByVal target As Range)
    Dim rng As Range:   Set rng = Range("C12").Resize(, Columns.Count - 11)
    If Not Intersect(rng, target) Is Nothing Then
        Select Case target.Value
            Case 123, 456, 789, 222, 333
                Call LockCell(target)
                Call UpdateValue(target)
        End Select
    End If
End Sub

[I]Private Sub UpdateValue[/I](target As Range)
    Dim cel As Range:   Set cel = target.Offset(-1)
    Select Case target.Value
        Case 123: cel = "Apple"
        Case 456: cel = "Banana"
        Case 789: cel = "Carrot"
        Case 222: cel = "Mango"
        Case 333: cel = "Peach"
    End Select
End Sub

[I] Private Sub LockCell[/I](target As Range)
    Me.Unprotect "password"
    target.Locked = True
    Me.Protect "password"
End Sub

 

[Yongle]

or without using sheet protection
- if cell already contains one of the 5 values then cell below amde active cell (user cannot select C12 - cusror does to C13 etc)

Place in SHEET module

[I]Private Sub Worksheet_SelectionChange[/I](ByVal Target As Range)
    Dim rng As Range:   Set rng = Range("C12").Resize(, Columns.Count - 11)
    If Not Intersect(rng, Target) Is Nothing Then
        Select Case Target.Value
            Case 123, 456, 789, 222, 333
                Target.Offset(1).Select
        End Select
    End If
End Sub

[I]Private Sub Worksheet_Change[/I](ByVal Target As Range)
    Dim rng As Range:   Set rng = Range("C12").Resize(, Columns.Count - 11)
    If Not Intersect(rng, Target) Is Nothing Then
        Select Case Target.Value
            Case 123, 456, 789, 222, 333
                Call UpdateValue(Target)
        End Select
    End If
End Sub
[I]
Private Sub UpdateValue[/I](Target As Range)
    Dim cel As Range:   Set cel = Target.Offset(-1)
    Select Case Target.Value
        Case 123: cel = "Apple"
        Case 456: cel = "Banana"
        Case 789: cel = "Carrot"
        Case 222: cel = "Mango"
        Case 333: cel = "Peach"
    End Select
End Sub

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...43231-creating-password-function-in-code.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.