VBA How to force a function to Return when a Form Button is pressed

excel, vba

Solution

Returning a value from a dialog is a common task & pretty simple to do.

The simplest pattern is to put the function in the dialog form itself and have that function show its host form modally.

Private passwordStatus As Boolean

Function checkPassword(message As String) As Boolean
  '//setup the form
  Me.passwordMsg.Caption = message

  '//show the form modally, this will not return until the form is unloaded 
  '//i.e. when the button is clicked; so values in private variable are still valid
  Me.Show vbModal

  '//form is unloaded (via unload me or a close) return the value;
  checkPassword = passwordStatus
End Function

Private Sub passwordok_Click()
  passwordStatus = Me.passwordtext.Text = "password"
  Unload Me
End Sub

Used as

passworkOk = frmPassword.checkPassword("enter your blabla")

Problem

I thought this would be simple, but it is proving quite difficult. Any advice or ideas would be appretiated. 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 ```

Original source