excelexcel-2010userformvba

Calling a userform and returning a value


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

Solution

  • Remove Dim bool As Boolean from the userform code area and declare it in the module as shown below

    This is how your Code in the module would look like

    Public bool As Boolean
    
    Sub Auto_Open()
        '
        '~~> Rest of the code
        '
        UserForm1.Show
    
        If bool = True Then
            '~~> Do Something
        Else
            '~~> Do Something        
        End If
    
        '
        '~~> Rest of the code
        '
    End Sub