How do I use an excel form to collect and check a password

[prestondocks]

I have a form in Excel that if a certain button is pressed I need the user to enter a password before the code for that button is run.

I could just use a inputbox, but that will allow anyone else to see the password when it is typed in. So I want to use a second form with a textbox and set it's PasswordChar parameter to *

Here is the problem. I want to use code like this

if checkPassword("Please enter your password") = False then exit sub

checkPassword is a function that takes a string as a parameter. This function opens a form and puts the message in to a lable. The user should enter the password and click OK.

the sub btnOK_Click() should check the password is correct and then force the function that opened the form to return True if the password was OK or False is the password was incorrect.

I just cant work out how to force the function to return. I have tried setting a global variable to either True or False when the user click OK and then unloading the form. This makes the Function return, but it also resets all the global variables set by the form.


Here is my function that calls the form

Function checkPassword(message As String) As Boolean

  frmPassword.Show
  frmPassword.passwordMsg.Caption = message
  
  'passwordStatus is a global variable
  If passwordStatus = True Then checkPassword = True Else  checkPassword = False

End Function

Here is the sub linked to the forms OK button

Private Sub passwordok_Click()
  If Me.passwordtext.Text = "password" Then
      passwordStatus = True
  Else
      passwordStatus = False
  End If
Unload Me

End Sub

Can anybody help me.

Simon

 

[combo]

First load the form and set variables and next show it. Modal userforms pass the code flow to calling procedure after hiding/unloading the form.
If frmPassword is the form's name:

Function checkPassword(message As String) As Boolean  frmPassword.Show
With frmPassword ' this also loads the userform
    .passwordMsg.Caption = message
    'passwordStatus is a global variable
    .Show
End With
If passwordStatus = True Then checkPassword = True Else  checkPassword = False
End Function

combo

 

[prestondocks]

Thanks combo.

I got a similar response from someone on stackoverflow. Will give this a try later.

Simon