Custom form call and return value

Question

I have vba code which is Auto_Open. He then performs some checks, and then requests a user form that asks for a username and password. I called this user form using userform_name.show .

My problem is how can I return Boolean to my Auto_Open sub from custom form code.

I linked a code that validates the credentials for the Login button on the form. this is code that creates a boolean value. I need to return it to 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

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

This is how your code in the module will 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 

Answer 2

How to use function instead of sub?

 Function loginbutton() ' your code loginbutton = bool End Function 

Now in the call code you can check true / false

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

Answer 3

You can do this without using public variables.

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

If you hide the form while it is loading, it will not be cleared, so you can refer to the state of the controls in the form.

For example, I used a date picker (called DTPicker1 ) on the 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 

In your form, you can simply use Me.Hide instead of Unload Me , and this should work