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

[Kaper]

Format cells where user can change data as unlocked (in attachment A12:Z12).
Protect your sheet - I used password excelforum.com
In sheet code (right click on sheet tab and select "display code"):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Range("A12:Z12")) Is Nothing Then
  For Each rng In Intersect(Target, Range("A12:Z12"))
    If rng.Offset(-1, 0).Value <> False Then
      ActiveSheet.Unprotect "excelforum.com"
      rng.Locked = True
      ActiveSheet.Protect "excelforum.com", UserInterfaceOnly:=True
    End If
  Next rng
End If
End Sub

PS. I used in row 11 simple function with one if only. Instead of nested ifs I'd recommend using VLOOKUP or similar solution, but it's a subject for separate thread

[diddy47]

thank you so much for taking the time to do this...this is the function i have been looking for.....additional question how do I repeat the function on a different row say A24 on the same sheet

[Kaper]

If protected - unprotect your sheet.
Format cells where user can change data as unlocked (so for instance three ranges A12:Z12, A24:Z24, A36:Z36 - change these ranges also in the code below).
Protect your sheet
In sheet code (right click on sheet tab and select "display code"):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, wholerange as range
set wholerange = union(Range("A12:Z12"),Range("A24:Z24"),Range("A36:Z36"))
If Not Intersect(Target, wholerange) Is Nothing Then
  For Each rng In Intersect(Target, wholerange)
    If rng.Offset(-1, 0).Value <> False Then
      ActiveSheet.Unprotect "excelforum.com"
      rng.Locked = True
      ActiveSheet.Protect "excelforum.com", UserInterfaceOnly:=True
    End If
  Next rng
End If
End Sub

[alansidman]

Rule 08: Cross-posting Without Telling Us

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

No further help to be offered, please, until the OP has complied with this request.