Calling a userform and returning a value

Question

I have a vba code thats Auto_Open. It does some checks then prompts a userform that asks for username and password. I called this userform with userform_name.show.

My issue is how can I return a Boolean to my Auto_Open sub from the userform code.

I linked the code that verifies if the credentials are correct to the "Login" button on the form. this is the code that produces the Boolean. I need to return it to the Auto_Open.

Private Sub loginbutton()
    Dim bool As Boolean
    Dim lrup
    Dim r As Long
    Dim pass As String

    loginbox.Hide

    'are fields empty
    Do While True
        If unBox.Text = "" Or pwBox.Text = "" Then
            MsgBox ("You must enter a Username and Password")
        Else
            Exit Do
        End If
        loginbox.Show
        Exit Sub
    Loop

    'find pw reated to username (if existant)
    lrup = UserPass.Range("A1").Offset(UserPass.Rows.Count - 1, 0).End(xlUp).Row

    If unBox = "b0541476" And pwBox = "theone" Then
        bool = True
    Else
        MsgBox ("Invalid username or password. Please try again.")
        loginbox.Show
        Exit Sub
    End If

    For r = 2 To lrup
        If unBox = Cells(r, 1) Then
            pass = Cells(r, 2).Value
            Exit For
        End If
    Next

    If pass = "" Then
        MsgBox ("Invalid username or password. Please try again.")
        loginbox.Show
        Exit Sub
    Else
        bool = True
    End If
End Sub

Answer 1

You can manage to do this without the use of public variables.

There appears to be a difference between show/hide and load/unload.

If you hide a form while it's still loaded it won't be cleared out, so you can reference the state of the controls on the form.

For example I was using a date picker (called DTPicker1) on a form, my code in the module looks something like this:

Dim NewDay As Date

Load FrmDayPicker
FrmDayPicker.Show

NewDay = FrmDayPicker.DTPicker1.Value

Unload FrmDayPicker

Debug.Print NewDay

On your form you can just use Me.Hide insteaded of Unload Me and this should work