Match new password to specific user and update list via custom user form

Hi

I am stumped and need help.

I have an Excel workbook that is used at work for Management Information held in a Shared Folder.

It is set up so that certain parts are password protected and to access is by way of a userform.

The access details are held on a seperate hidden sheet called Passwords with the User names in column A and the Passwords in Column B. This works great.

However recently some managers have asked to be able to change their passwords themselves with a Userform.

The Userform has a hidden Frame above the textbox so when the old password is entered if its correct it brings up the following to allow a change of password shown below.


The code I have used is fine except that it will only change one password mine in Cell B2, this is below

Option Explicit
Private Sub CommandButton1_Click()
'frame1 is hidden until password is validated
If TextBox1.Value = Sheet2.Range("B2").Value Then
  Frame1.Visible = True
  Me.Height = 90
Else: Unload Me
End If
End Sub
Private Sub CommandButton2_Click()
Sheet2.Range("B2").Value = TextBox2.Value
End Sub

The passwords are on sheet 2 between Column B2:B20

Can anyone help with the code so that ANY users password that is entered correctly in the "Enter Password" text box and is identified between Comumn B2:B20 as correct and take them to the change password textbox in the Frame and also when they change their password it allows it and updates the password in the correct place in Column B wherever it is located between B2:B20.

You need to click on the Access to Management Command Button and the User name is Portsmouth, the Password is Pompey.

The workbook with the password pages is attached.

Many thanks if anyone can.

Have you thought of replacing all your security code with just three lines of code (see demo) ? Here's the thing ... your users have already put in a username and password to get into windows .. right ? Well the system already knows who you are and remembers your login name . So instead of having the user have to type in another name and anther password just have the system validate that the Windows Login name ( i.e. the person currenlty logged in ) has their name on the "Password" sheet in column A. If there name is there then give the access to the manager section.

So , in you situation , a click on the "access to Management info" button would just run the below function "IsOnManagersList". If the function returns True then let them in .. if the function returns False then no addmitance. Boomba no second passwords or second logging in.

SETUP
Setup of a User is simple ... find out users windows login name and put that name in Column A of the Password Sheet. Done.

Public Sub demo()
    If IsOnManagersList Then
       MsgBox "Access Allowed"
    Else
       MsgBox "Access Denied"
    End If
End Sub

Private Function IsOnManagersList() As Boolean
      Set UserNameRange = ThisWorkbook.Sheets("Passwords").Range("A:A")
      WindowLogInName = Environ("username")
      
      IsOnManagersList = Application.WorksheetFunction.CountIf(UserNameRange, WindowLogInName)
End Function

Cross posted at Ozgrid