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

A very simple and intuitive solution is instead of calling userform_name.Show, write a function in the UserForm module that both shows it and returns a value.

For example, if you want your UserForm to return True or False based on input in the form:

'In the UserForm module
Public Function VerifyLogin() As Boolean
    Me.Show

    'Code that handles verification... 

    VerifyLogin = bool
End Function

And when you want to use it, just call the function on the UserForm:

userform_name.VerifyLogin

Answer 2

Update:

I was to quick to dismiss public variables. While both methods can work, Pub Vars and directly accessing items, sometimes it's not ideal to access an item directly if say it's a list.

I now have modules specifically for calling UserForms which only declar the public variables and call the userform. I can then call these modules from UserForms or Modules and have access to the public variable after the userform is closed.

Eg: Here is a module I use now, very basic, and all my other needs can just call this module/sub.

Public ColSelectorDic As Object
Public Sub Col_Picker_Sub()
 Col_Picker_UserForm.Show
End Sub

---

It's simplest IMO to use Public Variables declared in the Module calling the UserForm. But, this has the caveat if you wanted to call this userform from separate modules, you will get errors regarding duplicate declarations/ambiguous names.

So, if you know it's only going to be called be the one module, Pub Vars all the way. In my case I was using a "Column Picker" userform, which was very simple and I wanted to be able to utilize it again in unforseen future projects so I attempted to resolve the above caveat.

See this answer for Public Variables, no need to repeat information --> https://stackoverflow.com/a/18966341/5079799

And this answer related to Accessing the Form Variables directly --> https://stackoverflow.com/a/47919465/5079799 but I felt it could use some expanding.

Also, here is a good article which goes deeper in depth about accessing userform variables directly --> https://gregmaxey.com/word_tip_pages/userform_pass_data.html

So my UserForm looks like this and is named ColPicker:

Private Sub UserForm_Initialize()
Dim i As Long
 lCol = Get_lCol(ActiveSheet)
  For i = 1 To lCol
   ColumnLetter = Col_Letter(i)
   Me.ComboBox1.AddItem ColumnLetter
  Next
End Sub
Private Sub CommandButton1_Click()
 Me.Hide
End Sub

Sub PassVarFromUserForm()
 ColPicker.Show
 Dim ColLetter As String
 ColLetter = ColPicker.ComboBox1.Value
 Unload ColPicker
 Debug.Print ColLetter
End Sub

Notice how the "Run"/Command Button in the UserForm just hides the form, I then store the values in a variable, THEN unload the form, from the module, via utilizing it's name. (You can only use unload me from within the userform).

The variable is then available inside module and can be declared in the beginning as public, or inside module, it doesn't matter as it can be declared differently in each module, the userform has no idea/reference to what the variable name the information will be stored in.

Answer 3

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

Answer 4

How about using a function instead of a sub?

Function loginbutton()
  ' your code

  loginbutton = bool
End Function

Now in your calling code you can test for true/false

if loginbutton() then
  'true responce
else
  'false responce
end if

Answer 5

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