Assign UserForm inputs to variables in new module VBA

Question

I want to assign the inputs of a userform to variables in a new module once the user clicks submit.

I have read the following post : Passing variable from Form to Module in VBA

I believed I had written the code such that it followed the form set by the previous post but I am still getting an error:

Public Sub submit_click()

Dim user_name As String, occupation As String, state As String, city As String, married As Boolean, not_married As Boolean, num_kids As Integer

'General Information Inputs

user_name = firstname_textbox.Value

occupation = occupation_textbox.Value

state = state_textbox.Value

city = city_textbox.Value

married = married_option.Value
not_married = single_option.Value

num_kids = numchildren_textbox.Value

Unload Me

End Sub

This is the module I am using to check if I am passing the inputs over:

Public user_name As String, occupation As String, state As String, city As String, married As Boolean, not_married As Boolean, num_kids As Integer
Public salaried As Boolean, hourly As Boolean, monthly_hours As Double, salary As Double, wage_rate As Double, bonus As Double
Public retirement_account As String, retirement_account_contribution As Double, employer_match As Double, investing_amount As Double

Public Sub print_inputs()


Dim loc As Worksheet

Set loc = ThisWorkbook.Worksheets("Sheet1")


loc.Range("A1") = user_name.Value


End Sub

However, I get a "Invalid Qualifier" Error when trying to assign loc.range("A1") = user_name.Value

Am I assigning these inputs to variables incorrectly?

For reference. Check this https://rubberduckvba.wordpress.com/2017/10/25/userform1-show/ — Ricardo Diaz, Nov 07 '20 at 04:44

Variatus · Answer 1 · 2020-11-07T03:11:10.117

You're off on a difficult path. Please reconsider your approach. Place this code in a standard code module.

Sub Test()

    Dim MyForm      As MyInputForm
    Dim MyVariable  As Double
    
    ' now the form is created: Initiate event occurs
    Set MyForm = New MyInputForm
    With MyForm
        ' The form is still invisible but all its controls accessible
        ' use this moment to pre-load the form
        .TextBox1.Value = "Hello"
        .TextBox2.Value = Cells(2, "B").Value
        .Show                   ' now the form takes control
        
        ' Execution of this procedure resumes when the form is hidden.
        ' The form is invisible but all of it still in memory.
        
        ' You need a "Close", "K" or "Submit" button that triggers "Me.Hide"
        ' It may also include "Me.Tag = 1" as opposed to
        ' a Cancel button which has "Me.Hide: Me.Tag = 0"
        If .Tag = 1 Then            ' skip if cancelled
            ' now transfer from form to worksheet
            Cells(15, "B").Value = .TextBox2.Value
            MyVariable = Val(.TextBox1.Value)
        End If
    End With
    
    ' now you're done and clean up.
    Unload MyForm
    Set MyForm = Nothing
End Sub

The procedure creates an instance of the form which you can access before it is shown, to preload information from as data source like a worksheet, and again after it is closed to retrieve data from it. There is no need for any of the public variables your current system requires and, therefore, no opportunity for the errors to occur that drove your question.

For your test, please create a form by the name of MyInputForm. Give it 2 text boxes TextBox1 and TextBox2, and a Commandbutton with this code:-

Private Sub CommandButton1_Click()

    Me.Tag = 1
    Me.Hide
End Sub

You sai reconsider my approach. Is there a better way to do this? — , Nov 07 '20 at 02:38

Assign UserForm inputs to variables in new module VBA

1 Answers1