Passing variable from Form to Module in VBA

Question

I have the following button on a Form:

Private Sub CommandButton1_Click()
 Dim pass As String
 pass = UserForm1.TextBox1
 Unload UserForm1
End Sub

I then have a Module called Module1:

 Public Sub Login()

 ...

 UserForm1.Show
 driver.findElementByName("PASSWORD").SendKeys pass

 ...

End Sub

The idea is whatever password the users enters into the input box will be assigned to the variable pass. What I'm having trouble doing however is passing pass from UserForm1 into Module1's Login sub.

I would of thought adding something like Module1.Login (pass) to my form before I unload it would work, however that doesn't seem to pass anything. Any help would be much appreciated. Thanks.

score 42 · Accepted Answer · answered Nov 26 '13 at 10:17

42

Don't declare the variable in the userform. Declare it as Public in the module.

Public pass As String

In the Userform

Private Sub CommandButton1_Click()
    pass = UserForm1.TextBox1
    Unload UserForm1
End Sub

In the Module

Public pass As String

Public Sub Login()
    '
    '~~> Rest of the code
    '
    UserForm1.Show
    driver.findElementByName("PASSWORD").SendKeys pass
    '
    '~~> Rest of the code
    '
End Sub

You might want to also add an additional check just before calling the driver.find... line?

If Len(Trim(pass)) <> 0 Then

This will ensure that a blank string is not passed.

answered Nov 26 '13 at 10:17

Siddharth Rout

147,039
17
206
250

7

+1 Sid this is a good way of doing it. Alternatively you can hide the Userform1 instead of unloading it and access the UserForm1.TextBox1 from the module and then Unload userform1 but decision on the approach depends on the entire implementation – Nov 26 '13 at 10:23
Ahah, I never thought to declare the variable as Public. This is an extremly great tip that I'll have to start doing more, thank you! – JimmyK Nov 26 '13 at 10:27
@user2140173 Best to unload the form once you're done with it. Better for memory and security. – TylerH Apr 19 '17 at 13:39
4

@TylerH only because you're working with the form's default instance. Treat forms like the objects they are instead of like global-state UI, and both "memory" and "security" arguments don't hold anymore. – Mathieu Guindon Jul 04 '17 at 11:54
To add here: if you're passing the variable from a second UserForm back to the first UserForm, declaring the variable in Public and hiding the second UserForm is a solid approach to preserve the result. – TornadoEric Oct 31 '22 at 14:49
After UserForm1.Show, I tried ```MsgBox pass``` but I'm getting blank, the value from the UserForm input isn't passing to the module! – KiDo Feb 03 '23 at 08:42
@KiDo Public varibales should be used only when necessary. As MathieuGuindon mentioned below, OOP way is a better way. I have spoken about this in my YouTube video [VBA Classes](https://youtu.be/gDVXM5-0ZTI) – Siddharth Rout Feb 03 '23 at 13:22

Mathieu Guindon · Answer 2 · 2020-09-26T01:09:24.180

30

Siddharth's answer is nice, but relies on globally-scoped variables. There's a better, more OOP-friendly way.

A UserForm is a class module like any other - the only difference is that it has a hidden VB_PredeclaredId attribute set to True, which makes VB create a global-scope object variable named after the class - that's how you can write UserForm1.Show without creating a new instance of the class.

Step away from this, and treat your form as an object instead - expose Property Get members and abstract away the form's controls - the calling code doesn't care about controls anyway:

Option Explicit
Private cancelling As Boolean

Public Property Get UserId() As String
    UserId = txtUserId.Text
End Property

Public Property Get Password() As String
    Password = txtPassword.Text
End Property

Public Property Get IsCancelled() As Boolean
    IsCancelled = cancelling
End Property

Private Sub OkButton_Click()
    Me.Hide
End Sub

Private Sub CancelButton_Click()
    cancelling = True
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        cancelling = True
        Cancel = True
        Me.Hide
    End If
End Sub

Now the calling code can do this (assuming the UserForm was named LoginPrompt):

With New LoginPrompt
    .Show vbModal
    If .IsCancelled Then Exit Sub
    DoSomething .UserId, .Password
End With

Where DoSomething would be some procedure that requires the two string parameters:

Private Sub DoSomething(ByVal uid As String, ByVal pwd As String)
    'work with the parameter values, regardless of where they came from
End Sub

edited Sep 26 '20 at 01:09

answered Jul 14 '16 at 18:53

Mathieu Guindon

69,817
8
107
235

@RandomDownvoter feedback is always appreciated; I'm always open to new ways of improving my answers. Right now I'm kind of puzzled about what's wrong with avoiding global state and enhancing the code's expressiveness and robustness through simple abstraction. Cheers. – Mathieu Guindon Aug 21 '18 at 18:21
1

I love this approach to separating the form from the data and methods. How would I implement this in a way where the userform can remain visible. Using the example from above, say the ID or password come back incorrect, could the form remain open and give some warning? – Robert Todar Dec 06 '18 at 19:48
thanks so much for the information, I'm reading through the site and practicing it now with my own code. FYI the download link is no longer working on that site. Thanks again! – Robert Todar Dec 06 '18 at 20:29
1

Amazing solution. There is one error on the 'QueryClose' though. I am posting the link to the fix below. Since I lost 30 minutes trying to solve it, I am adding here for future reference as this is the first result when googling for passing values from form to module https://stackoverflow.com/questions/51179449/vba-automation-error-server-not-server-application-upon-closing-form – hend Sep 25 '20 at 20:21
1

@Hend thanks for the heads-up! note that this code does invoke `Me.Hide`, and I've never had any issues with this (although, this is a little bit simplified, I actually put the state flag toggle + form hiding in a private `OnCancel` method that I invoke from both `QueryClose` and some `CancelButton_Click` handler; the code in the linked question appears to be missing the hiding of the form, and missing the `Cancel = True` is what's causing the bug, because not cancelling this makes the form object self-destruct. I'm fixing this, thanks! – Mathieu Guindon Sep 26 '20 at 01:08

Passing variable from Form to Module in VBA

2 Answers2

Linked